Return value of a store procedure from VB ADO

Return value of a store procedure from VB ADO

Post by Juan Ontivero » Thu, 05 Apr 2001 18:18:58



Hello,
Do you know how can I get the return value of a store procedure?
The store procedure is in SQLServer 2000 and I am trying the pull this value
using VB6(ADO)
I tried the following code, but after I rub the Execute method on the
Command object, the oParameter.value is set to "Empty" instead of 7.

vb code
*********************************
Set oParameter = oCommand.CreateParameter("Return", adInteger,
adParamReturnValue,,0)
oCommand.Parameters.Append oParameter
 Set oRSbc = oCommand.Execute

store procedure
**********************************
CREATE PROCEDURE DummyProcedure
as
 select *
 from Table1
 where Table1.Key = 9

 return 7
GO

I appreciate your help thanks!
Juan

 
 
 

Return value of a store procedure from VB ADO

Post by Tim Heckm » Fri, 06 Apr 2001 04:43:31



Once you execute the procedure, just get the value
from the parameter object.

Quote:

>vb code
>*********************************
>Set oParameter = oCommand.CreateParameter("Return", adInteger,
>adParamReturnValue,,0)
>oCommand.Parameters.Append oParameter
> Set oRSbc = oCommand.Execute

And then (assuming "retval" is an already-declared variable):

retval = oCommand("Return")

or

retval = oCommand.Parameters("Return").Value

(or retval = oCommand.Parameters(0).Value)

Tim Heckman

 
 
 

Return value of a store procedure from VB ADO

Post by Don Arsenaul » Fri, 06 Apr 2001 21:52:55


I've heard others in this newsgroup mention a similar problem before.  They
were putting the rowcount of the resultset in an output parameter of a
stored procedure.  After executing the procedure, ADO still didn't see the
output parameter.  The found that you must get to the of the resultset
before the output parameters were available.  If your stored procedure
returns multiple resultsets, you must move through all the resultsets.  I
suspect that the store procedure return value has the same issue.

Don.


Hello,
Do you know how can I get the return value of a store procedure?
The store procedure is in SQLServer 2000 and I am trying the pull this value
using VB6(ADO)
I tried the following code, but after I rub the Execute method on the
Command object, the oParameter.value is set to "Empty" instead of 7.

vb code
*********************************
Set oParameter = oCommand.CreateParameter("Return", adInteger,
adParamReturnValue,,0)
oCommand.Parameters.Append oParameter
 Set oRSbc = oCommand.Execute

store procedure
**********************************
CREATE PROCEDURE DummyProcedure
as
 select *
 from Table1
 where Table1.Key = 9

 return 7
GO

I appreciate your help thanks!
Juan