0

I'm trying to conduct a duplicate email check on my database, and I have this on my form submission event:

rsEmailList = SPEmailCheck(Trim(Request.Form("EMail")))
If rsEmailList = 52016 Then MsgBox("Please input an email address not already in use")

My function for SPEmail check is:

oCmd.CommandText = "[spEmailCheck]"

oCmd.Parameters.Append oCmd.CreateParameter("pEmail", adVarChar, adParamInput, 255, cEmailAddress)
oCmd.Parameters.Append oCmd.CreateParameter("pMsg", adInteger, adParamOutput)

SPEmailCheck = oCmd.Parameters("pMsg")

DeleteAllParms

And the stored procedure is:

CREATE OR ALTER   PROCEDURE [dbo].[spEmailCheck]
@pEmail NVARCHAR(255),
@pMsg   INT OUT
AS

IF EXISTS(SELECT 1 FROM [dbo].[Address] WHERE [Addr_EMail] = @pEmail AND [Address_Type] = 'U')
BEGIN
    SET @pMsg = 52016 --Email address already exists and is associated with another profile.
    RETURN
END

I've confirmed by manual execution that the procedure works as intended. I can't seem to get the message box to trigger. My inetpub logs show error "Object_required:_'rsEmailList'" for the line that sets the SPEmailCheck on my event.

Also have an issue with the MsgBox being denied permission to do what it's supposed to when I try to trigger that separately, but that's another issue that I can address once I've figured out how to conduct this check correctly.

Michael Alan Nollette
  • 135
  • 3
  • 13
  • 2
    Are you ever actually executing the command? You create parameters but shouldn’t you be executing something? Been a long, long time since I’ve looked at VBScript, admittedly. – Chris Farmer Nov 14 '20 at 00:55
  • he not using vbscript - he is using vb.net. That tag needs to be changed. and while we are at this? One cannot use msgbox in vb.net if this is REALLY a asp.net application. So that tag also seems wrong. So the choice of tags here needs some serious love and care. – Albert D. Kallal Nov 14 '20 at 12:13
  • @AlbertD.Kallal This is not ASP.Net, this is VBScript syntax and ADODB if anything the ASP.Net tag should be removed as they are likely using Classic ASP. – user692942 Nov 15 '20 at 01:13
  • 1
    You cannot get an `OUTPUT` parameter before executing the stored procedure. You should have `Call oCmd.Execute(, , adExecuteNoRecords)` there before trying to retrieve the parameter. Also if you return a recordset you would need to close it before trying to read the parameter, in this case you don't so we specify `adExecuteNoRecords` as the `ExecuteOptionEnum`. – user692942 Nov 15 '20 at 01:19
  • 2
    @Lankymart Thank you very much for that - I was under the impression the SPEmailCheck statement handled the execution. I've been able to retrieve and check the procedure results as intended now. Just need to figure out how to send a MsgBox equivalent now. – Michael Alan Nollette Nov 16 '20 at 16:47
  • @AlbertD.Kallal sorry about that. Tags are edited. This is (obviously) far from my area of expertise, and I tagged based on the declared script language at the top. – Michael Alan Nollette Nov 16 '20 at 16:48

0 Answers0