sadoman
asked on
Return Value from Stored Procedure is not correct
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_InsertRegistrat ion]
(
@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,last name,email , professionID,pword,registr ationID,Re gistration Date)
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("@f irstname", adVarChar, adParamInp ut,20,strF irstName)
objCmd.Parameters.Append objCmd.CreateParameter("@l astname",a dVarChar,a dParamInpu t,30,strLa stName)
objCmd.Parameters.Append objCmd.CreateParameter("@e mailaddres s",adVarCh ar,adParam Input,75,s trEmail)
objCmd.Parameters.Append objCmd.CreateParameter("@p rofession" ,adTinyInt ,adParamIn put,1,intP rofessionI D)
objCmd.Parameters.Append objCmd.CreateParameter("@p ass",adVar Char,adPar amInput,10 ,strPasswo rd)
objCmd.Parameters.Append objCmd.CreateParameter("@i result",ad Integer,ad ParamOutpu t)
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!!
HERE is the Procedure Code:
ALTER PROCEDURE [dbo].[usp_InsertRegistrat
(
@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,last
Values(@fname, @lname,@emailaddress, @profession,@pass,newid(),
SELECT @iresult = 1
END
RETURN @iresult
HERE is the ASP Code:
dim objCmd,iresult
iresult = 0
set objCmd= Server.CreateObject("ADODB
objCmd.ActiveConnection = strConnection
objCmd.CommandText = "usp_InsertRegistration"
objCmd.CommandType = &H0004
objCmd.Parameters.Append objCmd.CreateParameter("@f
objCmd.Parameters.Append objCmd.CreateParameter("@l
objCmd.Parameters.Append objCmd.CreateParameter("@e
objCmd.Parameters.Append objCmd.CreateParameter("@p
objCmd.Parameters.Append objCmd.CreateParameter("@p
objCmd.Parameters.Append objCmd.CreateParameter("@i
objCmd.Execute()
iresult=objCmd.Parameters(
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!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much!!!
btw, you actually don't need to use the RETURN @iresult ... the OUTPUT paramter stuff will handle the "return" of the value
Open in new window