Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 25th, 2006, 02:07 PM
Registered User
 
Join Date: May 2006
Location: , , .
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)


Reply With Quote
  #2 (permalink)  
Old August 25th, 2006, 02:18 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
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
Reply With Quote
  #3 (permalink)  
Old August 25th, 2006, 02:34 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #4 (permalink)  
Old August 27th, 2006, 01:07 PM
Registered User
 
Join Date: May 2006
Location: , , .
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

Reply With Quote
  #5 (permalink)  
Old August 27th, 2006, 04:24 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #6 (permalink)  
Old August 28th, 2006, 04:01 PM
Registered User
 
Join Date: May 2006
Location: , , .
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



Reply With Quote
  #7 (permalink)  
Old August 28th, 2006, 04:27 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #8 (permalink)  
Old September 8th, 2006, 01:05 PM
Registered User
 
Join Date: Sep 2006
Location: Reno, NV, USA.
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


Reply With Quote
  #9 (permalink)  
Old September 8th, 2006, 01:55 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
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."
Reply With Quote
  #10 (permalink)  
Old September 8th, 2006, 02:06 PM
Registered User
 
Join Date: Sep 2006
Location: Reno, NV, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:31 PM.


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