Microsoft KB Archive/224332

= PRB: ADO Recordset Open Method May Behave Synchronously Even if adAsyncFetch is Specified =

Article ID: 224332

Article Last Modified on 8/23/2001

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q224332



SYMPTOMS
You may see the following symptoms, even though you've specified adAsyncFetch:


 * 1) The FetchComplete event is never raised.
 * 2) When stepping through the code, the line after the call to the Open method is not reached until the Open method call has completed.

NOTE: The FetchComplete and FetchProgress events fire without error in Microsoft Data Access Components version 2.7.



CAUSE
When a recordset is opened with adAsyncFetch, only those records that are not returned during the initial fetch of records are asynchronously retrieved. If all of the records are returned in the initial fetch, no asynchronous fetching occurs, and the FetchComplete event is never raised.



RESOLUTION
If you are using a dual processor you then also need to set the "Background Fetch Size" to a low value such as 1.



STATUS
This problem was corrected in ADO 2.6.



MORE INFORMATION
When you specify adAsyncFetch, 50 rows are fetched by default in the first batch of records. However, you can control the number of records in the initial fetch by setting the "Initial Fetch Size" property in the ADO Recordset Properties collection.

You must set the "Initial Fetch Size" property after the ADO recordset object is created, but before the recordset is opened. Also, because asynchronous fetching requires client-size cursors, be sure to set "Initial Fetch Size" after the code to set the recordset's CursorLocation to adUseClient.

Steps to Reproduce Behavior
In the following example, the default Initial Fetch Size prevents the FetchComplete event from being raised. To work around the problem, set the Initial Fetch Size to 1, so that most records are asynchronously fetched and the FetchComplete event is raised

This example uses the Publishers table in the Pubs sample database that comes with Microsoft SQL Server versions 6.5 and 7.0.  In Microsoft Visual Basic 6.0, create a new Standard EXE Project. Form1 is created by default. Add a reference to either the Microsoft ActiveX Data Objects 2.0 Library or the 2.1 Library. Copy the example code below into Form1's code window. In the connect string (strCnn), replace "MyServer" with the name of your server. Run the project and note that FetchComplete is not raised (you do not see the message box). Close the app.</li>  Uncomment the following line of code: 'rst.Properties("Initial Fetch Size") = 1 </li> Run the project and note that FetchComplete is now raised.</li></ol>

Private WithEvents rst As ADODB.Recordset

Private Sub Form_Load

Dim strCnn As String

strCnn = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=Pubs;User ID= ;Password= "

Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient

'Uncomment the next line to workaround the problem 'rst.Properties("Initial Fetch Size") = 1 ' For dual processor machines uncomment the next line also ' rst.properties("Background Fetch Size") = 1

rst.Open "select * from publishers", strCnn, adOpenKeyset, adLockOptimistic, adAsyncFetch rst.MoveLast

End Sub

Private Sub rst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

MsgBox "FetchComplete executed"

End Sub

<div class="references_section">