Return value and Output values from Stored Procedure 
Author Message
 Return value and Output values from Stored Procedure

Hi,

I'm having trouble getting the values from my stored procedure returned to
my VB6 program.  Here is what my VB6 codes looks like:

cmd.Parameters.Append cmd.CreateParameter("returncode", adInteger,
adParamReturnValue)

    cmd.Parameters.Append cmd.CreateParameter("Vertical", _
          adDouble, adParamInput, , 5030.99)
    cmd.Parameters.Append cmd.CreateParameter("OutId", _
          adInteger, adParamOutput)

   Set rs = cmd.Execute

Then I try to access cmd.Parameters.item("OutId") and it is Null. Here is my
Stored Proc:

CREATE procedure SP_Insert_Test

AS INSERT INTO [tblTest]
  (Vertical)

VALUES



    begin
     return 99
    end

return 0

Any help would be appreciated.

Thanks,

Ellie



Sat, 17 Sep 2005 05:00:30 GMT  
 Return value and Output values from Stored Procedure


Quote:
> Hi,

> I'm having trouble getting the values from my stored procedure returned to
> my VB6 program.  Here is what my VB6 codes looks like:

> cmd.Parameters.Append cmd.CreateParameter("returncode", adInteger,
> adParamReturnValue)

>     cmd.Parameters.Append cmd.CreateParameter("Vertical", _
>           adDouble, adParamInput, , 5030.99)
>     cmd.Parameters.Append cmd.CreateParameter("OutId", _
>           adInteger, adParamOutput)

>    Set rs = cmd.Execute

> Then I try to access cmd.Parameters.item("OutId") and it is Null. Here is
my
> Stored Proc:

> CREATE procedure SP_Insert_Test

> AS INSERT INTO [tblTest]
>   (Vertical)

> VALUES



>     begin
>      return 99
>     end

> return 0

> Any help would be appreciated.

> Thanks,

> Ellie



Sat, 17 Sep 2005 09:30:53 GMT  
 Return value and Output values from Stored Procedure
Hi,

First of all you need to add SET NOCOUNT ON as a very first statement inside
your SP to get recordset from that SP. But it will fix problem wehn you need
to get recordset. To get output and return values of parameters first you
need to proceed that oponed recordset, which means you need to close it.
After you close opened recordset you will get values in parameters.

--
Val Mazur
Microsoft MVP


Quote:
> Hi,

> I'm having trouble getting the values from my stored procedure returned to
> my VB6 program.  Here is what my VB6 codes looks like:

> cmd.Parameters.Append cmd.CreateParameter("returncode", adInteger,
> adParamReturnValue)

>     cmd.Parameters.Append cmd.CreateParameter("Vertical", _
>           adDouble, adParamInput, , 5030.99)
>     cmd.Parameters.Append cmd.CreateParameter("OutId", _
>           adInteger, adParamOutput)

>    Set rs = cmd.Execute

> Then I try to access cmd.Parameters.item("OutId") and it is Null. Here is
my
> Stored Proc:

> CREATE procedure SP_Insert_Test

> AS INSERT INTO [tblTest]
>   (Vertical)

> VALUES



>     begin
>      return 99
>     end

> return 0

> Any help would be appreciated.

> Thanks,

> Ellie



Sat, 17 Sep 2005 09:33:33 GMT  
 Return value and Output values from Stored Procedure
You don't actually need to use a recordset at all here. Try:

    'set up command object (NB also need active connection)
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "SP_Insert_Test"

    'call without returning a recordset
    cmd.Execute

    'extract output parameter value
    lngMyResult = cmd.Parameters("OutId").Value

Pete


Quote:
> Hi,

> I'm having trouble getting the values from my stored procedure returned to
> my VB6 program.  Here is what my VB6 codes looks like:

> cmd.Parameters.Append cmd.CreateParameter("returncode", adInteger,
> adParamReturnValue)

>     cmd.Parameters.Append cmd.CreateParameter("Vertical", _
>           adDouble, adParamInput, , 5030.99)
>     cmd.Parameters.Append cmd.CreateParameter("OutId", _
>           adInteger, adParamOutput)

>    Set rs = cmd.Execute

> Then I try to access cmd.Parameters.item("OutId") and it is Null. Here is
my
> Stored Proc:

> CREATE procedure SP_Insert_Test

> AS INSERT INTO [tblTest]
>   (Vertical)

> VALUES



>     begin
>      return 99
>     end

> return 0

> Any help would be appreciated.

> Thanks,

> Ellie



Sat, 17 Sep 2005 21:04:38 GMT  
 Return value and Output values from Stored Procedure
Thanks. I had a problem in that I was using a recordset for the command so
the program was not providing the value of that parameter until I closed the
recordset. For that specific operation, I just executed the command without
returning a recordset.


Quote:
> You don't actually need to use a recordset at all here. Try:

>     'set up command object (NB also need active connection)
>     cmd.CommandType = adCmdStoredProc
>     cmd.CommandText = "SP_Insert_Test"

>     'call without returning a recordset
>     cmd.Execute

>     'extract output parameter value
>     lngMyResult = cmd.Parameters("OutId").Value

> Pete



> > Hi,

> > I'm having trouble getting the values from my stored procedure returned
to
> > my VB6 program.  Here is what my VB6 codes looks like:

> > cmd.Parameters.Append cmd.CreateParameter("returncode", adInteger,
> > adParamReturnValue)

> >     cmd.Parameters.Append cmd.CreateParameter("Vertical", _
> >           adDouble, adParamInput, , 5030.99)
> >     cmd.Parameters.Append cmd.CreateParameter("OutId", _
> >           adInteger, adParamOutput)

> >    Set rs = cmd.Execute

> > Then I try to access cmd.Parameters.item("OutId") and it is Null. Here
is
> my
> > Stored Proc:

> > CREATE procedure SP_Insert_Test

> > AS INSERT INTO [tblTest]
> >   (Vertical)

> > VALUES



> >     begin
> >      return 99
> >     end

> > return 0

> > Any help would be appreciated.

> > Thanks,

> > Ellie



Sun, 18 Sep 2005 04:25:33 GMT  
 
 [ 5 post ] 

 Relevant Pages 

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

2. Stored Proc Return values / Output Params w ADO and SQL Server 7

3. Stored Proc Return values / Output Params w ADO and SQL Server 7

4. Stored Proc Return values / Output Params w ADO and SQL Server 7

5. Stored Proc Return values / Output Params w ADO and SQL Server 7

6. Retreive Output values from Stored procedure

7. Return Values and Output parameters of stored proc

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