How to Handle ADO Timeouts

Posted on 2006-05-18
Last Modified: 2013-12-25

I have a small VB 6.0 Pro application that executes a series of select statements against Oracle through ADO.  One of the select statements takes nearly 60 seconds to run outside of the application, but times out when executed through ADO.  I have tried setting the connectiontimeout to 180 without success and 0 without success.  The connection are recordset are established as follows:

Set cn = New ADODB.Connection
cn.ConnectionString = str                               'Where str is "Driver={Oracle in OraHome92};;UID=<uid>;pwd=<pwd>"
cn.CursorLocation = adUseClient
cn.ConnectionTimeout = 0                              'I have also tried 180 here

Set rsWO = New ADODB.Recordset
rsWO.CursorLocation = adUseClient
rsWO.CacheSize = 500
rsWO.CursorType = adOpenForwardOnly
rsWO.LockType = adLockReadOnly
rsWO.Open SQL, cn

Any help would be greatly appreciated.
Question by:tlchavet
    LVL 6

    Expert Comment

    This may help... Not done a lot of work with Oracle ODBC myself
    LVL 6

    Assisted Solution

    What is your SQL statement? That could be enchanced?
    LVL 52

    Assisted Solution

    by:Carl Tawn
    ConnectionTimeout controls the timeout for actually establishing the connection to the DB, not the execution time for queries. For that you need to use the CommandTimeout property:

        cn.CommandTimeout = 180
    LVL 11

    Accepted Solution

    Since you are using ado, try either microsofts oracle provider or use Oracles (from :

    "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"
    "Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"

    You could always try setting the cn.CommandTimeout to zero as well.  

    Perhaps you should look into creating stored procs to handle these queries???
    LVL 1

    Author Comment

    The cn.CommandTimeout worked perfect!  Thanks for the quick response!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now