Wrox Programmer Forums
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 25th, 2006, 02:07 PM
Registered User
 
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Timeout expired

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)


 
Old August 25th, 2006, 02:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there..

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

HTH

Gonzalo
 
Old August 25th, 2006, 02:34 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old August 27th, 2006, 01:07 PM
Registered User
 
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 27th, 2006, 04:24 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old August 28th, 2006, 04:01 PM
Registered User
 
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old August 28th, 2006, 04:27 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old September 8th, 2006, 01:05 PM
Registered User
 
Join Date: Sep 2006
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old September 8th, 2006, 01:55 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old September 8th, 2006, 02:06 PM
Registered User
 
Join Date: Sep 2006
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Timeout Expired Error nikotromus SQL Server 2000 16 December 5th, 2006 03:54 PM
TimeOut Expired Lalit_Pratihari SQL Server 2000 1 July 23rd, 2006 12:59 PM
Timeout expired glibo SQL Server 2000 6 July 21st, 2006 11:58 AM
Timeout Expired Error vinod_pawar1 SQL Server 2000 5 July 17th, 2004 09:37 PM
Timeout expired ssingh SQL Server 2000 4 April 19th, 2004 11:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.