HOWTO: Return Multiple Recordsets with Column Names and Values

• Microsoft Data Access Components versions 1.5, 2.0, 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

This article contains sample code using Visual Basic that demonstrates how to return multiple ADO Recordsets from a Microsoft SQL Server stored procedure. The code also demonstrates how to extract the column names and values from each ADO Recordset as each row is returned.

Returning multiple ADO Recordsets is only supported using Server-Side Firehose cursors, which are Forward-Only, Read-Only Recordsets that reside on the Server.

MORE INFORMATION

This application requires a Microsoft SQL Server stored procedure that returns multiple Recordsets. An example of the script required to accomplish this task is given below. It is assumed that the reader is familiar with creating stored procedures in Microsoft SQL Server. This stored procedure uses the pubs database supplied with Microsoft SQL Server. You need to run the following script against the pubs database before running the sample code:

CREATE PROCEDURE Multi_Results AS

SELECT * FROM Authors
SELECT * FROM Stores
SELECT * FROM Sales
GO 

1. Open a Standard EXE project in Visual Basic. Form1 is created by default.



2. Place a command button on the new Form.



3. From the Project menu, select References and then select Microsoft ActiveX Data Objects. (This was installed with the MDAC stack.)



4. Place the following code in the General Declarations section of Form1. You may need to alter the database connection information. Here is the code:

   
   Private Sub Command1_Click()
   Dim Conn1 As ADODB.Connection
   Set Conn1 = ADODB.Connection
   Set conn1= ADODB.Connection
   Dim Cmd1 As ADODB.Command
   Set Cmd1 = ADODB.Command
   Set cmd1= ADODB.Command
   Dim Rs1 As ADODB.Recordset
   
   Dim RSField As ADODB.Field
   Dim ColCnt, Cnt1, i As Long
   
   Debug.Print "----Starting-----"
   
   'Create all the objects required by this application.
   '
   'The connection string will have to be modified to reflect your data
   'source name (DSN).
   Conn1.Open "MyDSN", "sa", ""
   Conn1.DefaultDatabase = "pubs"
   Conn1.CursorLocation = adUseServer
   'Conn1.CursorLocation = adUseClient  'This will give error 3251 -
                     '"The operation requested by the application
                     'is not supported by the provider" when calling
                     'the NextRecordset method. 
                     'Actually, the above is not true for ADO 2.1 sp2, 
                     'and possibly other versions as well. This will 
                     'have to be changed to say 
                     '"Older versions of the OLE DB Provider for 
                     'ODBC Drivers will give error 3251."
   
   Cmd1.ActiveConnection = Conn1
   Cmd1.CommandType = adCmdStoredProc
   Cmd1.CommandText = "Multi_Results"
   
   'Opens a Forward-Only, Read-Only Recordset cursor.
   Set Rs1 = Cmd1.Execute
        
   'Continue looping while there are recordsets to process.      
   Do Until Rs1 Is Nothing
   
   Do While Rs1.State = adStateOpen
      ColCnt = Rs1.Fields.Count
      Debug.Print "The Recordset State is " & Rs1.State
      Debug.Print "The Column Count is: " & ColCnt
   
      Do While Not Rs1.EOF    'Cycle through the Recordset.
   
         Debug.Print "Next Record:"
         Cnt1 = 0
   
         'Cycle through the Field collection.
         For Each RSField In Rs1.Fields
            Debug.Print "   " & RSField.Name & " = " & Rs1(Cnt1)
            Cnt1 = Cnt1 + 1
            If Cnt1 = ColCnt Then
                Exit For
            End If
   
         Next RSField
   
         Rs1.MoveNext
      Loop
   
      'See the above note.
      'IMPORTANT:
      'The call to NextRecordset only works with firehose cursors
      'that exist on the server-side.
      '
      'Put a breakpoint here to see the last results in the Debug
      ' window.
   
           Set Rs1 = Rs1.NextRecordset
   
           If Not Rs1 Is Nothing Then
               Debug.Print
               Debug.Print "***** NEXT RECORDSET *****"
           Else
               Debug.Print
               Debug.Print "***** THE END *****"
               
           End If
   
   Loop
   
   Unload Me   'Quit the application.
   
   End Sub 

REFERENCES

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ron Nelson, Microsoft Corporation

Additional query words:

Keywords : kbADO kbADO200 kbADO210 kbDatabase kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1,2.1 SP1,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto