PRB: NextRecordset Fails When Called On a Disconnected Recordset

• ActiveX Data Objects (ADO), versions 1.5, 2.0
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
• Microsoft Visual Studio 6.0

SYMPTOMS

When trying to retrieve the second recordset in a batch query, the following error appears:

The operation requested by the application is not supported by the provider.

CAUSE

This problem can be caused by disconnecting the initial recordset prior to calling NextRecordset.

RESOLUTION

Do not disconnect the recordset until you have retrieved all of the recordsets in the batch query.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Standard .exe project in Visual Basic. Form1 is created by default.



2. From the Project menu, choose References and then select "Microsoft ActiveX Data Objects 2.0 Library".



3. Add the following code to your form:

   
         Dim cnPubs As ADODB.Connection
         Dim rsBatch As ADODB.Recordset
   
         Private Sub Form_Load()
          Dim strConn As String
          Dim strSQL As String
   
          strConn = "Provider=MSDASQL;Driver={SQL Server};" & _
                    "Server=YourServer;Database=pubs;UID=sa;PWD=;"
          Set cnPubs = New ADODB.Connection
          cnPubs.CursorLocation = adUseClient
          cnPubs.Open strConn
   
          strSQL = "SELECT * FROM Authors;" & _
                   "SELECT * FROM Employee;" & _
                   "SELECT * FROM Jobs"
          Set rsBatch = New ADODB.Recordset
          rsBatch.Open strSQL, cnPubs, adOpenStatic, _
                       adLockOptimistic, adCmdText
   
          If MsgBox("Disconnect the recordset?", vbYesNo) = vbYes Then
              Set rsBatch.ActiveConnection = Nothing
          End If
   
          Do
              Debug.Print rsBatch(0).Name & " = "  rsBatch(0)
              Set rsBatch = rsBatch.NextRecordset
          Loop Until rsBatch Is Nothing
   
          cnPubs.Close
          Set cnPubs = Nothing
         End Sub 




4. Modify the connection string as appropriate to connect to your SQL Server.



5. Run the project. When prompted, click Yes to disconnect the recordset. You then receive the error described in the SYMPTOMS section.



6. Run the project again. When prompted, click No to keep from disconnecting the recordset. You then successfully retrieve the remaining recordsets in the batch query.

Additional query words:

Keywords : kbADO kbADO200 kbDatabase
Version : WINDOWS:1.5,2.0,5.0,6.0
Platform : WINDOWS
Issue type : kbprb