PRB: QueryTimeout Event Not Available

ID: Q190606


The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0


SYMPTOMS

The ActiveX Data Objects (ADO) Connection object does not expose a QueryTimeout event as does the rdoConnection object, which allows programmatic control over whether to continue waiting for query results.


CAUSE

This is a design limitation.


RESOLUTION

Execute the query asynchronously. You can use a Timer event to call code that determines whether to cancel the query.


STATUS

This behavior is a limitation of the ADO object model for the versions listed at the beginning of this article.


MORE INFORMATION

The Remote Data Objects (RDO) 2.0 rdoConnection objects expose a QueryTimeout event. For long-running queries, this event fires after QueryTimeout seconds and allows you to cancel the query or to continue for another QueryTimeout seconds, when the event fires again.

ADO does not expose a QueryTimeout event. This article lists some techniques to get similar functionality.

Detecting if a Timeout has Occurred

If you want to detect that a query has timed-out, you can either trap for the run-time error -2147217871 (0x80040E31) in-line in your code (Synchronous queries only), or you can add code to the Connection's ExecuteComplete event and check for adStatus to have a value of two (adStatusErrorsOccurred) and pError.Number of -2147217871 (0x80040E31).

The following code is an example of this:

   If adStatus = adStatusErrorsOccurred Then

     If pError.Number = -2147217871 Then
       Debug.Print "Execute timed-out"
     End If

   End If 

Cancelling a Long-Running Query

This involves running the query asynchronously with no time-out and using a timer event to simulate the QueryTimeout event. You can then prompt the user and set the Cancel property to TRUE to cancel the query. This does have a limitation over RDO of requiring the Timer event to know about your Recordset object.

Example

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

NOTE: Since a MsgBox prevents the Execute_Complete message from being received, you have to use a modal form instead to query the user whether to cancel or retry. This is implemented via Form2 and encapsulated by a call to the SafeMsgBox() function.

  1. Create a new Visual Basic project and add a reference to the following:
    Microsoft ActiveX Data Objects Library


  2. Add a new form (Form2) with a text box and two command buttons:
    Name: txtMessage Make this Textbox large enough to display a reasonable message Name: cmdRetry Caption: Retry Name: cmdCancel Caption: Cancel


  3. Add the following code:
    
          Option Explicit
    
          Dim fCancel As Boolean
    
          Private Sub cmdCancel_Click ()
            fCancel = True
            Me.Visible = False
          End Sub
    
          Private Sub cmdRetry_Click ()
            fCancel = False
            Me.Visible = False
          End Sub 


  4. On the default form (Form1) add two command buttons and a Timer control:
    Name: cmdDetect Caption: Detect Timeout Name: cmdChoose Caption: Time-out?


  5. Add the following code:
    
          Option Explicit
    
          Dim WithEvents cn As ADODB.Connection, rs As ADODB.Recordset
    
          Private Sub cmdChoose_Click()
          Dim SQL As String
            Set cn = New ADODB.Connection
            Set rs = New ADODB.Recordset
            cn.Open "dsn=mydsn;database=pubs"  ' *** change connect string ***
            'CommandTimeout is optional; default is 30 seconds.
            cn.CommandTimeout = 15
            '
            ' This query must exceed the Timer1.Interval in order to test.
            '
            SQL = "SELECT authors.* FROM authors, titles a, titles b"
            rs.Open SQL, cn, adOpenKeyset, adLockOptimistic, adAsyncExecute
            Timer1.Interval = 2000
          End Sub
    
          Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, _
                                         ByVal pError As ADODB.Error, _
                                         adStatus As ADODB.EventStatusEnum, _
                                         ByVal pCommand As ADODB.Command, _
                                         ByVal pRecordset As ADODB.Recordset, _
                                         ByVal pConnection As ADODB.Connection)
            If adStatus = adStatusErrorsOccurred Then
              If pError.Number = -2147217871 Then
                Debug.Print "Execute timed-out"
              End If
            End If
            Timer1.Interval = 0   ' turn off timer for async code
            if adStatus = adStatusOK Then
              If pRecordset.State = adStateOpen Then
                '
                ' Execute code now async query has completed.
                '
                Debug.Print "Query Complete."
              End If
            End If
          End Sub
    
          Private Sub cmdDetect_Click()
          Dim SQL As String
            Set cn = New ADODB.Connection
            Set rs = New ADODB.Recordset
            cn.Open "dsn=mydsn;database=pubs"  ' *** change connect string ***
            'The below is set low for demonstration purposes, it is optional.
            cn.CommandTimeout = 2
            SQL = "SELECT authors.* FROM authors, titles a, titles b"
            rs.Open SQL, cn, adOpenKeyset, adLockOptimistic, adAsyncExecute
          End Sub
    
          Private Sub Timer1_Timer()
            Select Case rs.State
    
              Case adStateConnecting, adStateExecuting, adStateFetching
                If SafeMsgBox("Query has timed-out.") = vbCancel Then
                  rs.Cancel
                  Timer1.Interval = 0
                End If
    
              Case Else
                Timer1.Interval = 0 ' catch-all
    
            End Select
          End Sub
    
          Private Function SafeMsgBox(ByVal Message As String) As Long
            Load Form2
            Form2.txtMessage = Message
            Form2.Show vbModal
            SafeMsgBox = IIf(Form2.fCancel, vbCancel, vbRetry)
            Unload Form2
          End Function 


  6. Run the project and click each of the two buttons.


RESULTS: The cmdDetect code should print a message that the query has timed-out. The cmdChoose code should present you with at least one Cancel/Retry dialog box. NOTE: You may have to substitute a longer-running query depending on your data provider, query complexity, table size, machine speed, and network.


REFERENCES

OLE DB 2.0 SDK; search on: "CommandTimeout Property"; "ADO Events"

Additional query words: kbprb kbDatabase kbADO150 kbADO200 kbVBp kbSweepNext

Keywords : kbADO150 kbADO200 kbDatabase kbVBp kbprb kbSweepNext
Version : WINDOWS:1.0,1.5,2.0
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: June 30, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.