how to create my Validate Login SP IN sql 2000 ?

hi,all:

I'm new to Sql 2000,now I have a login asp.net page and I used the sql 2000 database.

my login page included a user id and password need user inputed. if the user input the correct userid

and password ,IE will transfer to main page,or there will show eorror message in login page.

my SP like this:

CREATE PROCEDURE dbo.Usp_Accounts_ValidateLogin
@.userid char(4) ,
@.EncPassword binary
AS
if (select count(*) from hhmxUserData whereUserid=@.userid andUserPWD=@.EncPassword) >0

return 1

else

return 0
GO
my asp.net code like this:

dim result As Integer

dim rowsAffected as integer

myConnection.Open()
Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters)
rowsAffected = command.ExecuteNonQuery
result = CInt(command.Parameters("ReturnValue").Value)
myConnection.Close()

Return result

I test it in sql 2000,it's ok.but when I performed it and retrieve the "returnValue", it still return 0 .

so how can I create my correct SP ?

thanks so much.

Change your datatype of userid from char(4) to varchar(4). When you use CHAR, if your user enter the userid which is less than 4 characters in length spaces will be added at the end and your search query may not look like what is should be.

|||

Yes,my UserID is fixed 4 characters length (like: 1012,1008),I think it's not problem,the point is I'm always get the wrong returnValue in my asp.net code when I performed my SP.

So maybe my SP has problem ?

|||

I would recommend you use OUTPUT parameters. check out Books On Line for more info.

check the 2nd part inthis articleto retrieve the output from OUTPUT variable.