#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    4
    Rep Power
    0

    Angry ADO - CommandTimeOut


    Hello,

    I would like to fall in Time Out after 1 seconde in the execution on a SQL

    Request.

    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")


    With cmd
    .ActiveConnection = getDSN(cle)
    .CommandType = adCmdText
    .CommandText = req
    .CommandTimeout = 1
    End With



    With rs
    .CursorLocation = adUseClient
    .Open cmd, , adOpenStatic, adLockReadOnly
    End With
    "

    Thanks for an answwer
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    The request could take 0,1,2 or 3 seconds and it works!! Why???
    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?
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    4
    Rep Power
    0
    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.
  6. #4
  7. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    4
    Rep Power
    0
    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.
  10. #6
  11. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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:

    Code:
    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
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        Set rstQuery = Nothing
        Set cnQuery = Nothing
    End Sub
    
    Private Sub cmdCancel_Click()
        blnCancel = True
    End Sub
    
    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
            DoEvents
        Loop
        
        'If a previously canceled query was run, clear the grid
        If rstQuery.State = adStateOpen Then rstQuery.Close
        grdData.Clear
        DoEvents
    
        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
            DoEvents
            If blnCancel Then '<- the user clicked the cancel button so cancel the query
                rstQuery.Cancel
                Set rstQuery.ActiveConnection = Nothing
                cnQuery.Cancel
                cnQuery.Close
                blnCancel = False 'Reset the flag
            Else
                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..."
                End If
            End If
            DoEvents
        Loop
        
        Set grdData.Recordset = rstQuery
        
        Set rstQuery.ActiveConnection = Nothing
        If cnQuery.State = adStateOpen Then cnQuery.Close
        cmdRunAsyncQuery.Enabled = True
        cmdCancel.Enabled = False
    End Sub
    
    '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
    End Sub
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    4
    Rep Power
    0
    Thanks. I will try it.

IMN logo majestic logo threadwatch logo seochat tools logo