How to Handle ADO Timeouts

Hi,

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};dbq=DW1.world;UID=<uid>;pwd=<pwd>"
cn.CursorLocation = adUseClient
cn.ConnectionTimeout = 0                              'I have also tried 180 here
cn.Open

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.
LVL 1
tlchavetAsked:
Who is Participating?
 
leclairmConnect With a Mentor Commented:
Since you are using ado, try either microsofts oracle provider or use Oracles (from www.connectionstrings.com) :

"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???
0
 
PhilAICommented:
This may help... Not done a lot of work with Oracle ODBC myself

http://support.microsoft.com/default.aspx/kb/251248
0
 
PhilAIConnect With a Mentor Commented:
What is your SQL statement? That could be enchanced?
0
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
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
0
 
tlchavetAuthor Commented:
The cn.CommandTimeout worked perfect!  Thanks for the quick response!
0
All Courses

From novice to tech pro — start learning today.