Can't pass return value or output parameter to ADO if storeproc updates 
Author Message
 Can't pass return value or output parameter to ADO if storeproc updates

In the following procedure I get output parameter without update sql part,
and don't get it with update.
I need to run update records and pass an output parameter to ADO in VB.
How can we pass output parameter when we update rows in stored procedure?

For your reference return value or output parameter are not passed to ADO
command parameters if it's not associated with select statement.
http://www.*-*-*.com/

Thank you very much
---------------------stored procedure

as

update idname
    set price = 1000

select id from idname






Sat, 03 Apr 2004 05:27:32 GMT  
 Can't pass return value or output parameter to ADO if storeproc updates
1 - Use SET NOCOUNT ON/OFF  to avoid that sql server returns the message
(xxx rows affected) as recordset.

2 - Use SET to assign values to variables, if not, you will return another
recordset, so you will have to use set objRs = objRs.NextRecordset.


3 - If you want to return:

    select id
    from idname

    then, in your client programm, you have to process the recordset and
then close it before accessing output parameters and return value.

Greetings,

Alejandro Mesa


Quote:
> In the following procedure I get output parameter without update sql part,
> and don't get it with update.
> I need to run update records and pass an output parameter to ADO in VB.
> How can we pass output parameter when we update rows in stored procedure?

> For your reference return value or output parameter are not passed to ADO
> command parameters if it's not associated with select statement.
> http://support.microsoft.com/support/kb/articles/Q194/7/92.ASP

> Thank you very much
> ---------------------stored procedure
> create proc bi


> as

> update idname
>     set price = 1000

> select id from idname







Sat, 03 Apr 2004 20:05:09 GMT  
 Can't pass return value or output parameter to ADO if storeproc updates
Thank you so much for the essential info.
It's weird that we have to close connection to see parameter.
It's too bad that we can't process recordset and parameter at the same time
or depending on the value of parameter.


Quote:
> 1 - Use SET NOCOUNT ON/OFF  to avoid that sql server returns the message
> (xxx rows affected) as recordset.

> 2 - Use SET to assign values to variables, if not, you will return another
> recordset, so you will have to use set objRs = objRs.NextRecordset.


> 3 - If you want to return:

>     select id
>     from idname

>     then, in your client programm, you have to process the recordset and
> then close it before accessing output parameters and return value.

> Greetings,

> Alejandro Mesa



> > In the following procedure I get output parameter without update sql
part,
> > and don't get it with update.
> > I need to run update records and pass an output parameter to ADO in VB.
> > How can we pass output parameter when we update rows in stored
procedure?

> > For your reference return value or output parameter are not passed to
ADO
> > command parameters if it's not associated with select statement.
> > http://support.microsoft.com/support/kb/articles/Q194/7/92.ASP

> > Thank you very much
> > ---------------------stored procedure
> > create proc bi


> > as

> > update idname
> >     set price = 1000

> > select id from idname







Sun, 04 Apr 2004 00:43:14 GMT  
 
 [ 3 post ] 

 Relevant Pages 

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

2. output parameters not returning values

3. Return Values and Output parameters of stored proc

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. Stored Proc Return values / Output Params w ADO and SQL Server 7

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

8. Passing parameters to VB application and get return value from it

9. Passing parameters to forms and return values

10. can't get output parameter from SP using ADO

11. FUNCTION doesn't return parameter value

12. Problem with Return Value Parameter with ADO

 

 
Powered by phpBB® Forum Software