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...
CREATE PROCEDURE ReturnValue
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
.Name = "RetVal"
.Type = adInteger
.Direction = adParamOutput
'Here's your number
lngRetIdx = cmd.Parameters("RetVal")
Set cmd = Nothing
Set prm = Nothing
Hope this is useful,
I need to build a stored procedure with a return value, that can be accessed
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.
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
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
.CommandType = adCmdStoredProc
.CommandText = "ReturnValue"
.ActiveConnection = "Provider=SQLOLEDB;" & _
"Server=si405;" & _
"UID=sa;" & _
"Pwd=pwd;" & _
Set obj2 = .Execute