Introduction
Anybody in the DB world knows what paging database results is and its effect. From the time I had started getting into good old classic ASP, I was intrigued with the ability to divide large sets of data into sections of x records per page. One thing that I didn't like about paging is that it seemed sites incorporated just a < Prev and Next > link on the search results page. I wasn't satisfied with such a lackluster paging technique, and from there I searched high and low on every ASP Web site I could find to see if there was code to show more advanced paging options, such as how many pages were remaining to be paged through, or, if the next page was the last page of results, how many records were on that last page. Unfortunately, I couldn't find any such code, so I had set out to do it myself. (To see the proposed paging enhancements I like, check out the live demo for this article...)

I have since coded a number of techniques for advanced paging in classic ASP, but my latest challenge has been to incorporate the same paging techniques in ASP.NET! (For more information on ASP.NET, be sure to visit the ASP.NET Article Index.) Pssst, don't ask me to talk about redoing my app in Beta 1, and upon upgrading to Beta 2 was horrified that my code needed to be redone.... by the way, the code in this article is all Beta 2 compliant.

Now, if anyone has looked into the Microsoft .NET SDK and Quickstart samples you will find custom paging samples, but it's the usual next and prev stuff. Now let's see how we can kick this paging up a notch and tell us more detail about our data output.

Accessing our Data
The first step is of course to query our database, and send our data into our datagrid. The first thing we should concern ourselves with in any .Net page is that we import the necessary namespaces for our app. In this case, as with most data access apps, I'm importing System.Data and System.Data.SqlClient for SQL Server. If you use MS Access or another database then System.Data.OleDb namespace and associated classes will work just fine, providing you modify the connection variables and data adapters.

This importing of namespaces is all done before our script tags like so:

Now, within our server-side script tags we include our object-oriented stuff our Subroutine to access our database and bind our result set into our ASP.NET Datagrid. This subroutine, BindSQL() needs to first create all of our variables that we'll use:

Sub BindSQL()
  Dim MyConnection As SqlConnection
  Dim DS as DataSet
  Dim MyCommand As SqlDataAdapter
  Dim RcdCount As Integer
  
  'Our SQL string
  Dim sqlStr As String = "SELECT titles.title, authors.au_lname, " & _
                            "authors.au_fname, titles.price " & _
                         "FROM authors INNER JOIN titleauthor ON " & _
                         "authors.au_id = titleauthor.au_id " & _
                         "INNER JOIN titles ON " & _
                             "titleauthor.title_id = titles.title_id"

  'The connection to our database
  Dim strConn As String = "server=(local);uid=sa;pwd=;" & _
                          "database=pubs;Trusted_Connection=yes;"

Next we need to instantiate our connection and command object, and the fill our DataSet object with the results of the SQL query:

  ...

  'Open up our connection with our connection object
  MyConnection = New SQLConnection(strConn)

  'To execute our Sql Statement and provide our active connection
  MyCommand = NewSqlDataAdapter(sqlStr, MyConnection)

  'Create instance of DataSet object and fill our predetermined 
  'datagrid with it and we name it
  DS = new DataSet()
  MyCommand.Fill(DS, "pubs")

  ...

Now comes the one part that we'll used for our custom paging the record count, and you'll see it quite different than our classic ASP way.

Now that we have this total count of the records in the DataSet, we'll save it to a global variable, since we'll want to access it from other subroutines. The variable ResultCount should be defined in global-scope, as an Integer. (See the complete source later on in this article to note how to create global-scoped variables)

Next, we display the number of records found in a label control:

Finally, at this point, we can bind our DataSet to the DataGrid and display a label illustrating what page of results we're currently viewing: which will display :

At this point, we need to determine if we need to show the Next/Prev links, as well as the First Page/Last Page links:

'Do we want to show the prev/First Page buttons
  If Pubs.CurrentPageIndex <> 0 Then
    Call Prev_Buttons()
    Firstbutton.Visible = true
    Prevbutton.Visible = true
  Else
    Firstbutton.Visible = false
    Prevbutton.Visible = false
  End If

'Do we want to show the Next/Last Page buttons
  If Pubs.CurrentPageIndex <> (Pubs.PageCount-1) then
    Call Next_Buttons()
    NextButton.Visible = true
    Lastbutton.Visible = true
  Else
    NextButton.Visible = false
    Lastbutton.Visible = False
  End If
End Sub

The HTML Portion
In our HTML section we need a couple of label controls to display various bits of information, such as what page we are currently viewing, how many total records there are, etc. We also need a DataGrid, to which we are binding the database results to. Don't forget to put the DataGrid in a WebForm (a <form> tag with the runat="server" attribute specified). Finally, we need a series of LinkButton Web controls, to display our Prev/First Page and Next/Last Page links.

The content for the HTML section is as follows:

<html>
<body>
  'For our recordcount and pagecount
  <asp:Label id="lblPageCount" runat="server" /><br>
  <asp:label id="RecordCount" runat="server" />
  
  <form runat="server">
    <ASP:Datagrid id="pubs" runat="server" 
                  AllowPaging="True" 
                  AllowCustomPaging="False" 
                  Pagesize="10" 
                  PagerStyle-Visible = "False"
    />

    <%-- Display the First Page/Previous Page buttons --%>
    <asp:linkbutton id="Firstbutton" Text="<< 1st Page" 
                    CommandArgument="0" runat="server" 
                    onClick="PagerButtonClick"/>
    <asp:linkbutton id="Prevbutton" Text= "" 
                    CommandArgument="prev" runat="server" 
                    onClick="PagerButtonClick"/>

<%-- Display the Next Page/Last Page buttons --%>
    <asp:linkbutton id="Nextbutton" Text= "" 
                    CommandArgument="next" runat="server" 
                    onClick="PagerButtonClick"/>
    <asp:linkbutton id="Lastbutton" Text="Last Page >>" 
                    CommandArgument="last" runat="server" 
                    onClick="PagerButtonClick"/>

    <br><br><br><br>

    Change Pagesize
    <asp:DropDownList id="ps" runat="server">
        <asp:ListItem>4</asp:ListItem>
        <asp:ListItem>5</asp:ListItem>
        <asp:ListItem>7</asp:ListItem>
        <asp:ListItem selected>10</asp:ListItem>
        <asp:ListItem>12</asp:ListItem>
        <asp:ListItem>15</asp:ListItem>
        <asp:ListItem>22</asp:ListItem>
    </asp:DropDownList>
    
    <asp:button text="Change Pagesize" runat="server" OnClick="RePage"/>
  </form>
</body>
</html>

Note that in our DataGrid Web control we set the PagerStyle's Visible property to False. This is because we are implementing our own paging solution, and don't want to use the default paging style supported by the DataGrid Web control. (For more information on paging database results using the DataGrid's built-in functionality, be sure to read: Paging Database Results in ASP.NET!) Also note that the four LinkButton controls all specify the server-side subroutine PagerButtonClick as the sub to be called when they are clicked; similarly, the "Change Pagesize" button has the RePage subroutine defined as its OnClick event handler.

The event handler for the four LinkButtons (PagerButtonClick) must display the appropriate page of data, be it the next page, the previous page, the first page, or the last page. Which page to display, of course, depends on what LinkButton the user clicked. The PagerButtonClick (shown below) uses the CommandArgument passed in from the LinkButton Web controls to determine which control was clicked, and then takes the appropriate action.

Sub PagerButtonClick(sender As Object, e As EventArgs)
  'used by external paging UI
  Dim arg As String = sender.CommandArgument

  Select arg
     Case "next":  'The next Button was Clicked
        If (Pubs.CurrentPageIndex < (Pubs.PageCount - 1)) Then
            Pubs.CurrentPageIndex += 1
        End If 

     Case "prev":   'The prev button was clicked
         If (Pubs.CurrentPageIndex > 0) Then
             Pubs.CurrentPageIndex -= 1
         End If

     Case "last":   'The Last Page button was clicked
         Pubs.CurrentPageIndex = (Pubs.PageCount - 1)

     Case Else:     'The First Page button was clicked
         Pubs.CurrentPageIndex = Convert.ToInt32(arg)
	End Select

    'Now, bind the data!
    BindSQL()
End Sub

The RePage event handler, which is called when the "Change Pagesize" button is clicked, simply resets the DataGrid's CurrentPageIndex property back to 0 and rebinds the database data:

Sub Repage(sender As Object, e As EventArgs)
  Pubs.CurrentPageIndex = 0
  BindSQL()
End Sub

Finally, the last two server-side subroutines are two meager helper subroutines, Next_Buttons() and Prev_Buttons(), which display the correct text for each of the LinkButtons. These two subs, which are called from BindSQL(), can be seen below:

Sub Prev_Buttons()
  Dim PrevSet As String

  If Pubs.CurrentPageIndex+1 <> 1 and ResultCount <> -1 Then
    PrevSet = Pubs.PageSize
    PrevButton.Text = ("< Prev " & PrevSet)
	
    If Pubs.CurrentPageIndex+1 = Pubs.PageCount Then
      FirstButton.Text = ("<< 1st Page")
    End If
  End If
End Sub


Sub Next_Buttons()
  Dim NextSet As String

  If Pubs.CurrentPageIndex+1 < Pubs.PageCount Then
    NextSet = Pubs.PageSize
    NextButton.Text = ("Next " & NextSet & " >")
  End If

  If Pubs.CurrentPageIndex+1 = Pubs.PageCount-1 Then
    Dim EndCount As Integer 
    EndCount = ResultCount - (Pubs.PageSize * (Pubs.CurrentPageIndex+1))
    NextButton.Text = ("Next " & EndCount & " >")
  End If
End Sub

Well, that's it! Be sure to view the complete code, try out the live demo, and read up on the related articles! If you have any questions, please do not hesitate to email me!

Happy Programming!