Link to home
Start Free TrialLog in
Avatar of cdemott33
cdemott33Flag for United States of America

asked on

Classic ASP - Display returned sql output parameter

Hello,

I'm having trouble getting a Stored Procedure's Output parameter value.  My code is below.    The output parameter is @loginSuccess.  The error I'm getting is "Item cannot be found in the collection corresponding to the requested name or ordinal." and it's pointing to the Output parameter line "Response.write("@loiginSuccess = " & rsUser("loginSuccess") & "<br />")"

Here's the Stored Procedure I wrote:

ALTER PROCEDURE [dbo].[test] 
	@username		NVARCHAR(50),
	@password		NVARCHAR(50), 
	@loginSuccess	BIT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @salt nvarchar(36)
	DECLARE @pwdHash varbinary(500)


	SELECT @salt = salt
	FROM login
	WHERE userName = @username 

	SET @pwdHash = HASHBYTES('SHA1', @password+@salt)

	IF EXISTS (SELECT * FROM login WHERE @pwdHash = passwordHash)
		Begin
			SELECT eMailAddress FROM login WHERE userName = @userName
			SET @loginSuccess = 1
		end
	ELSE
		SET @loginSuccess = 0
END

Open in new window


... and this is my ASP page.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="ssi/x_str_inc/openConn.asp" -->
<!--#include file="ssi/adovbs.asp" -->
<%

	strLogin = "sjones"
	strPassword = "Bob24!"

	Set Conn = CreateObject("ADODB.Connection")
	Conn.ConnectionString = connectionStr
	Conn.Open

	Set sqlUser = Server.CreateObject("ADODB.Command")
	sqlUser.ActiveConnection = Conn
	sqlUser.Prepared = true
	sqlUser.commandtext	= 	"test"
	sqlUser.CommandType = 	adCmdStoredProc

	sqlUser.Parameters.Append sqlUser.CreateParameter("@Username", 200, 1, 150, strLogin) 
	sqlUser.Parameters.Append sqlUser.CreateParameter("@Password", 200, 1, 150, strPassword) 
	sqlUser.Parameters.Append sqlUser.CreateParameter("@loginSuccess", 11, 2, , adParamReturnValue)
	
	set rsUser = sqlUser.execute
	
	If Not rsUser.eof Then
		strUserFound = "True"
		Response.write("@loiginSuccess = " & rsUser("loginSuccess") & "<br />")		
		Response.write("eMailAddress = " & rsUser("eMailAddress") & "<br />")
	Else
		strUserFound = "False"
	End If
	
	
	Conn.close
	set Conn = Nothing
	
	Response.write("strUserFound = " & strUserFound)

%>

Open in new window

Avatar of hielo
hielo
Flag of Wallis and Futuna image

Instead of:
rsUser("loginSuccess")

Open in new window

try:
sqlUser.parameters("@loginSuccess")

Open in new window

Refer to https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/parameters-collection-ado
whats the purpose of Lıne 23

SELECT eMailAddress FROM login WHERE userName = @userName
this code can be replaced
IF EXISTS (SELECT * FROM login WHERE @pwdHash = passwordHash)
		Begin
			SELECT eMailAddress FROM login WHERE userName = @userName
			SET @loginSuccess = 1
		end
	ELSE
		SET @loginSuccess = 0

Open in new window


with
  SET @loginSuccess = (select sign(count(1)) from login WHERE userName = @userName and @pwdHash = passwordHash);

Open in new window

oops, I guess you want one result + one output parameter :)

why not one email as output, if it is empty then not successful, if it is something then it is ok
ALTER PROCEDURE [dbo].[test] 
	@username		NVARCHAR(50),
	@password		NVARCHAR(50), 
	@email	VARCHAR(256) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @salt nvarchar(36)
	DECLARE @pwdHash varbinary(500)

	SELECT @salt = salt
	FROM login
	WHERE userName = @username 

	SET @pwdHash = HASHBYTES('SHA1', @password+@salt)
        select @email = eMailAddress FROM login WHERE userName = @userName and @pwdHash = passwordHash;
END

Open in new window


then use
	sqlUser.Parameters.Append sqlUser.CreateParameter("@Username", 200, 1, 150, strLogin) 
	sqlUser.Parameters.Append sqlUser.CreateParameter("@Password", 200, 1, 150, strPassword) 
	sqlUser.Parameters.Append sqlUser.CreateParameter("@email", 200, 1, 256, adParamReturnValue)

	set rsUser = sqlUser.execute
	dim email = sqlUser.parameters("@email")
	If email <> "" Then ' user exists
	  ' no such user
	Else
	  ' user login ok
	End If
...

Open in new window

even shorter

ALTER PROCEDURE [dbo].[test] 
	@username		NVARCHAR(50),
	@password		NVARCHAR(50), 
	@email	VARCHAR(256) OUTPUT
AS
BEGIN
  SELECT @email = eMailAddress
    FROM login
   WHERE userName = @userName and passwordHash = HASHBYTES('SHA1', @password+salt);
END;

Open in new window

Avatar of cdemott33

ASKER

Thank you,  Huseyin, for all the suggested SP changes.  I'll get to work.

As far as getting the returned OUTPUT parameter value I'm still having issues.  

Heilo, I did what you had suggested and executed the page.  I'm no longer getting the "Item cannot be found... " error but when I run the ASP page nothing is displayed.
Response.write("@loiginSuccess = " & sqlUser.parameters("@loginSuccess") & "<br />")

Open in new window


An idea as to what could be wrong?  Is my output parameter code correct?
sqlUser.Parameters.Append sqlUser.CreateParameter("@loginSuccess", 11, 2, , adParamReturnValue)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or, if you go with output parameters, use

sqlUser.Parameters.Append (sqlUser.CreateParameter("loginSuccess", adInteger, adParamOutput))

and use

sqlUser.Parameters("loginSuccess").value

if it does not work add @ in front...
Thank you!  This worked!