Microsoft KB Archive/190606

= PRB: QueryTimeout Event Is Not Available =

Article ID: 190606

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q190606



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 by design.



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.

 Create a new Visual Basic project and add a reference to the following:

Microsoft ActiveX Data Objects Library

 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

  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  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?

  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 </li> Run the project and click each of the two buttons.</li></ol>

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.

<div class="references_section">