Timeout expired

ks2006 · August 25th, 2006, 02:07 PM

I am developing a ASP.NET application using sql server database. I was trying to bind the resultset to a data grid and I received the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I am retriving data from 2 tables (tbl1 and tbl2)
when I do select * from tbl1 that contains 2000 rows ..application works fine, but when I select records form tbl2 that has more than 60,0000 records, the application throws above error.

I have to retrive all the rows from tbl2 and bind it to a datagrid.

Please help!!!!
(It looks like I cannot access a very large dataset..please correct me)

gbianchi · August 25th, 2006, 02:18 PM

hi there..

how are you binding the grid?? usually the data source object has a timeout property...

HTH

Gonzalo

Imar · August 25th, 2006, 02:34 PM

Hi ks2006,

Personally, I think your design may be flawed. How is a user going to cope with 600000 records in a page? Have you considered the massive amount of HTML that's going to produce?

What about a more light-weight approach using paging?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

ks2006 · August 27th, 2006, 01:07 PM

Thanks a lot for replying.
Here is what i am trying to do:

I am in a research field where I have to provide data to the users in the form of an excel sheet. I am retriving the data in the dataset and binding the dataset to the grid, then exporting the datagrid to excel so that users can download the sheet. This approach works fine as long as I export 2000 rows, but when I have a large dataset I get the timeout error.
I am kind of new to ASP.NET. I am open for any suggestion and please let me know if I am using a wrong approach. Could i use something else other than a datagrid??

Again thanks a lot.
Ks

Imar · August 27th, 2006, 04:24 PM

You won't be able to export more than 65,000 rows to Excel. This is a technical limitation of Excel.

If you still want to export a (smaller) dataset to Excel you can use code like this to stream a DataSet to the browser:

Code:

public static void PushDataSetAsExcelSheet(DataSet myDataSet, HttpResponse myResponse)
{
  myResponse.Clear();
  myResponse.Charset = "";
  myResponse.ContentType = "application/x-unknown";
  myResponse.AddHeader("Content-Disposition", "attachment; filename=\"SomeName.xls\"");
  StringWriter myStringWriter = new System.IO.StringWriter();
  HtmlTextWriter myHtmlWriter = new System.Web.UI.HtmlTextWriter(myStringWriter);
  DataGrid dg = new DataGrid();
  dg.DataSource = myDataSet.Tables[0];
  dg.DataBind();
  dg.RenderControl(myHtmlWriter);
  myResponse.Write(myStringWriter.ToString());
  myResponse.End();
}

You may need to add a few using statements (Import in VB) to make this work, but the compiler will tell you what to do....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

ks2006 · August 28th, 2006, 04:01 PM

I have already used above code to export datagrid to excel, and still I get timeout exception.

I am unable to even export 6000 rows to excel.

Again Thanks Imar for your reply

Imar · August 28th, 2006, 04:27 PM

In that case, I don't know what's going on. I have successfully used this code in the past myself....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Coleen · September 8th, 2006, 01:05 PM

Hi Imar :-)

Thanks for posting your snippet of code. I am trying to do the same thing - at least initially. I am trying to download/export data in an existing datagrid into an excel file. I am using VB.net (2003) and would appreciate a little help in converting the code. I've been able to get the code to open an HTML file of the datagrid - but NOT an excel file, and yes I do have Excel 2000 installed on my computer.

Any help you can give me would be most appreciated, such as what Imports do I need? How Do I connect to an existing datagrid? Thanks in advance for your help,

Coleen

dparsons · September 8th, 2006, 01:55 PM

What Imar has provided should work without flaw as I have used a similar process to pass a table to Word such as:

        Dim table As New Table
        Dim tr As TableRow
        Dim tc As TableCell

        [some more code]

        Dim strStringWriter As New System.IO.StringWriter
        Dim htmlWriter As New HtmlTextWriter(strStringWriter)
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=Report.doc")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.No Cache)
        Response.ContentType = "application/vnd.word"

        table.RenderControl(htmlWriter)
        Response.Write(strStringWriter.ToString)
        Response.End()

The only thing that I am doing differently is that I am exporting to Word, and that I render a Table. (Though i should point out the [some more code] area above is where i actually construct the data for my table control using a for loop to loop through the records in a Datatable that i have retrieved from SQL)

P.S. ks2006: what you may be running into is the physical time out of the application is being reached so you may want to increase your HTTP Timeout in your config file.

hth

"The one language all programmers understand is profanity."

Coleen · September 8th, 2006, 02:06 PM

Thanks - actually I did get it ro work - but you have to save the file when the download window comes up instead of trying to open it directly. Also in order to view my original dtagrid I had to create a button event and move the code to the the on click of the button - that way the original datagird renders, and I can download it to an excel file! Thanks very much!

Coleen