Ask a Question related to ASP Database, Design and Development.
-
Vipul Pathak #1
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.
[url]http://www.ebotsoft.com[/url]
Vipul Pathak Guest
-
Trouble getting stored procedure return value!?
This is my first stored procedure so go easy on me. Procedure runs fine except I can't get a value into the return value @RecordCount. CREATE... -
Can't get return Value from Stored Procedure
I'm trying to get the @@IDENTITY value back from my stored procedure in T-SQL to use it in later code. I haven't been able to get it to work. ... -
ASP/ADO: Return a value from a Stored Procedure
I generally use the following code to call a stored procedure: sSQL = "Exec MySP " & param1 & ", " & param2 oConn.Execute (sSQL) .... or... -
Using stored procedure to return a whole row of data, without using record set?
I now know that I cannot use client application written in embedded SQL to receive record sets, that only an application using CLI can receive... -
Stored Procedure has both return value and data set (SqlDataReader)
Greetings! I met the same question as in ADO a few months ago. I'm working on MS SQL Server 2000. I have a stored procedure that returns a... -
Vipul Pathak #2 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.
[url]http://www.ebotsoft.com[/url]
Vipul Pathak Guest
-
Ken Schaefer #3 Re: Getting Return Value of Stored Procedure
[url]www.adopenstatic.com/faq/800a0bb9step2.asp?[/url]
Do you have Option Explicit at the top of your page?
Cheers
Ken
"Vipul Pathak" <vipulp@ebotsoft.com> wrote in message
news:Obf3zwmVDHA.2340@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.
: [url]http://www.ebotsoft.com[/url]
:
:
Ken Schaefer Guest
-
Ken Schaefer #4 Re: Getting Return Value of Stored Procedure
[url]www.adopenstatic.com/faq/800a0bb9step2.asp?[/url]
Do you have Option Explicit at the top of your page?
Cheers
Ken
"Vipul Pathak" <vipulp@ebotsoft.com> wrote in message
news:Obf3zwmVDHA.2340@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.
: [url]http://www.ebotsoft.com[/url]
:
:
Ken Schaefer Guest
-
Harag #5 Re: Getting Return Value of Stored Procedure
1. Dont X post.... people dont like it.
2. Always use Option Explicit... Its very useful!
HTH
AL
On Wed, 30 Jul 2003 14:18:34 +0530, "Vipul Pathak"
<vipulp@ebotsoft.com> wrote:
>No !Harag Guest
-
Ray at #6 Re: Getting Return Value of Stored Procedure
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" <vipulp@ebotsoft.com> wrote in message
news:Obf3zwmVDHA.2340@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=============================================== ======================
Ray at Guest
-
Ray at #7 Re: Getting Return Value of Stored Procedure
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" <vipulp@ebotsoft.com> wrote in message
news:Obf3zwmVDHA.2340@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=============================================== ======================
Ray at Guest
-
Bob Barrows #8 Re: Getting Return Value of Stored Procedure
Vipul Pathak wrote:
Good! It is far better to return a single value as a parameter rather than> I have the Following Code, that Executes a Stored Procedure and
> Attempt to read a Returned Integer Value from the StoredProc.
>
using a bulky recordset.
REM=============================================== ======================:-) Save yourself some typing - use short object names:> <BODY>
> <%
> Const adCmdStoredProc = 4
>
> Dim objConnection, objCommand, objParameter, sSQL
dim cn,cmd,param
These variable names have become almost standard among developers.
:-) No, no, no, no, no! You are using adCmdStoredProc, not adCmdText! That>
> 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'"
>
means you need to supply the NAME of the procedure for the CommandText
property! Just the name!
objCommand.CommandText = "RegisterMessage"
Good start. Now you need to create and append the rest of the parameters> 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
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
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]
No parentheses needed. You should tell the Command that it does not need to> objCommand.Execute()
create a recordset behind the scenes:
const adExecNoRecords = &H00000080
objCommand.Execute ,,adExecNoRecords
Do not use Parameters.Refresh in production code! It causes an extra>
> 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()
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
Bob Barrows Guest
-
Bob Barrows #9 Re: Getting Return Value of Stored Procedure
Vipul Pathak wrote:
Good! It is far better to return a single value as a parameter rather than> I have the Following Code, that Executes a Stored Procedure and
> Attempt to read a Returned Integer Value from the StoredProc.
>
using a bulky recordset.
REM=============================================== ======================:-) Save yourself some typing - use short object names:> <BODY>
> <%
> Const adCmdStoredProc = 4
>
> Dim objConnection, objCommand, objParameter, sSQL
dim cn,cmd,param
These variable names have become almost standard among developers.
:-) No, no, no, no, no! You are using adCmdStoredProc, not adCmdText! That>
> 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'"
>
means you need to supply the NAME of the procedure for the CommandText
property! Just the name!
objCommand.CommandText = "RegisterMessage"
Good start. Now you need to create and append the rest of the parameters> 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
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
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]
No parentheses needed. You should tell the Command that it does not need to> objCommand.Execute()
create a recordset behind the scenes:
const adExecNoRecords = &H00000080
objCommand.Execute ,,adExecNoRecords
Do not use Parameters.Refresh in production code! It causes an extra>
> 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()
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
Bob Barrows Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
