Getting Return Value of Stored Procedure

Vipul Pathak · Jul 30, 2003

Hello Friends !

I have the Following Code, that Executes a Stored Procedure and Attempt to
read a Returned Integer Value from the StoredProc.
But It gives Error ...

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/C4U/DBOutputParameterTest.asp, line 25

Can some one Points Out the Problem ....

REM=====================================================================
<BODY>
<%
Const adCmdStoredProc = 4

Dim objConnection, objCommand, objParameter, sSQL

Set objConnection = Server.CreateObject("AdoDB.Connection")
Set objCommand = Server.CreateObject("AdoDB.Command")
Set objParameter = Server.CreateObject("AdoDB.Parameter")

'Dim objParameter 'As AdoDB.Parameter

sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts', '24304',
'Hello By Test Message', 'P', '2492331', 'VipulP: The Tester'"

objConnection.Open Session("CONNECTION_STRING")
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = sSQL
objCommand.CommandType = adCmdStoredProc

Set objParameter = objCommand.CreateParameter("Return", adInteger,
adParamReturnValue,,0)
objCommand.Parameters.Append objParameter
Response.Write "SQL: " & sSQL & " "
'Response.End()

objCommand.Execute()

IF objConnection.Errors.Count > 0 Then
Response.Write "ERROR:"
Response.Write "Error # " & objConnection.Errors(0).Number & " "
Response.Write "Error Description: " &
objConnection.Errors(0).Description & " "
Response.Write "Error Source: " & objConnection.Errors(0).Source & " "
Else
Response.Write "Generated Message Id: " &
objCommand.Parameters.Item("MsgId").Value & ""
End IF

objCommand.Parameters.Refresh()
For Each objParameter In objCommand.Parameters
Response.Write objParameter.Name & ": " & objParameter.Value & " "
Next
%>
</BODY>

REM=====================================================================

Thanks !

Ken Schaefer · Jul 30, 2003

www.adopenstatic.com/faq/800a0bb9step2.asp?

Do you have Option Explicit at the top of your page?

Cheers
Ken

: Hello Friends !
:
: I have the Following Code, that Executes a Stored Procedure and Attempt to
: read a Returned Integer Value from the StoredProc.
: But It gives Error ...
:
: ADODB.Command (0x800A0BB9)
: Arguments are of the wrong type, are out of acceptable range, or are in
: conflict with one another.
: /C4U/DBOutputParameterTest.asp, line 25
:
: Can some one Points Out the Problem ....
:
: REM=====================================================================
: <BODY>
: <%
: Const adCmdStoredProc = 4
:
: Dim objConnection, objCommand, objParameter, sSQL
:
: Set objConnection = Server.CreateObject("AdoDB.Connection")
: Set objCommand = Server.CreateObject("AdoDB.Command")
: Set objParameter = Server.CreateObject("AdoDB.Parameter")
:
: 'Dim objParameter 'As AdoDB.Parameter
:
: sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts', '24304',
: 'Hello By Test Message', 'P', '2492331', 'VipulP: The Tester'"
:
: objConnection.Open Session("CONNECTION_STRING")
: Set objCommand.ActiveConnection = objConnection
: objCommand.CommandText = sSQL
: objCommand.CommandType = adCmdStoredProc
:
: Set objParameter = objCommand.CreateParameter("Return", adInteger,
: adParamReturnValue,,0)
: objCommand.Parameters.Append objParameter
: Response.Write "SQL: " & sSQL & " "
: 'Response.End()
:
: objCommand.Execute()
:
: IF objConnection.Errors.Count > 0 Then
: Response.Write "ERROR:"
: Response.Write "Error # " & objConnection.Errors(0).Number & " "
: Response.Write "Error Description: " &
: objConnection.Errors(0).Description & " "
: Response.Write "Error Source: " & objConnection.Errors(0).Source &
" "
: Else
: Response.Write "Generated Message Id: " &
: objCommand.Parameters.Item("MsgId").Value & ""
: End IF
:
: objCommand.Parameters.Refresh()
: For Each objParameter In objCommand.Parameters
: Response.Write objParameter.Name & ": " & objParameter.Value & " "
: Next
: %>
: </BODY>
:
: REM=====================================================================
:
: Thanks !
:
:
: ------------------------------
: V I P U L P A T H A K
: eBot Technosoft Limited,
: Indore, (MP), India.
: http://www.ebotsoft.com
:
:

Ray at · Jul 30, 2003

I personally just use:

Set rsWhatever = objADO.Execute "EXEC sp_SomeStoredProc arg1, arg2"
sReturnValueFromStoredProc = rs.Fields.Item(0).Value
rsWhatever.Close
Set rsWhatever = Nothing

Ray at work

Bob Barrows · Jul 30, 2003

Vipul said:
I have the Following Code, that Executes a Stored Procedure and
Attempt to read a Returned Integer Value from the StoredProc.

Good! It is far better to return a single value as a parameter rather than
using a bulky recordset.

REM=====================================================================

<BODY>
<%
Const adCmdStoredProc = 4

Dim objConnection, objCommand, objParameter, sSQL

Save yourself some typing - use short object names:
dim cn,cmd,param

These variable names have become almost standard among developers.

Set objConnection = Server.CreateObject("AdoDB.Connection")
Set objCommand = Server.CreateObject("AdoDB.Command")
Set objParameter = Server.CreateObject("AdoDB.Parameter")

'Dim objParameter 'As AdoDB.Parameter

sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts',
'24304', 'Hello By Test Message', 'P', '2492331', 'VipulP: The
Tester'"

No, no, no, no, no! You are using adCmdStoredProc, not adCmdText! That
means you need to supply the NAME of the procedure for the CommandText
property! Just the name!

objCommand.CommandText = "RegisterMessage"

objConnection.Open Session("CONNECTION_STRING")
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = sSQL
objCommand.CommandType = adCmdStoredProc

Set objParameter = objCommand.CreateParameter("Return", adInteger,
adParamReturnValue,,0)
objCommand.Parameters.Append objParameter

Good start. Now you need to create and append the rest of the parameters
collection! And set their values. This can be tricky, but you can make the
task easier by using one of the stored procedure parameter code generators
out there. I've written one myself. It's available at
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

objCommand.Execute()

No parentheses needed. You should tell the Command that it does not need to
create a recordset behind the scenes:
const adExecNoRecords = &H00000080
objCommand.Execute ,,adExecNoRecords

IF objConnection.Errors.Count > 0 Then
Response.Write "ERROR:"
Response.Write "Error # " & objConnection.Errors(0).Number & " "
Response.Write "Error Description: " &
objConnection.Errors(0).Description & " "
Response.Write "Error Source: " & objConnection.Errors(0).Source &
" " Else
Response.Write "Generated Message Id: " &
objCommand.Parameters.Item("MsgId").Value & ""
End IF

objCommand.Parameters.Refresh()

Do not use Parameters.Refresh in production code! It causes an extra
time-consuming trip to the database. Anyways, you seem to have the wrong
idea about what this does. Parameters.Refresh is used to create the
Parameters collection so the individual Parameter objects' values can be set
BEFORE the command is executed. After the Command is executed, the Return
parameter (and any output parameters) will contain the value returned by the
procedure. Using Refresh after execution will clear those values.

HTH,
Bob Barrows

Returning recordsets from a stored procedure	4	Jul 27, 2007
Return value from Stored Procedure	16	Sep 16, 2008
The precision is invalid. -- Stored procedure,..	7	Feb 21, 2008
Fake progress bar whilst Stored Procedure runs using ASP	4	Jul 15, 2008
Using a stored procedure to return multiple recordsets with .Filter support	1	Nov 1, 2006
stored Procedure with argument	2	Apr 9, 2007
Trying to use a stored procedure	0	Feb 1, 2007
aspUpload: what am I doing wrong?	1	Feb 12, 2005

Getting Return Value of Stored Procedure

Vipul Pathak

Ken Schaefer

Ray at

Bob Barrows

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads