Custom ASP.NET Datagrid Paging With Exact Count

The Source Code

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
' Only bind the data on the first visit to the page
If Not Page.IsPostBack
End If
End Sub
'Create an variable in Global scope
Dim ResultCount as Integer

Sub BindSQL()
Dim MyConnection As SqlConnection
Dim DS as Dataset
Dim MyCommand As SqlDataAdapter
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"
Dim strConn As String = "server=(local);uid=sa;pwd=;database=pubs;Trusted_Connection=yes;"
Dim RcdCount As Integer
'Open up our connection with our connection object
MyConnection = New SQLConnection(strConn)
'To execute our Sql Statement and provide out active connection
MyCommand = New SqlDataAdapter(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")
RcdCount = DS.Tables("pubs").Rows.Count.ToString()
Pubs.Pagesize = CInt(ps.SelectedItem.Value)
If Not Page.IsPostback
    Pubs.CurrentPageIndex = 0
End if
ResultCount = RcdCount
RecordCount.Text = "<b><font color=red>" & RcdCount & "</font> records found"
'Now we assign the dataview to the datasource of the datagrid and we send it right on it
    Pubs.DataSource = DS
Catch e As Exception
    Pubs.CurrentPageIndex = 0
End Try
If Pubs.CurrentPageIndex <> 0 Then
    Call Prev_Buttons()
    Firstbutton.Visible = true
    Prevbutton.Visible = true
    Firstbutton.Visible = false
    Prevbutton.Visible = false
end if
If Pubs.CurrentPageIndex <> (Pubs.PageCount-1) then
    Call Next_Buttons()
    NextButton.Visible = true
    Lastbutton.Visible = true
    NextButton.Visible = false
    Lastbutton.Visible = False
end if
lblPageCount.Text = "Page " & Pubs.CurrentPageIndex + 1 & " of " & Pubs.PageCount
End Sub

Sub PagerButtonClick(sender As Object, e As EventArgs)
'used by external paging UI
Dim arg As String = sender.CommandArgument
Select arg
    Case "next":
    If (Pubs.CurrentPageIndex < (Pubs.PageCount - 1)) Then
        Pubs.CurrentPageIndex += 1
    End If
    Case "prev":
    If (Pubs.CurrentPageIndex > 0) Then
        Pubs.CurrentPageIndex -= 1
    End If
    Case "last":
    Pubs.CurrentPageIndex = (Pubs.PageCount - 1)
    Case Else:
    Pubs.CurrentPageIndex = Convert.ToInt32(arg)
End Select
End Sub

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 = ResultCount - (Pubs.PageSize * (Pubs.CurrentPageIndex+1))
    NextButton.Text = ("Next " & EndCount & " >")
End If
End Sub

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

<asp:Label id="lblPageCount" runat="server" /><br>
<asp:label id="RecordCount" runat="server" />
<form runat="server">
    <ASP:Datagrid id="Pubs" runat="server"
            PagerStyle-Visible = "False"
    <asp:linkbutton id="Firstbutton" Text="<< 1st Page" CommandArgument="0" runat="server" onClick="PagerButtonClick"/>
    <asp:linkbutton id="Prevbutton" Text= "" CommandArgument="prev" runat="server" onClick="PagerButtonClick"/>
    <asp:linkbutton id="Nextbutton" Text= "" CommandArgument="next" runat="server" onClick="PagerButtonClick"/>
    <asp:linkbutton id="Lastbutton" Text="Last Page >>" CommandArgument="last" runat="server" onClick="PagerButtonClick"/>
    Change Pagesize
    <asp:DropDownList id="ps" runat="server">
    <asp:ListItem selected>10</asp:ListItem>
    <asp:button text="Change Pagesize" runat="server" OnClick="RePage"/>

This article was originally published on

You might also like...


About the author

Dimitrios Markatos

Dimitrios Markatos United States

Dimitrios, or Jimmy as his friends call him, is a .NET developer/architect who specializes in Microsoft Technologies for creating high-performance and scalable data-driven enterprise Web and des...

Interested in writing for us? Find out more.


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is anticipated with distaste, performed with reluctance, and bragged about forever.” - Dan Kaminsky