Microsoft KB Archive/193352

= PRB: NextRecordset Fails When Called On a Disconnected Recordset =

Article ID: 193352

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q193352



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

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

-or-

Object or Provider is not capable of performing requested operation.



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.



Steps to Reproduce Behavior
 Start a new Standard .exe project in Visual Basic. Form1 is created by default. From the Project menu, choose References and then select "Microsoft ActiveX Data Objects 2.0 Library".  Add the following code to your form.

Note You must change UID and PWD to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. 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= ;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  Modify the connection string as appropriate to connect to your SQL Server. Run the project. When prompted, click Yes to disconnect the recordset. You then receive the error described in the SYMPTOMS section.</li> 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.</li></ol>

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by David Sceppa, Microsoft Corporation.

Keywords: kbprb KB193352

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.