SqlDataReader & paging

  • Thread starter Thread starter Mark Rae
  • Start date Start date
M

Mark Rae

Hi,

I have an <asp:DataGrid> control to which I'm trying to bind an
SqlDataReader. When I do so, I get the following error:

AllowCustomPaging must be true and VirtualItemCount must be set for a
DataGrid with ID dgrdTimesheet when AllowPaging is set to true and the
selected datasource does not implement ICollection.

If I set the datagrid's AllowCustomPaging property to true and its
VirtualItemCount property to be the number of records returned by the
SqlDataReader, the error disappears. The datagrid's paging hyperlinks
correctly show the correct number of pages, but each page contains the first
page's data. I.e.

SqlDataReader contains 76 rows
DataGrid's page size is 10 rows
DataGrid correctly shows pages 1-8
Clicking on page 1 correctly shows the first 10 records
Clicking on any other page incorrectly shows the first 10 records, not
11-20, 21-30 etc.

Of course, using a DataSet object instead of an SqlDataReader object cures
the problem instantly, but I was wondering if there is any way to use an
SqlDataReader object as the datasource of a datagrid which supports paging?

Any assistance gratefully received.

Mark
 
Consider that the DataReader is a "connected" interface. That is, it
requires that the connection be maintained throughout the rowset population
(fetching) process. In a Windows forms application you might be able to get
away with paging a few rows at a time as the connection can easily be held
open as long as needed. However, in an ASP application, the connection is
(typically) closed and returned to the pool as the ASP page is rehydrated
each time it's invoked. No, I don't expect this approach will work.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Consider paging at the stored procedure.
You pass the page you want and pass back 10 (or whatever number you decide)
rows in the resultset, if available. Just in case you're at the last page
(could be less than 10).

Also, pass the number of pages back as an OUTPUT parameter so the client
knows how many pages there is, so you can give a "1 of 200 pages" message.
Give the user an idea of how many pages that are available because if a user
see's "1 of 200 pages", the user will redefine thier query.

Of course for this to work you'll need a unique key to work off of.

That's what I'm doing right now.
 
Back
Top