I am working on an ASP.net 2.0 application written in C#, pulling data from an Oracle 10g database on a remote server (i.e. the application and database are on different servers).
The data is pulled via a SqlDataSource control. When I run small, quick queries, everything works fine, but when I try to run a query that returns 3000-4000 records, I get the error message in the subject line. The query times out after about 15 seconds. I have taking the exact query that my application is trying to run, and ran it directly on the database server. It takes a few seconds, but it works. I have reached the conclusion that it must be a timeout issue.
I have pored through many forums, and based on my findings, tried 4 fixes:
1) Going into the control panel, then administrative tools, then ODBC sources. I selected the appropriate DSN, and unchecked the "Enable Query Timeout" option.
2) Added the connection lifetime and connection timeout options to the connection string as follows:
sql.ConnectionString = "DSN=******;Connection Lifetime=0;Connection Timeout=0;UID=****;PWD=****; DRIVER={Microsoft ODBC for Oracle}; SERVER=***.***.**.*;";
3) I have added the line "SQLNET.EXPIRE_TIME = 0" to every one of the three sqlnet.ora files on the database server.
4) An old coworker of mine used "Server.ScriptTimeout = 600" in his code. But, these were plain old ASP pages.
None of these things worked. Please help!!!