sp does not return right value but query analyzer does

[Patrick]

sorry if this is a repsot, but I can't see my 1st post. Lost my connection
in the middle of posting

I don't know what's happening here. I can run the sp in query analyzer and
(using PRINT) see that it returns a positive record number. But when I run
this function on that sp it returns -1. I used EM's SQL profiler and can see
that it's returning -1, there, too. But the same inputs in query analyzer
returns 2, a passing authenticate. How can I see what's being sent to SQL
server? I have SQL debugger turned on, but ...


Function DBAuthenticate( strUsername As String, strPassword As String ) As
Integer
Dim conMyData As SqlConnection
Dim cmdSelect As SqlCommand
Dim parmReturnValue As SqlParameter
Dim intResult As Integer

conMyData = New SqlConnection("workstation id=W2000;packet
size=4096;integrated security=SSPI;data source=W2000;" & _
"persist security info=False;initial catalog=trythis")

cmdSelect = New SqlCommand( "DBAuthenticate", conMyData )
cmdSelect.CommandType = CommandType.StoredProcedure
parmReturnValue = cmdSelect.Parameters.Add( "RETURN_VALUE",
SqlDbType.Int )
parmReturnValue.Direction = ParameterDirection.ReturnValue
cmdSelect.Parameters.Add( "@username", strUsername )
cmdSelect.Parameters.Add( "@password", strPassword )
conMyData.Open()
cmdSelect.ExecuteNonQuery()
intResult = cmdSelect.Parameters( "RETURN_VALUE" ).Value
conMyData.Close()
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!"
Else
lblMessage.Text = "Invalid Password!"
End If
End If
Return intResult
End Function

This code is from Sam's ASP.Net Unleashed 2nd edition. I've had to tweak a
few other things, but this looks like it should work.

TIA,

Patrick
psully at eatel dot net


ps: here's the sp

CREATE PROCEDURE DBAuthenticate

(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As

DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )

SELECT
@ID = IdentityCol,
@actualPassword = u_password
FROM UserList
WHERE u_username = @username

IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1


GO

 

[Keenan Newton]

Well this should work from what i can see, and not trying to setup a
tst environment to run it. I would verify that the value strUsername
is what you expect by setting a break point on it. Pay attention and
make sure your not setting any single quotes around it. As the
SqlClient provider will do that for you

 

[Patrick]

Thanks again Keenan. Usually I would think of that I believe, but I am not
used to troublshooting on this many levels at once. My VB and ASP classes
were around 2 years ago, lol. I don't know how to see the @variables but
will spend more time on that as that must be where the problem is.
Patrick

 

[Patrick]

It had double quotes around it, and shouldn't have had that! When I was
trying to figure out why I could not connect to sql server, earlier, I had
removed the variable arguments and passed empty stings, "". Thanks for the
hints!
Patrick

 

[Keenan Newton]

IMO I don't think the problem is with the @variables them selves. What
I would suggest checking are the strUsername and strPaqssword variables
as they are coming into the method. Just make sure they are what you
expect. Again everything seems normal, so I would look at the simple
things first.

 

[Mary Chipman [MSFT]]

A few suggestions:

-- the first statement in the stored procedure should be "SET NOCOUNT
ON".

-- Create an output parameter (or two) in the stored procedure instead
of relying on the return value. It helps to code explicit information
in output parameters so the client code can retrieve and branch
accordingly, which leads to the third suggestion:

-- Validate your input parameters and put some error handling in your
stored procedure code that feeds into the output parameter(s).

The more error handling you have in every layer, the easier it becomes
to debug complex processes.

--Mary