Important FYI - Multiple Recordsets w ADO/SQL Server 
Author Message
 Important FYI - Multiple Recordsets w ADO/SQL Server

Read the help from the MSDN below.  It turns out that if you have
multiple statements in a stored procedure (either row returning or not -
 that is really weird) that multiple recordsets will be returned for
each.  Check out the example below as well.  This means that you will
need to write stored procs in a logical order as well.

It only took me 2 hours to figure out that an UPDATE and a SELECT
statement in succession in a stored proc was producing multiple
recordsets (the one returned by the UPDATE is closed).  I just assumed
(the old addage about that word really come into play here) that there
would be only one recordset returned from such a stored proc because
there was only one select statement in it.  Oh well - you live and


NextRecordset Method

Clears the current Recordset object and returns the next Recordset by
advancing through a series of commands.


Set recordset2 = recordset1.NextRecordset( RecordsAffected )

Return Value

Returns a Recordset object. In the syntax model, recordset1 and
recordset2 can be the same Recordset object, or you can use separate


RecordsAffected   Optional. A Long variable to which the provider
returns the number of records that the current operation affected.

Note   This parameter only returns the number of records affected by an
operation; it does not return a count of records from a select
statement used to generate the Recordset.


Use the NextRecordset method to return the results of the next command
in a compound command statement or of a stored procedure that returns
multiple results. If you open a Recordset object based on a compound
command statement (for example, "SELECT * FROM table1;SELECT * FROM
table2") using the Execute method on a Command or the Open method on a
Recordset, ADO executes only the first command and returns the results
to recordset. To access the results of subsequent commands in the
statement, call the NextRecordset method.

As long as there are additional results and the Recordset containing
the compound statements is not marshaled across process boundaries, the
NextRecordset method will continue to return Recordset objects. If a
row-returning command returns no records, the returned Recordset object
will be empty. Test for this case by verifying that the BOF and EOF
properties are both True. If a nonrow-returning command executes
successfully, the returned Recordset object will be closed, which you
can verify by testing the State property on the Recordset. When there
are no more results, recordset will be set to Nothing.

Remote Data Service Usage   The NextRecordset method is not available
on a client-side Recordset object.

If an edit is in progress while in immediate update mode, calling the
NextRecordset method generates an error; call the Update or
CancelUpdate method first.

To pass parameters for more than one command in the compound statement
by filling the Parameters collection, or by passing an array with the
original Open or Execute call, the parameters must be in the same order
in the collection or array as their respective commands in the command
series. You must finish reading all the results before reading output
parameter values.

When you call the NextRecordset method, ADO executes only the next
command in the statement. If you explicitly close the Recordset object
before stepping through the entire command statement, ADO never
executes the remaining commands.


NextRecordset Method Example (VB)

This example uses the NextRecordset method to view the data in a
recordset that uses a compound command statement made up of three
separate SELECT statements.

Public Sub NextRecordsetX()

   Dim rstCompound As ADODB.Recordset
   Dim strCnn As String
   Dim intCount As Integer

   ' Open compound recordset.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "

   Set rstCompound = New ADODB.Recordset
   rstCompound.Open "SELECT * FROM authors; " & _
      "SELECT * FROM stores; " & _
      "SELECT * FROM jobs", strCnn, , , adCmdText

   ' Display results from each SELECT statement.
   intCount = 1
   Do Until rstCompound.State = adStateClosed
      Debug.Print "Contents of recordset #" & intCount
      Do While Not rstCompound.EOF
         Debug.Print , rstCompound.Fields(0), _

      Set rstCompound = rstCompound.NextRecordset
      intCount = intCount + 1

End Sub


Angelo Pacione

Sent via http://www.*-*-*.com/
Before you buy.

Mon, 06 May 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Important FYI - Multiple Recordsets w ADO/SQL Server

2. Important FYI - Multiple Recordsets w ADO/SQL Server

3. IMPORTANT ado connection to remote sql server v.7

4. IMPORTANT ado connection to remote sql server v.7

5. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

6. multiple connections with ADO to SQL server

7. multiple parameters with ADO and SQL Server 2000 Stored proc

8. ADO creating/destroying multiple SQL server connections

9. Indexes in SQL Server with ADO Recordset

10. FYI: Attention by connecting MS-SQL-Server thru RAS

11. Inserting data from an ADO recordset into a SQL Server table

12. HELP - ADO recordset clone failing on SQL Server 7


Powered by phpBB® Forum Software