What am I doing wrong

[Clanger67]

I am trying to return a value to my vb app. When I run it in query analyzer I can get the return value and the recordset, but when I try in my vb app I get an empty value.

This is my stored proc:

CREATE PROCEDURE sp_FindUser
@UserName varChar(30)
AS

DECLARE @Found int

SELECT
UserName,
[Password],
PremScreen,
AdminScreen,
AdminUser

FROM tblFJ_Users

WHERE UserName = @UserName

IF @@ROWCOUNT > 0
BEGIN
SELECT @Found = 1
--PRINT @Found
RETURN @Found
END

ELSE
BEGIN
SELECT @Found = 0
--PRINT @Found
RETURN @Found
END


This is the vb code calling the procedure

Public Sub chkUser()

Dim rsChkUser As New ADODB.Recordset
Dim params As ADODB.Parameters

On Error GoTo Err_ChkUser

Set cmd.ActiveConnection = FJames_ADOCONNECT

With cmd
.CommandType = adCmdStoredProc
.CommandText = "sp_FindUser"
End With

Set params = cmd.Parameters

params.Append cmd.CreateParameter("Found", adInteger, adParamReturnValue, , -1)
params.Append cmd.CreateParameter("User", adVarChar, adParamInput, 30, m_strChkUser)

Set rsChkUser = cmd.Execute
'cmd.Execute
Debug.Print "Param value = " & cmd.Parameters("Found").Value

If cmd("Found").Value = 0 Then
MsgBox "User not found"
ElseIf cmd("Found").Value = 1 Then
m_strChkUser = rsChkUser!UserName
m_strPassword = rsChkUser!Password
m_strAdminUser = rsChkUser!AdminUser
m_strAdScreen = rsChkUser!AdminScreen
m_strPremScreen = rsChkUser!PremScreen

End If

Set cmd = Nothing


Exit Sub

Err_ChkUser:
Call ErrorTrap("clsLogin!ChkUser", True)

End Sub

If I change the following lines so that the Set line is not called I get the return value but not the recordset
'Set rsChkUser = cmd.Execute
cmd.Execute

Has anyone got any ideas

Thanks

Dave

 

[JohnEfford]

Should

params.Append cmd.CreateParameter("Found", adInteger, adParamReturnValue, , -1)

not be

params.Append cmd.CreateParameter("Found", adInteger, adParamReturnValue)

Plus your also need to have the '@' as part of your variable name. so:--

params.Append cmd.CreateParameter("@Found", adInteger, 
adParamReturnValue)
params.Append cmd.CreateParameter("@User", adVarChar, adParamInput, 30, m_strChkUser)

Cheers
John Efford

 

[donutman]

Replace cmd.Parameters("Found").Value with
cmd.Parameters(0).Value
Why mess with @Found you could just Return @@RowCount. But even better don't missuse the Return function which is there to report an error and simply use rsChkUser.recordcount
-Karl