Max Datagrid DataSource Size

I ran into this problem and was curious what others think. I have a web app with 3 datagrids on. Each datagrid I am creating a datatable from a database then binding it to the datagrid. 2 of the 3 datagrids have about 6,000 records in, and the 3rd has about 25,000. I also have a button that allows you to print out by a certrain criteria. My problem happens when you click the button, if i have all my records in the datagrids, the button doesnt go into the on click, i just get a page can not be displayed. If the 3rd datagrid i reduce the number to 20,000 everything works fine. I then went and added a column to the 3rd datagrid. When I click the button i get the same page can not be displayed. So I reduced that number down to 17,000 and everything works fine again. The only thing that I can think is that there is a maximum size to a datatable that can be bound to a datagrid. I'm not sure if that is true. I was curious if anyone else had a problem like this, or any ideas as to what is causing this.

Thanks.

I figure, from your description, that you're using ASP.NET.

The possible cause of your problem is a timeout, I would guess, caused by retrieving so much data, in that there may not be enough memory available to load the data, or something is slowing the processing down in a way that the error/timeout occurs. What are the specs on your machine?

As a solution, I would suggest that you page the data you are getting. Here's a sample SP I wrote for paging records.

Code:

---

CREATE PROCEDURE Get_Employees( @CurrentPage int,
    @PageSize int,
    @TotalRecords int OUTPUT)

AS

-- Turn off count return.
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

-- Create a temp table to hold the current page of data
-- Add an ID column to count the records
Create Table #TempTable
(
 EmpId int IDENTITY PRIMARY KEY,
 fname varchar(20),
 lname varchar(30),
 pub_id char(4),
 hire_date datetime
)

--Fill the temp table with the reminders
Insert Into #TempTable
(
 fname,
 lname,
 pub_id,
 hire_date
)
Select  fname,
  lname,
  pub_id,
  hire_date
From  employee
Order By lname

--Select one page of data based on the record numbers above
Select fname,
 lname,
 pub_id,
 hire_date
From #TempTable
Where EmpId > @FirstRec
And EmpId < @LastRec

--Return the total number of records available as an output parameter
Select @TotalRecords = Count(*)
From #TempTable
GO

---