BUG: Error Message "Async Operation Is Pending" Using RDS DataControl

If you try to access the methods or properties of an RDS DataControl object that has been executed asynchronously, you see one of the following errors: The error occurs when you try to use the DataControl's Recordset collection, SortColumn property, Refresh method, or Reset method.

The errors occur despite checking the status of the Data Control's ReadyState flag to see if execution is complete.

The errors occur because the DataControl is still executing the recordset.

Although it should be possible to use the DataControl's ReadyState property to determine when the execution is finished, ReadyState incorrectly indicates that execution is complete before execution is actually complete. That is, the flag that signals the end of the asynchronous execution does not get set until after the ReadyState flag has been set and the ReadyState notification has fired.

The same behavior occurs when using the DataControl's OnReadyStateChange event.
The ReadyState is incorrectly reported as complete if checked within the OnReadyStateChange event.

One workaround is to change the ExecuteOptions property of the DataControl to 1, adcExecSync, so that execution is synchronous.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

The following code uses the Pubs database that comes with SQL Server. The sample uses Visual Basic, but can easily be adapted to VBScript.

1. In Visual Basic, create a new Standard EXE. Form1 is created by default.
2. Add a CommandButton to Form1. Command1 is created by default
3. Paste the following code into Form1's Code window:
   
   Note You must change User ID=<User ID> and Password=<Strong Password> to the correct values. Make sure that User ID has the appropriate permissions to perform this operation on the database.

      Private Sub Command1_Click()
   
         Dim dc
         Set dc = CreateObject("RDS.DataControl")
     
         'Open asynchronously
         dc.ExecuteOptions = 2  'adcExecAsync
     
         'Uncomment this line to workaround
         'dc.ExecuteOptions = 1  'adcExecSync
     
         dc.SQL = "select * from authors"
         dc.Connect = "Provider=SQLOLEDB;Data Source=servername;" & _
                      "Initial Catalog=Pubs;User ID=<User ID>;Password=<Strong Password>;"
     
         'Open the recordset
         dc.Refresh
     
         'Check to see when execution is finished
         Do While dc.ReadyState <> 4  'adcReadyStateComplete
             DoEvents
         Loop
   
         'Sort the recordset
          dc.SortColumn = "au_id"
          dc.Reset
   
      End Sub

4. In the code, modify the Data Control's Connect property so that the Data Source uses your server and a valid User ID and Password.
5. Run the program. Click Command1. Note run-time error 4113.
6. Uncomment the code that sets the DataControl's ExecuteOptions property to 1, which is adcExecSync.
7. Again run the program. Click Command1. The code completes successfully.

For additional information on the Remote Data Service DataControl, please refer to the topic "RDS.Data Control" in MSDN Online Help.