cdemott33
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("@loiginSuc cess = " & rsUser("loginSuccess") & "<br />")"
Here's the Stored Procedure I wrote:
... and this is my ASP page.
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("@loiginSuc
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
... 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)
%>
whats the purpose of Lıne 23
SELECT eMailAddress FROM login WHERE userName = @userName
SELECT eMailAddress FROM login WHERE userName = @userName
this code can be replaced
with
IF EXISTS (SELECT * FROM login WHERE @pwdHash = passwordHash)
Begin
SELECT eMailAddress FROM login WHERE userName = @userName
SET @loginSuccess = 1
end
ELSE
SET @loginSuccess = 0
with
SET @loginSuccess = (select sign(count(1)) from login WHERE userName = @userName and @pwdHash = passwordHash);
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
then use
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
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
...
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;
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.
An idea as to what could be wrong? Is my output parameter code correct?
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 />")
An idea as to what could be wrong? Is my output parameter code correct?
sqlUser.Parameters.Append sqlUser.CreateParameter("@loginSuccess", 11, 2, , adParamReturnValue)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or, if you go with output parameters, use
sqlUser.Parameters.Append (sqlUser.CreateParameter(" loginSucce ss", adInteger, adParamOutput))
and use
sqlUser.Parameters("loginS uccess").v alue
if it does not work add @ in front...
sqlUser.Parameters.Append (sqlUser.CreateParameter("
and use
sqlUser.Parameters("loginS
if it does not work add @ in front...
ASKER
Thank you! This worked!
Open in new window
try:Open in new window
Refer to https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/parameters-collection-ado