Members

Technology Zones

IBM Learning Center

Articles

Hosted By

MaximumASP

Info
Rated
Read 120,766 times
5 comments

Contents

Related Categories

Custom ASP.NET Datagrid Paging With Exact Count - The Source Code

DMarko1

The Source Code

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<html><body>
<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
    BindSQL()
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
Try
    Pubs.DataSource = DS
    Pubs.Databind()
Catch e As Exception
    Pubs.CurrentPageIndex = 0
End Try
If Pubs.CurrentPageIndex <> 0 Then
    Call Prev_Buttons()
    Firstbutton.Visible = true
    Prevbutton.Visible = true
else
    Firstbutton.Visible = false
    Prevbutton.Visible = false
end if
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
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
BindSQL()
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
BindSQL()
End Sub
</script>

<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"
            PagerStyle-Visible = "False"
            HeaderStyle-BackColor="Blue"
            HeaderStyle-ForeColor="White"
    />
    <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"/>
    <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>

This article was originally published on 4guysfromrolla.com.

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 desktop applications. Till now Jimmy has authored nearly two dozen .NET articles, published on Dot Net Junkies, 4 Guys From Rolla, Sitepoint, MSDN Academic Alliance, Developers.NET, The Official Microsoft ASP.NET Site, and here on Developer Fusion, covering various unique and advanced techniques on .NET.

Comments

Read Comments | Post Comment

  • Posted by DMarko1 on 08 Jul 2005

    Hi, thanks, I appreciate your compliments.

    I have written another older article that I think should help. It's entitled- [url="http://www.sitepoint.com/article/datagrid-searching-asp-net"]Drilldown...

  • About Custom Paging

    Posted by jmawebco on 08 Jul 2005

    I loved your article on custom paging but would very much like to know how to apply it when you filter your initial data set to retreive specific records. I have a grid with 813 records sorted by user...

  • Paging with exact count and large datasets.

    Posted by mcslayton on 26 Oct 2003

    [url="http://www.seasidecon.com/arsnet/"]Check this site...[/url]

    I too was on the same quest and I've created a best practices reference site that covers many enterprise topics necessary to creat...

  • Posted by Grumly on 17 Oct 2003

    Hello,

    Tankete, I just have try what you are asking for a couple of days ago :

    The principe is simple : returning only a needed page from 50th to 60th records consists in querying this way :

    ...

  • What about large datasets?

    Posted by tankete on 16 Oct 2003

    I was hoping that you would address how to do paging with exact counts while NOT returning the entire dataset from the database, just the current page worth of data. Can you provide any information on...