How To Extract Multiple RDO Resultsets from Stored Procedures

MORE INFORMATION

There are two methods to avoid Error 40002.

Method 1

Use The ODBC Cursor library rather than Server Side Cursors. To do this, use the following code:

   rdoEngine. rdoDefaultCursorDriver = rdUseODBC
				

   rdoEnvironments(0).CursorDriver = rdUseOdbc
				

This option gives better performance for small result sets, but may degrade quickly for larger result sets depending on the Server and workstation configuration.

NOTE: If you are using SQL text fields, you must use Server-side cursors to bypass Error 40002"

Method 2

Use Server Side Cursors, a Forward Only Cursor, and a rowset size of 1. Make the server create a cursor-less resultset on the server side by using a forward only cursor and a RowSetSize of 1.

The following code sample illustrates how to create a stored procedure that returns multiple result sets using method 2.

1. Create the stored procedure on SQL Server:
   
   
   1. Start the ISQL utility that shipped with SQL server.
   2. Connect to your SQL server.
   3. Select the pubs database from the combo box labeled DB.
   4. Enter the following lines into the Query Tab:

            CREATE PROCEDURE TestMultiResults AS
            select * from authors
            select * from discounts
            GO
       
      								

   5. Choose the Query|Execute menu. The Results tab should display:
      
      This command did not return data, and it did not return any rows
      
      A stored procedure called TestMultiResults has now been created in the pubs database on SQL Server.
2. Create the Visual Basic client to call the stored procedure:
   
   
   1. Start Visual Basic. Form1 is created by default.
   2. Add a Command Button (Command1) and a List Box (List1) to Form1.
   3. Add the following code to Form1:

            Private Sub Form_Load()
               Command1.Caption = "Run Stored Procedure"
            End Sub
      
             Private Sub Command1_Click()
              Dim cn As rdoConnection
              Dim ps As rdoPreparedStatement
              Dim rs As rdoResultset
              Dim strConnect As String
              'set cursor driver to use server-side cursors
              rdoDefaultCursorDriver = rdUseServer
      
              'open a connection to the pubs database using DSNless connections
              'Remember to change the following connection string parameters to reflect the correct values
      
              strConnect = "Driver={SQL Server}; Server=myServer; " & _
                           "Database=pubs; Uid=<username>; Pwd=<strong password>"
              Set cn = rdoEnvironments(0).OpenConnection(dsName:="", _
                                                    Prompt:=rdDriverNoPrompt, _
                                                    ReadOnly:=False, _
                                                    Connect:=strConnect)
      
              'create a prep stmt for the stored proc call
              Set ps = cn.CreatePreparedStatement("MyPs", _
                                                  "{call TestMultiResults}")
              'set the RowSet size to 1
               ps.RowsetSize = 1
      
               'open the resultset with forward-only cursor
               Set rs = ps.OpenResultset(rdOpenForwardOnly)
      
               'add the first resultset to a list box
               While Not rs.EOF
                   list1.AddItem rs("au_fname") & " " & rs("au_lname")
                   rs.MoveNext
               Wend
      
               'move to the second resultset
               rs.MoreResults
               list1.AddItem "Second Resultset Below"
               'add the second resultset to the same list box
               While Not rs.EOF
                   list1.AddItem rs("discounttype") & " = " & rs("discount")
                   rs.MoveNext
               Wend
      
               'Close the resultset and the connection and set both to nothing
               rs.Close
               Set rs = Nothing
               cn.Close
               Set cn = Nothing
            End Sub
       
      								

   4. Run the project and click the "Run Stored Procedure" button. You should see a list of Authors and then Discounts in the list box.