Getting Return Value of Stored Procedure - ASP / Active Server Pages

Vipul Pathak

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 & " "
'Response.End()

objCommand.Exec ute()

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

objCommand.Para meters.Refresh( )
For Each objParameter In objCommand.Para meters
Response.Write objParameter.Na me & ": " & objParameter.Va lue & " "
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

Subscribe Reply

22928

Ken Schaefer

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 & " "
: 'Response.End()
:
: objCommand.Exec ute()
:
: IF objConnection.E rrors.Count > 0 Then
: Response.Write "ERROR:"
: Response.Write "Error # " & objConnection.E rrors(0).Number & " "
: Response.Write "Error Description: " &
: objConnection.E rrors(0).Descri ption & " "
: Response.Write "Error Source: " & objConnection.E rrors(0).Source &
" "
: Else
: Response.Write "Generated Message Id: " &
: objCommand.Para meters.Item("Ms gId").Value & ""
: End IF
:
: objCommand.Para meters.Refresh( )
: For Each objParameter In objCommand.Para meters
: Response.Write objParameter.Na me & ": " & objParameter.Va lue & " "
: 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

Ray at

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

Bob Barrows

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:"
Response.Write "Error # " & objConnection.E rrors(0).Number & " "
Response.Write "Error Description: " &
objConnection.E rrors(0).Descri ption & " "
Response.Write "Error Source: " & objConnection.E rrors(0).Source &
" " Else
Response.Write "Generated Message Id: " &
objCommand.Para meters.Item("Ms gId").Value & ""
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

Similar topics

8088

Calling Executables from SQL Server and getting return value.

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?

Microsoft SQL Server

2561

getting return value from function without return statement.

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) {

C / C++

2856

Opening modal window and getting return value in C#

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>"); }

ASP.NET

883

error in getting return value

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...

.NET Framework

3255

Output value stored procedure dotnet

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)

ASP.NET

2190

getting return from srtored procedure

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

ASP / Active Server Pages

2219

Getting Return Value From Stored Proceedure.

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

ASP.NET

1801

Getting Return Value From Stored Proccedure (Part 2)

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"

ASP.NET

2247

Getting Values from Stored Procedure

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

ASP / Active Server Pages

9579

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9422

Changing the language in Windows 10

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,...

Windows Server

10208

Problem With Comparison Operator <=> in G++

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...

C / C++

9987

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9857

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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...

General

8867

AI Job Threat for Devs

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...

Career Advice

5294

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

3952

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2812

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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...

General