Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

HOWTO: Return Multiple Recordsets with Column Names and Values


View products that this article applies to.

This article was previously published under Q182290

↑ Back to the top


Summary

The Microsoft Visual Basic sample in this article demonstrates how to return multiple ActiveX Data Objects (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.

↑ Back to the top


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
				
NOTE: You need to acquire and install the Microsoft Data Access Components (MDAC) 2.x stack for the sample in this article. Please refer to the article listed in the REFERENCES section for more information on MDAC 2.x.

After the stored procedure is created, follow these steps:
  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 = New ADODB.Connection
    Dim Cmd1 As ADODB.Command
    Set Cmd1 = New 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
    
       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
    
       '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
    						
Run the application by pressing F5 and click the command button. The results display in the Debug window.

↑ Back to the top


Keywords: KB182290, kbstoredproc, kbhowto, kbdatabase

↑ Back to the top

Article Info
Article ID : 182290
Revision : 5
Created on : 3/14/2005
Published on : 3/14/2005
Exists online : False
Views : 436