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

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

Post by winglessligh » Wed, 17 Oct 2001 06:27:32



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

as

update idname
    set price = 1000

select id from idname




 
 
 

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

Post by Alejandro Mes » Wed, 17 Oct 2001 21:05:09


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






 
 
 

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

Post by winglessligh » Thu, 18 Oct 2001 01:43:14


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.


> 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





 
 
 

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

Hi
I found out output parameter or return value doesn't work well.
ADO don't get them when stored procedure contains update statement.

I included 3 codes, Stored Procedure, Sql to test SP, VB to test SP
SP works
sql to test SP gets output param and return value whether SP has update
statement or not
VB to test SP gets output param and return value only if SP don't have
update and outparam
or return value are associated with select statement.

Any feedback is appreciated

---------------stored procedure
use pubs
GO

if exists (select * from sysobjects where id =
  object_id('dbo.bi') and sysstat & 0xf = 4)
drop procedure dbo.bi
GO


as

update idname
 set name = 'MyName'



----------sql to test stored procedure






----------VB to test stored procedure

Option Explicit
Private Sub Form_Load()
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim constr As String

constr = "PROVIDER=SQLOLEDB;Driver={SQL
Server};Server=.;database=pubs;uid=sa;pwd=sa;"
Set conn = CreateObject("adodb.connection")
Set cmd = CreateObject("Adodb.command")
Set rs = CreateObject("adodb.recordset")
conn.Open constr
cmd.CommandText = "bi"
cmd.CommandType = adCmdStoredProc
With cmd.Parameters
    .Append cmd.CreateParameter("rv", adInteger, adParamReturnValue)


End With


cmd.ActiveConnection = conn
Set rs = cmd.Execute()


Debug.Print "rv = " & cmd.Parameters("rv")
End
End Sub

2. Memory problem

3. ODBC calling stored proc: Can't get output parameter or return value to work

4. US-TX-DBA WITH NT NEEDED IN SAN ANTONIO

5. How to capture the return value and output parameter value of a stored procedure in VB

6. VA-Northern VA-89513--ORACLE-PL/SQL-SQL *Loader-Triggers-Stored Procedures-Oracle Developer:

7. Stored procedure returning empty output parameters or return value

8. Paradox 10: force output to single page

9. Return result set and return value/output parameter

10. 'No output parameters returned' Error

11. Return output parameters from EXEC('tsql')

12. Pass ADO/ADO.NET parameter arrays for batch insert/update

13. Getting OUTPUT parameters and return values from Stored Procedures