accessing stored procedure return value using ADO (2.0) command execute method

accessing stored procedure return value using ADO (2.0) command execute method

Post by ccheun » Sun, 12 Mar 2000 04:00:00



Hello,
I am in search of an answer to the following problem:

I have a stored procedure that executes a series of other procedures.
for example:
    create procedure PR_ABC
    as





    end




    end

stored procedure PR_RST inserts data into table A and PR_XYZ  inserts data
into table B etc.

I then call the main stored procedure PR_ABC using ADO command execute
method in my VB 6.5 application.  I am interested in the return code from my
stored procedure (PR_ABC) to test whether it ran successfully.
for example:
...
   Set Param1 = cmd.CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue)
   cmd.Parameters.Append Param1
   cmd.execute ,  , adExecNoRecords
   returnvalue = cmd.parameters("RETURN_VALUE")

The problem is I can't seem to get the proper return code using ADO command
when the stored procedure involves INSERTs, UPDATEs, OR DELETEs.  The return
value is always 0 even when there is an error.   It only seems to work  for
SELECT statements (it only work if there is a resultset).

Is there  a way I can get to the return value of stored procedure executing
inserts, updates, or deletes using ADO command ???

Suggestion are much appreciated.

C

 
 
 

accessing stored procedure return value using ADO (2.0) command execute method

Post by sloa » Tue, 14 Mar 2000 04:00:00


check out my post
Re: How to return value of stored-procedure to VB / The Answer ... with Code
for pubs DB


>Hello,
>I am in search of an answer to the following problem:

>I have a stored procedure that executes a series of other procedures.
>for example:
>    create procedure PR_ABC
>    as





>    end




>    end

>stored procedure PR_RST inserts data into table A and PR_XYZ  inserts data
>into table B etc.

>I then call the main stored procedure PR_ABC using ADO command execute
>method in my VB 6.5 application.  I am interested in the return code from
my
>stored procedure (PR_ABC) to test whether it ran successfully.
>for example:
>...
>   Set Param1 = cmd.CreateParameter("RETURN_VALUE", adInteger,
>adParamReturnValue)
>   cmd.Parameters.Append Param1
>   cmd.execute ,  , adExecNoRecords
>   returnvalue = cmd.parameters("RETURN_VALUE")

>The problem is I can't seem to get the proper return code using ADO command
>when the stored procedure involves INSERTs, UPDATEs, OR DELETEs.  The
return
>value is always 0 even when there is an error.   It only seems to work  for
>SELECT statements (it only work if there is a resultset).

>Is there  a way I can get to the return value of stored procedure executing
>inserts, updates, or deletes using ADO command ???

>Suggestion are much appreciated.

>C


 
 
 

1. SQL Server stored procedure return value - how to access from ADO Command object

Here is an example in VB:

With adoCmd
    Set .ActiveConnection = adoConn
    .CommandText = "p_insertauthor"
    .CommandTimeout = 30
    .CommandType = adCmdStoredProc
    '**return code
    Set adoParam = .CreateParameter _
        ("retcode", adInteger, adParamReturnValue)
    .Parameters.Append adoParam
    Set adoParam = .CreateParameter _
        ("au_id", adVarChar, adParamInput, 11, txtAuID)
    .Parameters.Append adoParam
    Set adoParam = .CreateParameter _
        ("au_lname", adVarChar, adParamInput, 40, txtAuLname)
    .Parameters.Append adoParam
    Set adoParam = .CreateParameter _
        ("au_fname", adVarChar, adParamInput, 20, txtAuFname)
    .Parameters.Append adoParam
    Set adoParam = .CreateParameter _
        ("phone", adChar, adParamInput, 12, txtPhone)
    .Parameters.Append adoParam
    Set adoParam = .CreateParameter _
        ("contract", adBoolean, adParamInput, , chkContract)
    .Parameters.Append adoParam
    .Prepared = False
    .Execute intRA, , adExecuteNoRecords
    '**look at value of return code
    Debug.Print .Parameters.Item("retcode")
End With

2. ODBC

3. ADO Command Object - accessing MS SQL Server Stored Procedure return value

4. us.tx.dallas.informix dba.contract.6-9 mo.'s.$55-60/hr.

5. SQL server ADO.command.execute stored procedure does not return a records

6. Deployment of PostgreSQL Applications

7. ADO or DataEnvironment COMMAND Using Store procedure can't return recordset

8. Job Opportunity - Informix Programmer with 4GL or SQL Exp. - Newport Beach, CA

9. Stored Procedure returned value using ADO

10. returning values from stored procedures using ADO / VB code

11. Executing a stored procedure from another procedure that returns values

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