|
 |
asp_databases thread: Problem Writing Output Parameters
Message #1 by playhard@m... on Sat, 20 Jan 2001 04:14:10 -0000
|
|
Hi,
Does anyone know how to access the values in ouput parameters? I run my
code below, it calls the stored procedure fine, and runs, no error
messages, but when I try to access (even just write out) the values in my
output parameters, no values are output.
I've also tried accessing the output values with, for example,
Response.Write @Param3 but then I get an ASP error message:
Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/phaseforward/login.asp, line 43, column 15
Response.Write @Param3
When I run the stored proc. directly agains the db, it runs fine, and
returns the values of the output parameters.
It seems like when I run it through the ASP code, the ouptput values are
for some reason not being sent back to the ASP parameters.
I am working with a SQL Server 7 db.
Any help would really be appreciated.
--------------^
ASP CODE:
Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = "sp_ValidateUser"
.CommandType = adCmdStoredProc
Call .Parameters.Append(.CreateParameter("@Param1", adChar,adParamInput,
20, strUsername))
Call .Parameters.Append(.CreateParameter("@Param2", adChar,adParamInput,
10, strPassword))
Call .Parameters.Append(.CreateParameter("@Param3",
adChar,adParamOutput, 20, out_Username))
Call .Parameters.Append(.CreateParameter("@Param4",
adChar,adParamOutput, 10, out_Password))
'If (Param3.Value <> null) AND (Param4.Value <> null) Then
'Response.Redirect "welcomeform.asp"
'Else
'Response.Redirect "index.asp"
'End If
Response.Write Param3
Response.Write Param4
End With
objConnection.Close
Set objCommand = Nothing
Set objConnection = Nothing
STORED PROCEDURE:
CREATE PROCEDURE sp_ValidateUser
@t_Username char(20)= null,
@t_Password char(10) = null,
@out_Username char(20) OUTPUT,
@out_Password char(10) OUTPUT
AS
SELECT @out_Username = pa.Username, @out_Password = pa.Password
FROM PersonApplication AS pa
WHERE (pa.UserName = @t_Username) AND (pa.Password = @t_Password)
Message #2 by Imar Spaanjaars <Imar@S...> on Sat, 20 Jan 2001 09:44:21 +0100
|
|
Try the following after you have set up the command (including SP name) but
before the parameters:
objCommand.Parameters.Refresh
Dim aParam
for each aParam in objCommand.Parameters
Response.Write("Param " & aParam.name & " is of type " &
aParam.type & "<BR>")
next
This will give you list of all the parameters the SP expects. It will
return a number which you can "translate" to the constant by looking it up
in the file adovbs.inc, under the section: DataTypeEnum Values
Afterwards when you have the list, remove the Refresh command again, since
this will add all parameters to the command object for you, and you can't
of course do that twice.
HtH
Imar
At 04:14 AM 1/20/2001 +0000, you wrote:
>Hi,
>Does anyone know how to access the values in ouput parameters? I run my
>code below, it calls the stored procedure fine, and runs, no error
>messages, but when I try to access (even just write out) the values in my
>output parameters, no values are output.
>I've also tried accessing the output values with, for example,
>Response.Write @Param3 but then I get an ASP error message:
>Error Type:
>Microsoft VBScript compilation (0x800A0408)
>Invalid character
>/phaseforward/login.asp, line 43, column 15
>Response.Write @Param3
>
>When I run the stored proc. directly agains the db, it runs fine, and
>returns the values of the output parameters.
Message #3 by "Walter Burrough" <lists@c...> on Sat, 20 Jan 2001 12:12:38 -0000
|
|
To access the output parameter's value use:
strValue = objCommmand.Parameters("@Param4")
What does the "out_Password" parameter in your create&append line do - I've never seen it before?
walter
-----Original Message-----
From: playhard@m... [mailto:playhard@m...]
Sent: 20 January 2001 04:14
To: ASP Databases
Subject: [asp_databases] Problem Writing Output Parameters
Hi,
Does anyone know how to access the values in ouput parameters? I run my
code below, it calls the stored procedure fine, and runs, no error
messages, but when I try to access (even just write out) the values in my
output parameters, no values are output.
I've also tried accessing the output values with, for example,
Response.Write @Param3 but then I get an ASP error message:
Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/phaseforward/login.asp, line 43, column 15
Response.Write @Param3
When I run the stored proc. directly agains the db, it runs fine, and
returns the values of the output parameters.
It seems like when I run it through the ASP code, the ouptput values are
for some reason not being sent back to the ASP parameters.
I am working with a SQL Server 7 db.
Any help would really be appreciated.
--------------^
ASP CODE:
Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = "sp_ValidateUser"
.CommandType = adCmdStoredProc
Call .Parameters.Append(.CreateParameter("@Param1", adChar,adParamInput,
20, strUsername))
Call .Parameters.Append(.CreateParameter("@Param2", adChar,adParamInput,
10, strPassword))
Call .Parameters.Append(.CreateParameter("@Param3",
adChar,adParamOutput, 20, out_Username))
Call .Parameters.Append(.CreateParameter("@Param4",
adChar,adParamOutput, 10, out_Password))
'If (Param3.Value <> null) AND (Param4.Value <> null) Then
'Response.Redirect "welcomeform.asp"
'Else
'Response.Redirect "index.asp"
'End If
Response.Write Param3
Response.Write Param4
End With
objConnection.Close
Set objCommand = Nothing
Set objConnection = Nothing
STORED PROCEDURE:
CREATE PROCEDURE sp_ValidateUser
@t_Username char(20)= null,
@t_Password char(10) = null,
@out_Username char(20) OUTPUT,
@out_Password char(10) OUTPUT
AS
SELECT @out_Username = pa.Username, @out_Password = pa.Password
FROM PersonApplication AS pa
WHERE (pa.UserName = @t_Username) AND (pa.Password = @t_Password)
Message #4 by "Dallas Martin" <dmartin@z...> on Sat, 20 Jan 2001 10:47:54 -0500
|
|
I would change the sproc parameters from the char() datatype to varchar().
I know this places a little more stress on the server, but it will save you
headaches
in the future. Also, change the datatype in the table from char() to
varchar(). I had
an occassion when I was confounded by code that would work in a VB/Access
environment
but failed in an VB/SQL environment. By changing the SQL datatypes from
char() to varchar()
everything worked.
----- Original Message -----
From: <playhard@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, January 19, 2001 11:14 PM
Subject: [asp_databases] Problem Writing Output Parameters
> Hi,
> Does anyone know how to access the values in ouput parameters? I run my
> code below, it calls the stored procedure fine, and runs, no error
> messages, but when I try to access (even just write out) the values in my
> output parameters, no values are output.
> I've also tried accessing the output values with, for example,
> Response.Write @Param3 but then I get an ASP error message:
> Error Type:
> Microsoft VBScript compilation (0x800A0408)
> Invalid character
> /phaseforward/login.asp, line 43, column 15
> Response.Write @Param3
>
> When I run the stored proc. directly agains the db, it runs fine, and
> returns the values of the output parameters.
>
> It seems like when I run it through the ASP code, the ouptput values are
> for some reason not being sent back to the ASP parameters.
>
> I am working with a SQL Server 7 db.
>
> Any help would really be appreciated.
> --------------^
> ASP CODE:
>
> Set objCommand = Server.CreateObject("ADODB.Command")
> With objCommand
> .ActiveConnection = objConnection
> .CommandText = "sp_ValidateUser"
> .CommandType = adCmdStoredProc
> Call .Parameters.Append(.CreateParameter("@Param1", adChar,adParamInput,
> 20, strUsername))
> Call .Parameters.Append(.CreateParameter("@Param2", adChar,adParamInput,
> 10, strPassword))
> Call .Parameters.Append(.CreateParameter("@Param3",
> adChar,adParamOutput, 20, out_Username))
> Call .Parameters.Append(.CreateParameter("@Param4",
> adChar,adParamOutput, 10, out_Password))
>
> 'If (Param3.Value <> null) AND (Param4.Value <> null) Then
> 'Response.Redirect "welcomeform.asp"
> 'Else
> 'Response.Redirect "index.asp"
> 'End If
>
> Response.Write Param3
> Response.Write Param4
>
> End With
>
> objConnection.Close
>
> Set objCommand = Nothing
> Set objConnection = Nothing
>
> STORED PROCEDURE:
>
> CREATE PROCEDURE sp_ValidateUser
> @t_Username char(20)= null,
> @t_Password char(10) = null,
> @out_Username char(20) OUTPUT,
> @out_Password char(10) OUTPUT
>
> AS
>
> SELECT @out_Username = pa.Username, @out_Password = pa.Password
> FROM PersonApplication AS pa
> WHERE (pa.UserName = @t_Username) AND (pa.Password = @t_Password)
|
|
|
View Cart
