Visual Basic Concepts

Running a Stored Procedure That Returns Multiple Resultsets

RDO

This example illustrates how to perform a query that returns more than one resultset. It’s common for a stored procedure to return more than a single set of rows or a resultset that contains results from an action query. As a result, your code must deal with each of the resultsets individually, or else you won’t be able to use the product of your query. In RDO, you use the MoreResults method to step through the resultsets one at a time. Each call to MoreResults closes the current resultset and moves to the next (if there is one).

Private Sub MultipleRSButton_Click()
   sql = "Select * from Authors Where year_born is not null; " _
    & "Select * from Authors where year_born is  null"
   Set rs = cn.OpenResultset(sql)
   rdoGrid1.ShowData rs

   i = MsgBox("Ready for next results?", vbYesNoCancel)
   If i = vbYes Then
      If rs.MoreResults Then
         rdoGrid1.ShowData rs
      End If
   End If
End Sub

ADO

The following code illustrates how to handle SPs that return multiple resultsets in ADO. ADO’s approach is different from the RDO approach in that ADO uses the NextRecordset method in which you assign the next recordset in the batch to an ADO Recordset object. The next recordset read doesn't overwrite the first one, as it does in RDO. ADO also allows for multiple recordsets, if the data provider supports it.

Private Sub MultipleRSButton_Click()
   Dim rs As New ADODB.Recordset
   sql = "Select * from Authors Where year_born is not null; " _
    & "Select * from Authors where year_born is  null"
   rs.Open sql, cn
   Do
      i = MsgBox("Ready for results?", vbYesNoCancel)
      If i = vbYes Then
         ADOGrid1.ShowData rs
         Set rs = rs.NextRecordset
      End If
   Loop Until rs.State = adStateClosed
End Sub