Click here to Skip to main content
13,559,193 members
Click here to Skip to main content
Add your own
alternative version


82 bookmarked
Posted 5 Feb 2006
Licenced Ms-RL

Paging GridView with ROW_NUMBER()

, 5 Feb 2006
Rate this:
Please Sign up or sign in to vote.
This article explains how to implement custom paging in the GridView web control using the ROW_NUMBER() function of SQL Server 2005

Sample Image - PagingGridView.jpg


Searching on the web, I found a lot of useful articles about this new feature of SQL Server 2005 and among them I mention the Jason Witty’s article - that was a starting point for me. We have been waiting for a long time for this new feature of SQL Server 2005. I don’t want to compare this new kind of ASP .NET paging with the well-known paging using


, RowCount method or Cursor method, used with SQL Server 2000, but rather how simple is to paging a GridView in this way and to customize it. I don't use the built-in mechanism for paging of GridView web control.

I wrote this sample, because I needed to add custom paging for a grid view that allows easily changing the page size and go directly to a page number without passing throughout each page, one by one. In the past, I already used this kind of paging with data grid. To change it from datagrid to gridview I cut and paste the code and the job is done. Code behind is really a rudimentary one and is very easily to understand.


In the Size button click event, we set the current page number and the page size and the we bind the GridView to display the data:

PageNum = 1;
PageSize = Convert.ToInt16(PageSizeTxt.Text);
In the Go button event click, where we go to a page number:
if (GoToPageTxt.Text != "")
    int maxPage  = Convert.ToInt32(TotalPages.Text);
    int goToPage = Convert.ToInt32(GoToPageTxt.Text);

    if (goToPage <= maxPage)
        PageNum = goToPage;
Here is the GridView HTML code:
<asp:GridView  ID="GridView1" runat="server" AutoGenerateColumns="False" >
              <asp:BoundField DataField="RowNumber" HeaderText="RowNumber" />
              <asp:BoundField DataField="Name" HeaderText="Name" />
              <asp:BoundField DataField="SalesYTD" HeaderText="SalesYTD" />
              <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />

I built the stored procedure with a SQL Server 2005 Books Online example. As you can see, it returns the ROW_NUMBER for the salespeople in AdventureWorks database, based on the year-to-date sales. My intention is not to explain how ROW_NUMBER() works, because it's already done in SQL Server 2005 Books Online or in many articles on the web, but how to pass the parameters to a stored procedure for customizing paging using ROW_NUMBER(). I just included the Microsoft query into my stored procedure and the paging works very well. Finally, the query returns the total number of records to calculate how many pages we can display. The stored procedure script is shown below:


CREATE PROCEDURE [dbo].[sp_GridView_RowNumber]
    @PageNum int,
    @PageSize int,
    @TotalRowsNum int output

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    -- Use ROW_NUMBER function
    WITH Salespeople_AdventureWorks As
        SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY SalesYTD DESC), 
               'Name' = c.FirstName + ' ' + c.LastName, s.SalesYTD, a.PostalCode
        FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
        JOIN Person.Address a ON a.AddressID = c.ContactID
        WHERE ((TerritoryID IS NOT NULL) AND (s.SalesYTD <> 0))

    -- Query result
    SELECT * 
    FROM Salespeople_AdventureWorks
    WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize             

    -- Returns total records number
    SELECT @TotalRowsNum = count(*) 
    FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
    JOIN Person.Address a ON a.AddressID = c.ContactID
    WHERE ((TerritoryID IS NOT NULL) AND (s.SalesYTD <> 0))
The source code includes the solution in Visual Studio 2005 and the script for the stored procedure. You have to change your connection string in the code to run the sample.


This article, along with any associated source code and files, is licensed under Microsoft Reciprocal License


About the Author

Web Developer
Canada Canada
No Biography provided

You may also be interested in...

Comments and Discussions

Questionhow to download? Pin
Member 120026901-Aug-16 20:04
memberMember 120026901-Aug-16 20:04 
AnswerRe: how to download? Pin
OriginalGriff1-Aug-16 20:04
protectorOriginalGriff1-Aug-16 20:04 
GeneralRe: how to download? Pin
Socrate121-Aug-16 10:02
memberSocrate121-Aug-16 10:02 
AnswerRe: how to download? Pin
Socrate121-Aug-16 10:02
memberSocrate121-Aug-16 10:02 
GeneralRe: how to download? Pin
Socrate121-Aug-16 7:22
memberSocrate121-Aug-16 7:22 
QuestionThank You Pin
kazemtnt23-Mar-13 8:26
memberkazemtnt23-Mar-13 8:26 
QuestionHow to impliment with store procedure Pin
prince_rumeel20-Mar-13 0:33
memberprince_rumeel20-Mar-13 0:33 
GeneralMy vote of 4 Pin
thinkpad_r50024-Apr-11 19:11
memberthinkpad_r50024-Apr-11 19:11 
GeneralMy vote of 5 Pin
Neullson9-Aug-10 22:54
memberNeullson9-Aug-10 22:54 
GeneralI think this solution is good Pin
niithoang19-Sep-09 19:19
memberniithoang19-Sep-09 19:19 
QuestionHow can I have sorting for this gridview? Pin
sepid2-Jan-09 5:00
membersepid2-Jan-09 5:00 
Questionwhats the difference? Pin
marktoth3-Oct-08 19:24
membermarktoth3-Oct-08 19:24 
AnswerRe: whats the difference? Pin
PigsOnTheWing19-Oct-08 3:51
memberPigsOnTheWing19-Oct-08 3:51 
GeneralTry this Method Pin
mayurmv4-Sep-08 17:11
membermayurmv4-Sep-08 17:11 
QuestionInvalid column name 'rownum' error Pin
auxcom27-Feb-07 1:29
memberauxcom27-Feb-07 1:29 
GeneralFantastic Example, Thank you!!! [modified] Pin
great_scandinavian1-Feb-07 20:53
membergreat_scandinavian1-Feb-07 20:53 
QuestionSqlDataAdapter Pin
maxtoroq4-Jan-07 16:54
membermaxtoroq4-Jan-07 16:54 
GeneralAll well and good, but... Pin
Rob_Ehle11-Oct-06 7:30
memberRob_Ehle11-Oct-06 7:30 
GeneralNot a good solution Pin
kolbis4-Jul-06 19:25
memberkolbis4-Jul-06 19:25 
What will happen when you need to implement serveral grids in the application?
You will have to implement for each, the navigation bar you created.
Sniff | :^)

"to code or not to code?" this is the question..

GeneralRe: Pin
Socrate129-Jul-06 14:05
memberSocrate129-Jul-06 14:05 
GeneralIncorrect syntax near the keyword 'WITH' Pin
sasire186-Feb-06 2:49
membersasire186-Feb-06 2:49 
GeneralRe: Incorrect syntax near the keyword 'WITH' Pin
Socrate16-Feb-06 5:17
memberSocrate16-Feb-06 5:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180515.1 | Last Updated 5 Feb 2006
Article Copyright 2006 by Socrate1
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid