troubleshooting Question

Check For Duplicate Records Before Insert Return Message

Avatar of lotterygirl
lotterygirlFlag for United States of America asked on
Microsoft SQL Server
5 Comments1 Solution713 ViewsLast Modified:
I have a stored procedure where I want to make sure the email is not already in the file prior to inserting the record inthe file because it is set as the primary key. I want to return either a success or failure message back to the calling asp page and then redirect the page depending upon the message received.  It seems to work if the record is a duplicate record but I get this error "ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. " when it is a new record.  When I run the stored procedure in query analyzer it seems to work but I am not getting the expected results in the asp page.

Here is the stored procedure code
USE MembersFile
Go
CREATE PROC membersClubSP
  @eMail varchar(50)
As
DECLARE @Message varChar(15)
  IF EXISTS(SELECT EMail FROM membersFile WHERE @eMail = EMail)
    BEGIN
      SET @Message = 'Duplicate Email'
      SELECT @Message AS ReturnMessage
    END
  ELSE
  BEGIN
  /*Create the new record*/
  INSERT INTO membersFile
  VALUES(
    @eMail
    )
  SET @Message = 'Successful'
  SELECT @Message AS ReturnMessage
  END
rtnMessage = objRS.Fields("ReturnMessage")


Here is the code in the asp page to get the return message                  
If (rtnMessage = "Duplicate Email") Then
  Response.Redirect("SignUpFailure.asp")
Else
  Response.Redirect("SignUpSuccessfull.asp")
End If

Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros