February 19th, 2003, 10:56 AM
ADO - CommandTimeOut
I would like to fall in Time Out after 1 seconde in the execution on a SQL
The request could take 0,1,2 or 3 seconds and it works!! Why???
Here is my code :
" Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
.ActiveConnection = getDSN(cle)
.CommandType = adCmdText
.CommandText = req
.CommandTimeout = 1
.CursorLocation = adUseClient
.Open cmd, , adOpenStatic, adLockReadOnly
Thanks for an answwer
February 19th, 2003, 01:26 PM
Could be a variety of reasons:
Your provider may not support this property. What database are you using?
Does the command take longer than 1 second or does the entire process take more than 1 second? The timeout property only applies to how long ADO will wait for data to start returning. If you run the query from the command prompt (not using ADO), how long does it take to start returning results?
February 20th, 2003, 03:17 AM
I 'm using Oracle 8i with MS ODBC driver for Oracle.
The query run in 0 or 1 second if the server is not too use. It can be 2 or 3 seconds and maybe more if the server is more used.
Perhaps Oracle does not support this property but I don't know.
February 20th, 2003, 07:31 AM
I had a similar problem with an Oracle 9i database. I didn't set the timeout property, so it was the default 15 seconds. One day I got a "timeout" error, but it took about 30 seconds. So I set the timeout to 120. It still timed out in about 30 seconds.
This happened with both Oracle's driver and Microsoft's Oracle driver. I got the impression the timeout value was being set at the server and ignored by ADO.
February 20th, 2003, 07:41 AM
I think the default value is 30 seconds.
If you don't want to have Timeout problems, you can set the value 0. It will runs until the server gives a response.
It seems that we can avoid TimeOut but we can't have one when we want it!!
Someone tell me to use Asynchronous Recordset but I don't know how to use it in the configuration of my project. It's just an Active X DLL. I call a function where the code you have seen is in and it returns a recordset! Damn it.
Thanks to you for your help.
February 20th, 2003, 08:33 AM
Here's an example of an async recordset I wrote. This is just a prototype that I threw together for testing before using the concept in a real project:
Dim blnCancel As Boolean
Dim cnQuery As ADODB.Connection
Dim WithEvents rstQuery As ADODB.Recordset
Dim lngRecordsFetched As Long
Private Sub Form_Load()
Set cnQuery = New ADODB.Connection
cnQuery.CursorLocation = adUseClient
Set rstQuery = New ADODB.Recordset
rstQuery.CursorLocation = adUseClient
Private Sub Form_Unload(Cancel As Integer)
Set rstQuery = Nothing
Set cnQuery = Nothing
Private Sub cmdCancel_Click()
blnCancel = True
Private Sub cmdRunAsyncQuery_Click()
cmdRunAsyncQuery.Enabled = False
cmdCancel.Enabled = True
cnQuery.ConnectionString = "Provider=SQLOLEDB.1;Password=ReadOnly;Persist Security Info=True;User ID=ReadOnly;Initial Catalog=Fire;Data Source=OMSDEV"
cnQuery.Open , , , adAsyncExecute '<- Asynchronous Connection
Do While cnQuery.State = adStateConnecting '<- While trying to connect, DoEvents
'If a previously canceled query was run, clear the grid
If rstQuery.State = adStateOpen Then rstQuery.Close
rstQuery.Open "SELECT * FROM CadSegment", cnQuery, adOpenForwardOnly, adLockReadOnly, adAsyncFetch '<- Fetch records asynchronously in the background
Do While rstQuery.State = adStateExecuting Or rstQuery.State = adStateOpen + adStateFetching '<- If the query is trying to execute or has finished but is still fetching, then DoEvents
If blnCancel Then '<- the user clicked the cancel button so cancel the query
Set rstQuery.ActiveConnection = Nothing
blnCancel = False 'Reset the flag
If lngRecordsFetched > 0 Then '<- the query is in the process of fetching
txtStatusBar.Text = "Records Fetched: " & lngRecordsFetched
Else '<- the query is still in the process of executing
txtStatusBar.Text = "Executing Query..."
Set grdData.Recordset = rstQuery
Set rstQuery.ActiveConnection = Nothing
If cnQuery.State = adStateOpen Then cnQuery.Close
cmdRunAsyncQuery.Enabled = True
cmdCancel.Enabled = False
'This is a callback routine. It monitors the fetching progress.
'I'm setting a global counter which is used in the above routine to display the progress.
Private Sub rstQuery_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
On Error Resume Next
lngRecordsFetched = MaxProgress
February 20th, 2003, 09:05 AM