How to collect Return value from stored procedure with ADO 
Author Message
 How to collect Return value from stored procedure with ADO

Hi,

I need to build a stored procedure with a return value, that can be accessed from
an application like vb as well as another stored procedure.

It's a common scenorio for many applications , but i am not aware of it. please
help me in this.

I had written the stored procedure like the following

CREATE PROCEDURE ReturnValue AS



I can able to call it from another stored procedure like

CREATE PROCEDURE callout AS



But i am not sure of calling from VB .
The code i have written is as follows. here i set the output of execute method to
recordset object as it returns. How to collect the return value.

Dim obj As ADODB.Command
Dim obj2 As ADODB.Recordset
Dim x As Long
Set obj = New ADODB.Command

With obj
   .CommandType = adCmdStoredProc
   .CommandText = "ReturnValue"
   .ActiveConnection = "Provider=SQLOLEDB;" & _
                      "Server=si405;" & _
                      "UID=sa;" & _
                      "Pwd=pwd;" & _
                      "Database=sample;"

    Set obj2 = .Execute

End With

thanks,
Devender



Sun, 30 Nov 2003 18:36:47 GMT  
 How to collect Return value from stored procedure with ADO
Not sure if this is what you're looking for, but one way of doing this is
with an output parameter.

In the SP set an output parameter...

<snip>

CREATE PROCEDURE ReturnValue

AS



<end snip>

In VB;

<snip>

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngRetIdx As Long

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = m_Cnn1
Set prm = New ADODB.Parameter

cmd.CommandText = "ReturnValue"
cmd.CommandType = adCmdStoredProc

With prm
  .Name = "RetVal"
  .Type = adInteger
  .Direction = adParamOutput
End With

cmd.Parameters.Append prm

cmd.Execute

'Here's your number
lngRetIdx = cmd.Parameters("RetVal")

Set cmd = Nothing
Set prm = Nothing

<end snip>

Hope this is useful,

Mike


Hi,

I need to build a stored procedure with a return value, that can be accessed
from
an application like vb as well as another stored procedure.

It's a common scenorio for many applications , but i am not aware of it.
please
help me in this.

I had written the stored procedure like the following

CREATE PROCEDURE ReturnValue AS



I can able to call it from another stored procedure like

CREATE PROCEDURE callout AS



But i am not sure of calling from VB .
The code i have written is as follows. here i set the output of execute
method to
recordset object as it returns. How to collect the return value.

Dim obj As ADODB.Command
Dim obj2 As ADODB.Recordset
Dim x As Long
Set obj = New ADODB.Command

With obj
   .CommandType = adCmdStoredProc
   .CommandText = "ReturnValue"
   .ActiveConnection = "Provider=SQLOLEDB;" & _
                      "Server=si405;" & _
                      "UID=sa;" & _
                      "Pwd=pwd;" & _
                      "Database=sample;"

    Set obj2 = .Execute

End With

thanks,
Devender



Sun, 30 Nov 2003 22:09:18 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Stored Procedure Return Value and ADO question

2. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

3. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

4. ADO don't get output param or return value when stored procedure contains update

5. MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem

6. ADO: Value Returning Stored Procedures

7. Return value and Output values from Stored Procedure

8. how to get return value from store procedure

9. Capturing return value of Stored Procedure...

10. Return Value from Stored Procedure?

11. SQL Stored Procedure Return Value

12. Stored procedures return value error

 

 
Powered by phpBB® Forum Software