I am using classic ASP to call a stored procedure on SQL Server 2005. The code functions correctly in that if during registration I find the same email address already in the data table, the record is not inserted. If it's not in the table it gets inserted. This works when I run a select in the management studio after running the code. However, the return value is ALWASY coming back as 1, meaning it was successful (even if it wasn't successful and NOT inserted into the table).
HERE is the Procedure Code:
ALTER PROCEDURE [dbo].[usp_InsertRegistration]
(
@fname VARCHAR(20) = '',
@lname VARCHAR(30) = '',
@emailaddress VARCHAR(75) = '',
@profession tinyint,
@pass varchar(10) = '',
@iresult int output
)
AS
BEGIN
SET NOCOUNT ON
if exists(select email from dbo.tblVIPs where rtrim(upper(email)) = rtrim(upper(@emailaddress)))
SELECT @iresult = 0
else
INSERT dbo.tblVIPs(firstname,lastname,email, professionID,pword,registrationID,RegistrationDate)
Values(@fname, @lname,@emailaddress, @profession,@pass,newid(),getdate())
SELECT @iresult = 1
END
RETURN @iresult
HERE is the ASP Code:
dim objCmd,iresult
iresult = 0
set objCmd= Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = strConnection
objCmd.CommandText = "usp_InsertRegistration"
objCmd.CommandType = &H0004
objCmd.Parameters.Append objCmd.CreateParameter("@firstname",adVarChar,adParamInput,20,strFirstName)
objCmd.Parameters.Append objCmd.CreateParameter("@lastname",adVarChar,adParamInput,30,strLastName)
objCmd.Parameters.Append objCmd.CreateParameter("@emailaddress",adVarChar,adParamInput,75,strEmail)
objCmd.Parameters.Append objCmd.CreateParameter("@profession",adTinyInt,adParamInput,1,intProfessionID)
objCmd.Parameters.Append objCmd.CreateParameter("@pass",adVarChar,adParamInput,10,strPassword)
objCmd.Parameters.Append objCmd.CreateParameter("@iresult",adInteger,adParamOutput)
objCmd.Execute()
iresult=objCmd.Parameters("@iresult").Value
set objCmd=nothing
response.write iresult
What am I doing incorrectly that causes the stored procedure to ALWAYS return a 1?
Thank you for your help in advance!!