[Solved] Grid / Virtual Scrolling and Paging Horrible example

[Jim]

We purchased the Telerik ASP.Net AJAX control set last week, and one of the main reasons for this purchase was the virtual scrolling grid.

This grid example is flawed in three major ways:

1)      The virtual item count is hard-coded

2)      The selected rows are being determined by the row ID of the selected table

3)      The scrolling does not function properly.

The example being used is based on a very unrealistic query from a single table where the row selections are being determined by the row id's of the table. In almost any real-world example, a query would be derived from a query involving more than a single table, which would preclude the use of a table row id as a row selection device. I have figured out how to get around this limitation using SQL Server 2005, which has a built in function called ROW_NUMBER() that allows me to add row numbers from the returned recordset, and select a subset of the returned records by row number.  Another problem with this example is the fact that you have used a hard-coded value for the virtual Item Count. Once again, in a real-world scenario, no query would produce the same number of results every time, and in order for the virtual scrolling to work properly, this is a necessity. I got around this problem by performing a second query without limiting the returned rows to just get the count.

But the problem I can’t get around is that the scrolling in this example does not work properly in IE or Firefox. Just navigate to the example, and click a single time on the down arrow at the base of the scroll bar. Wait for 3-5 seconds, and click the arrow again. Do this 4-6 times and you will see the problem.

1^st Click – grid moves down one row (no problem)

2^nd Click - grid moves down one row (no problem)

3^rd Click – grid moves down one row, then goes to page two, and the grid posts back to the server and returns a single record from page 2.

4^th Click – grid posts back, the scroll tool tip indicates that the grid is moving to page4, then changes to indicate page 5, but the paging navigation at the bottom is indicates that we are on page 4

5^th Click – grid posts back, the scroll tool tip indicates that the grid is moving to page 6, then changes to indicate page 8, but the paging navigation at the bottom indicates that we are on page 6

6^th Click – grid posts back, the scroll tool tip indicates that the grid is moving to page 9, then changes to indicate page 13, but the paging navigation at the bottom indicates that we are on page 9

Every subsequent click of the down arrow causes the grid to post back to the server.

[Jim]

I just wanted to publish the SQL that would work in SQL Server 2005.

RadGrid1.DataSource = GetDataTable(

 

 

"WITH OrderedResult AS ( " +  

 

" SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS ROWID, " +  

 

" OrderID, ProductID, Quantity, Discount " +  

 

" FROM LargeOrderDetails " +  

 

" ) SELECT * " +  

 

" FROM OrderedResult " +  

 

" WHERE ROWID BETWEEN " + RadGrid1.CurrentPageIndex * RadGrid1.PageSize + " AND " + ((RadGrid1.CurrentPageIndex + 1) * RadGrid1.PageSize));

[Veli]

Hello Jim,

Thank you for sharing with us a better approach to fetch paged data from MS SQL Server. Indeed, different SQL distributions provide different approaches for paged data. MySQL, for example, supports the LIMIT key word with which you can specify a range of data records (a page of data) to select.

MS SQL Server, even though not supporting direct paging, provides the ROW_NUMBER() function to retrieve the successive index of the records, zero-based on the first record returned with the SELECT statement.

The RadGrid virtual scroll paging demo, demonstrates how the SQL SELECT statement can be adjusted to retrieve a paged set of records, but only with a single primary key of increasing values (an Id column). In fact, this demo does not aim at reproducing a real-life scenario of data retrieval and paging, but rather demonstrate RadGrid's virtual scroll paging capability. Your suggestion covers the more general approach of returning paged data that can be used for a broader range of scenarios.

Thank you for sharing.

Best wishes,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.