467,858 Members | 1,462 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,858 developers. It's quick & easy.

Getting Return Value of Stored Procedure

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
  • viewed: 22339
Share:
3 Replies
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
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
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.

Similar topics

1 post views Thread by Seong-Kook Shin | last post: by
reply views Thread by CoolCyber | last post: by
1 post views Thread by csgraham74 | last post: by
4 posts views Thread by Mick Walker | last post: by
2 posts views Thread by Mick Walker | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.