You are currently offline, waiting for your internet to reconnect

PRB: QueryTimeout Event Is Not Available

This article was previously published under Q190606
This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
The ActiveX Data Objects (ADO) Connection object does not expose aQueryTimeout event as does the rdoConnection object, which allowsprogrammatic 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 codethat determines whether to cancel the query.
Status
This behavior is by design.
More information
The Remote Data Objects (RDO) 2.0 rdoConnection objects expose aQueryTimeout event. For long-running queries, this event fires afterQueryTimeout seconds and allows you to cancel the query or to continue foranother QueryTimeout seconds, when the event fires again.

ADO does not expose a QueryTimeout event. This article lists sometechniques 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 forthe run-time error -2147217871 (0x80040E31) in-line in your code(Synchronous queries only), or you can add code to the Connection'sExecuteComplete 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 atimer event to simulate the QueryTimeout event. You can then prompt theuser 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 yourRecordset 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 impliedwarranties 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:
    Textbox
    Name: txtMessage
    Make this Textbox large enough to display a reasonable message

    Command1
    Name: cmdRetry
    Caption: Retry

    Command2
    Name: cmdCancel
    Caption: Cancel
  3. Add the following code to the code window of Form2:
          Option Explicit      Public 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:
    Command1
    Name: cmdDetect
    Caption: Detect Timeout

    Command2
    Name: cmdChoose
    Caption: Time-out?
  5. Add the following code to Form1's Code Window. Modify the Connection Strings to connect to your SQL Server:
          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"
Properties

Article ID: 190606 - Last Review: 10/26/2013 02:07:00 - Revision: 2.0

Microsoft ActiveX Data Objects 2.7

  • kbnosurvey kbarchive kbdatabase kbDSupport kbprb KB190606
Feedback