ASP and SQL

xerouk · 30 Sep 2010 at 23:19

Hi bit of a long question here i know but i wanted to make sure you had all the info, I am trying to send data to an SQL stored procedure and return a value to my asp page based on the results.

----------------------------------------------------------------------

@username varchar(12),
@password varchar(12),
@Return varchar(20) Output

AS

Select *
From tblUsers
Where Username Like @username AND Password Like @password

Select @Return = @@rowcount

Return @Return

----------------------------------------------------------------------

This should return 1 if the username and pass are correct and 0 if incorrect if i understand it correctly.

----------------------------------------------------------------------
ASP Code

paramOutput = cmdStoredProc.CreateParameter("@Return", adchar, adParamOutput, 20, "")
cmdStoredProc.Parameters.Append(paramOutput)
Response.Write(cmdStoredProc.Parameters("@Return").Value)

This is the asp code im using to create, pass and retrieve the variable

When i run the SP on the server it executes perfectly and gives me 1 when i use the correct details and 0 when i dont, but when i do the same from asp it returns 0 all the time. sorry for the long post but any help is appreciated

Pho · 30 Sep 2010 at 23:54

With regards to your stored procedure I wouldn't use LIKE but use = instead. I'd also make @Return an INT or a BIT (true/false) depending on your needs.

Without seeing all your ASP code it's hard to say, are you even querying the database? You seem to be missing all that code between cmdStoredProc.Parameters.Append(paramOutput) and Response.Write(cmdStoredProc.Parameters("@Return") .Value) - you're just displaying the default value of this parameter here so it'll likely always be zero.

Pho · 30 Sep 2010 at 23:59

This is butchered from some working code I have to give you an idea of how to do it. Replace the name of the stored procedure with your own:

PHP:

' Execute query
cmdStoredProc.CommandText = "[User].[Login]"
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@return", adInteger, adParamReturnValue, 4)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@Username", adVarChar, adParamInput, -1, Username)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@Password", adInteger, adParamInput, -1, Password)
cmdStoredProc.execute()

' Get result
user_login = cmdStoredProc.parameters(0).value

Note this is only reading the return value (Return @Return) not your output parameter, if this works you can add that in later.

edit: you might need to stick this at the very top of your ASP file for the constants adVarChar, adInteger to work:

xerouk · 2 Oct 2010 at 10:17

Works perfectly thank you very much for your responses

Pho · 2 Oct 2010 at 13:05

Nice

. I completely missed the fact that in the above example I set @password to an integer rather than adVarChar - but presumably you found that

.