By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,813 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,813 IT Pros & Developers. It's quick & easy.

Getting Return Value of Stored Procedure

P: n/a
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 & "<BR>"
'Response.End()

objCommand.Execute()

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

objCommand.Parameters.Refresh()
For Each objParameter In objCommand.Parameters
Response.Write objParameter.Name & ": " & objParameter.Value & "<BR>"
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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
www.adopenstatic.com/faq/800a0bb9step2.asp?

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

Cheers
Ken

"Vipul Pathak" <vi****@ebotsoft.com> wrote in message
news:Ob**************@TK2MSFTNGP10.phx.gbl...
: 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 & "<BR>"
: 'Response.End()
:
: objCommand.Execute()
:
: IF objConnection.Errors.Count > 0 Then
: Response.Write "ERROR:<P>"
: Response.Write "Error # " & objConnection.Errors(0).Number & "<BR>"
: Response.Write "Error Description: " &
: objConnection.Errors(0).Description & "<BR>"
: Response.Write "Error Source: " & objConnection.Errors(0).Source &
"<BR>"
: Else
: Response.Write "Generated Message Id: " &
: objCommand.Parameters.Item("MsgId").Value & "<P>"
: End IF
:
: objCommand.Parameters.Refresh()
: For Each objParameter In objCommand.Parameters
: Response.Write objParameter.Name & ": " & objParameter.Value & "<BR>"
: 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
:
:
Jul 19 '05 #2

P: n/a
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

"Vipul Pathak" <vi****@ebotsoft.com> wrote in message
news:Ob**************@TK2MSFTNGP10.phx.gbl...
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=============================================== ======================

Jul 19 '05 #3

P: n/a
Vipul Pathak wrote:
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/ind...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:<P>"
Response.Write "Error # " & objConnection.Errors(0).Number & "<BR>"
Response.Write "Error Description: " &
objConnection.Errors(0).Description & "<BR>"
Response.Write "Error Source: " & objConnection.Errors(0).Source &
"<BR>" Else
Response.Write "Generated Message Id: " &
objCommand.Parameters.Item("MsgId").Value & "<P>"
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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.