HOWTO: Return Multiple Recordsets with Column Names and Values

ID: Q182290

The information in this article applies to:

Microsoft Data Access Components, version 1.5

SUMMARY

This article contains sample code using Visual Basic version 5.0 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 Firehouse 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 will return 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

NOTE: You need to acquire and install the Microsoft Data Access Components (MDAC) 1.5 stack for the sample in this article. Please refer to the article listed in the REFERENCES section for more information on MDAC 1.5.

After the stored procedure is created, follow these steps:

1. Open a new project in Visual Basic 5.0. 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 1.5. (This was installed with the MDAC 1.5 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 New ADODB.Connection
      Dim Cmd1 As New ADODB.Command
      Dim Rs1 As New 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.
      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 the Recordset state is open.
      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

         '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 Rs1.State = adStateOpen Then
                  Debug.Print
                  Debug.Print "***** NEXT RECORDSET *****"
              Else
                  Debug.Print
                  Debug.Print "***** THE END *****"
                  Exit Do
              End If

      Loop

      Set Rs1 = Nothing
      Unload Me   'Quit the application.

      End Sub

Run the application by pressing F5 and click the command button. The results display in the Debug window.

REFERENCES

For additional information on MDAC 1.5, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q175018
   TITLE     : HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Keywords          : mdacGenInfo 
Version           : WINDOWS:1.5
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: October 13, 1998