473,881 Members | 1,704 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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/DBOutputParamet erTest.asp, line 25

Can some one Points Out the Problem ....

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

Dim objConnection, objCommand, objParameter, sSQL

Set objConnection = Server.CreateOb ject("AdoDB.Con nection")
Set objCommand = Server.CreateOb ject("AdoDB.Com mand")
Set objParameter = Server.CreateOb ject("AdoDB.Par ameter")

'Dim objParameter 'As AdoDB.Parameter

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

objConnection.O pen Session("CONNEC TION_STRING")
Set objCommand.Acti veConnection = objConnection
objCommand.Comm andText = sSQL
objCommand.Comm andType = adCmdStoredProc

Set objParameter = objCommand.Crea teParameter("Re turn", adInteger,
adParamReturnVa lue,,0)
objCommand.Para meters.Append objParameter
Response.Write "SQL: " & sSQL & "<BR>"
'Response.End()

objCommand.Exec ute()

IF objConnection.E rrors.Count > 0 Then
Response.Write "ERROR:<P>"
Response.Write "Error # " & objConnection.E rrors(0).Number & "<BR>"
Response.Write "Error Description: " &
objConnection.E rrors(0).Descri ption & "<BR>"
Response.Write "Error Source: " & objConnection.E rrors(0).Source & "<BR>"
Else
Response.Write "Generated Message Id: " &
objCommand.Para meters.Item("Ms gId").Value & "<P>"
End IF

objCommand.Para meters.Refresh( )
For Each objParameter In objCommand.Para meters
Response.Write objParameter.Na me & ": " & objParameter.Va lue & "<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
3 22932
www.adopenstatic.com/faq/800a0bb9step2.asp?

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

Cheers
Ken

"Vipul Pathak" <vi****@ebotsof t.com> wrote in message
news:Ob******** ******@TK2MSFTN GP10.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/DBOutputParamet erTest.asp, line 25
:
: Can some one Points Out the Problem ....
:
: REM============ =============== =============== =============== ============
: <BODY>
: <%
: Const adCmdStoredProc = 4
:
: Dim objConnection, objCommand, objParameter, sSQL
:
: Set objConnection = Server.CreateOb ject("AdoDB.Con nection")
: Set objCommand = Server.CreateOb ject("AdoDB.Com mand")
: Set objParameter = Server.CreateOb ject("AdoDB.Par ameter")
:
: 'Dim objParameter 'As AdoDB.Parameter
:
: sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts', '24304',
: 'Hello By Test Message', 'P', '2492331', 'VipulP: The Tester'"
:
: objConnection.O pen Session("CONNEC TION_STRING")
: Set objCommand.Acti veConnection = objConnection
: objCommand.Comm andText = sSQL
: objCommand.Comm andType = adCmdStoredProc
:
: Set objParameter = objCommand.Crea teParameter("Re turn", adInteger,
: adParamReturnVa lue,,0)
: objCommand.Para meters.Append objParameter
: Response.Write "SQL: " & sSQL & "<BR>"
: 'Response.End()
:
: objCommand.Exec ute()
:
: IF objConnection.E rrors.Count > 0 Then
: Response.Write "ERROR:<P>"
: Response.Write "Error # " & objConnection.E rrors(0).Number & "<BR>"
: Response.Write "Error Description: " &
: objConnection.E rrors(0).Descri ption & "<BR>"
: Response.Write "Error Source: " & objConnection.E rrors(0).Source &
"<BR>"
: Else
: Response.Write "Generated Message Id: " &
: objCommand.Para meters.Item("Ms gId").Value & "<P>"
: End IF
:
: objCommand.Para meters.Refresh( )
: For Each objParameter In objCommand.Para meters
: Response.Write objParameter.Na me & ": " & objParameter.Va lue & "<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_SomeStoredPr oc arg1, arg2"
sReturnValueFro mStoredProc = rs.Fields.Item( 0).Value
rsWhatever.Clos e
Set rsWhatever = Nothing

Ray at work

"Vipul Pathak" <vi****@ebotsof t.com> wrote in message
news:Ob******** ******@TK2MSFTN GP10.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/DBOutputParamet erTest.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.CreateOb ject("AdoDB.Con nection")
Set objCommand = Server.CreateOb ject("AdoDB.Com mand")
Set objParameter = Server.CreateOb ject("AdoDB.Par ameter")

'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.Comm andText = "RegisterMessag e"
objConnection.O pen Session("CONNEC TION_STRING")
Set objCommand.Acti veConnection = objConnection
objCommand.Comm andText = sSQL
objCommand.Comm andType = adCmdStoredProc

Set objParameter = objCommand.Crea teParameter("Re turn", adInteger,
adParamReturnVa lue,,0)
objCommand.Para meters.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.Exec ute()
No parentheses needed. You should tell the Command that it does not need to
create a recordset behind the scenes:
const adExecNoRecords = &H00000080
objCommand.Exec ute ,,adExecNoRecor ds

IF objConnection.E rrors.Count > 0 Then
Response.Write "ERROR:<P>"
Response.Write "Error # " & objConnection.E rrors(0).Number & "<BR>"
Response.Write "Error Description: " &
objConnection.E rrors(0).Descri ption & "<BR>"
Response.Write "Error Source: " & objConnection.E rrors(0).Source &
"<BR>" Else
Response.Write "Generated Message Id: " &
objCommand.Para meters.Item("Ms gId").Value & "<P>"
End IF

objCommand.Para meters.Refresh( )


Do not use Parameters.Refr esh 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.Refr esh 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
8095
by: Chandra Mohan | last post by:
Hi All, I have a requirement of calling a Executable from SQL Server. I know we can use - EXEC @result = Master..xp_cmdshell @<command_string> However I want to get the return value (int) from the executable. xp_cmdshell always returns 0 or 1. How can the executable return code say 99 be obtained in SQL Server?
1
2563
by: Seong-Kook Shin | last post by:
Hi. Just curiocity, Because of pre-ANSI C, it is possible to have a function without specifying return type of a function (which makes the return type 'int', though) and give no 'return' statement. For example: foo(int a, int b) {
1
2859
by: WLF | last post by:
I have the following function (C# behind aspx page): private void ButtonNewSupplier_Click(object sender, System.EventArgs e) { Response.Write("<script language=javascript>window.showModalDialog('" + Const.sPPAddSupplier + "','_blank','left=50,top=50,toolbar=false,status=yes,directories=false,menubar=false,scrollbars=true,copyhistory=false,width=500,height=500');</script>"); }
0
886
by: CoolCyber | last post by:
Hi, I wrote a simple webservice to insert data into db from the values sent to it. It works partially. very first time after system restart it works properly. after that it works partially. Partially means the webservice is invoked and the data is inserted, but the calling page doen't receive any return value and it timesout. I get the following error: System.Net.WebException: The operation has timed out at...
1
3258
by: csgraham74 | last post by:
Hi Guys, Im attempting to get an output value from my stored procedure. Im pulling my hair out at this one. any help appreciated. my code is as follows: Dim cnstring As String Dim iConn As New SqlClient.SqlConnection(strRegistry)
5
2194
by: noLoveLusT | last post by:
hi everyone i am very very new to the sql server (2 days actually and ) so far i learned creating SPs etc but couldnt workout how to get return value from my prodecure my sp as follows ________________________ CREATE PROCEDURE . @PageIndex INT, @PageSize INT, @Total INT OUTPUT
4
2223
by: Mick Walker | last post by:
Hi Everyone, I am stumped here. I have the following stored proceedure:P CREATE PROCEDURE . @SupplierSKU varchar(50), @RetVal int AS Select @Retval = count(*) from dbo.ImportLines Where = @SupplierSKU
2
1806
by: Mick Walker | last post by:
Public Sub CheckProduct(ByVal _ConnString As String, ByVal ProductList As List(Of Import_ImportLines.Lines)) Dim ReturnValue As Integer = 0 ' Our Return Value Dim conn As New SqlConnection Dim cmd As New SqlCommand conn.ConnectionString = _ConnString cmd.Connection = conn cmd.CommandType = Data.CommandType.StoredProcedure cmd.CommandText = "dbo.ImportLinesProductExists"
4
2251
rrocket
by: rrocket | last post by:
I currently have this that sends a value to the DB and should return the information I need: Set RS = createobject("ADODB.Connection") Set x1 = server.CreateObject("ADODB.Command") With x1 .ActiveConnection = gObjConnect .CommandType = adCmdStoredProc .CommandText = "s_FRCWP_OpenSPInvoiceList" .Parameters.Append .CreateParameter("@DirectoryId", adInteger,adParamInput,,94) .Execute
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10716
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10399
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.