Please Help!!, SQL 2000 and Return Values from SP

[SuperCyber]

Please help!! When I run the stored procedure in SQL I get the proper return values for "1", "2", and "3", but when I execute the ASP code I only get return values when they are equal to "1" and equal to "2". The return value for "3" comes back null in ASP code. Any help is GREATLY appreciated; I've been working on this for 3 days now.

Brian


-------------------ASP calling code---------------------
Dim cmdGetCust, rstGetCust, varLogin, varPassword, strID, strFirstName, strLastName, strLogin

varLogin = Request("LOGIN_EMAIL&quot
varPassword = Request("LOGIN_PASSWORD&quot

Set cmdGetCust = Server.CreateObject("ADODB.Command&quot
With cmdGetCust
.ActiveConnection = connAPS
.CommandText = "sproc_CustLogIn"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Login",adVarChar,adParamInput,50,varLogin)
.Parameters.Append .CreateParameter("@Password",adVarChar,adParamInput,50,varPassword)
.Parameters.Append .CreateParameter("@RETVAL",adInteger,adParamReturnValue)
Set rstGetCust = .Execute
strLogin = .Parameters("@RETVAL&quot
End With

IF strLogin = 3 THEN

strID = rstGetCust("custID&quot
strFirstName = rstGetCust("custFirstName&quot
strLastName = rstGetCust("custLastName&quot
Set rstGetCust = nothing

-------other code------

Response.Redirect"authorized_user_page.asp"
ELSEIF strLogin = 2 Then
Response.Redirect"CustFailedLogin.asp"
ELSEIF strLogin = 1 Then
Response.Redirect"unauthorized_user_page.asp"
END IF


-----------------SQL SP----------------------

Alter Procedure sproc_CustLogin
@Login varchar(50),
@Password varchar(50),
@RETVAL int = null OUTPUT
AS
DECLARE @chkPassword varchar(50)
set nocount on
-- Pull the record regardless of password
SELECT @chkPassword = custPassword
FROM tblCustomers
WHERE CONVERT(varbinary(50),custEmail) = CONVERT(varbinary(50),@Login)

IF @@ROWCOUNT > 0 --Found the login
BEGIN
SET @RETVAL =
CASE
WHEN CONVERT(varbinary(50),@chkPassword) = CONVERT(varbinary(50),@Password) THEN 3
ELSE 2
END

IF @RETVAL = 3
BEGIN
SELECT custID,custFirstName,custLastName
FROM tblCustomers
WHERE custEmail = @Login
END
END
ELSE
BEGIN
SET @RETVAL = 1
END
set nocount off

 

[rac2]

Hello Bryan,

I bumped into this when I first tried to use an OUTPUT parameter with a recordset. See the answer posted by dbrom in thread333-144936 and my problem post at thread333-142369 for other angles.

Richard