Resolved..... - Query timeout when using odbcDataAdapter

[manju]

Hi all,

In my asp.net application when I try to run my query using ODBCDataAdapter it throws the exception 'Query timeout expired'.
I am using dataadapter as I fill up the results into a dataset which is bound as datasource to my datagrid to display to user.

The same generates results when I use a OdbcDataReader.

Any reasons y this is happening??

Also does anyone have any idea on the number fo rows that can be displayed in a datagrid in a single page.
When I try to display the 50,000+ rows in a single page the ASP.Net wp.exe gets recycled.

P.S: The query returns more then 50,000 rows.


Any help will be appreciated.

[Cudabean]

I remember reading about an important feature of a datagrid that allows you to automatically present the result to the user in 100-200 row increments.

That said, even using the paging feature, I can't imagine any useful reason for sending a 50,000 row result to a user. That could take a couple of days to scroll through.

cudabean

[manju]

Thanks Cudabean for replying back. The reason I want the results in datagrid w/o paging is becos later user can export then results into excel spreadsheet.

StringWriter tw = new StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

this.dgCounts.RenderControl( hw ); // Get the HTML for the control.
Response.Write( tw.ToString() ); // Write the HTML back to the browser.

If I implement paging then my data exported to excel will be only the first page.

Also any ideas y I receive Query Timed out on using ODBCDataAdapter??

[Cudabean]

ideas y I receive Query Timed out on using ODBCDataAdapter??

Well, the obvious one is that the huge query exceeds the timeout period before succesfully completing.

As far as the other problem. I'd approach it like this:
Present the user with some representative data, i.e. the first 100 lines and give them the option to export the whole thing to a spreadsheet. Don't make them cut and paste, format the data into an excel spreadsheet and let them download the resulting file. It will be way faster all around.

cudabean

[manju]

Thanks a lot.

Shall post the result by tomorrow..

[manju]

after research on micorosft site I got the following article:

http://support.microsoft.com/default...Product=aspnet

PRB: "Request Timed Out" Error Occurs When You Use the DataAdapter Method in an ASP.NET Application
SYMPTOMS
When you use the DataAdapter.Fill method, or you run a query in an ASP.NET Web application that takes more than 90 seconds to process, you may receive the following error message:

HttpException (0x80004005): Request timed out.
Note This error occurs only when you run the Web application in release mode, and the value of the Debug attribute in the Web.Config file is set to false.
CAUSE
By default, the value of the executionTimeout attribute is set to 90 seconds in the Machine.config file. This error occurs when the processing time exceeds 90 seconds.
WORKAROUND
To work around this problem, increase the time-out value that is set for the executionTimeout attribute in the configuration file.

The executionTimeout attribute exists under httpRequest in the Machine.config file. You can change these settings either in the Web.Config file or in the Machine.config file. The default value for the time-out is 90 seconds. The executionTimeout attribute indicates the maximum number of seconds a request is permitted to run before being shut down by the ASP.NET Web application.



Hope this helps someone else whos trying.