Datagrid paging with dropdown

In this blog we will know about how to do a custom paging containing a dropdown list pager with the help of a Datagrid control.

 

Features to do

1.Here we will have four link buttons to navigate the records as first, previous, next and last. Tooltip is added to each link buttons.

2.We have a label control where we can know how many records are present in the database.

3.We have a Dropdown List where its list item is bound with some numbers. So when we choose any number from the dropdown list that number of records will be display in the datagrid.

4.While navigating the records we have a page count, which is displayed in the label control. Here we can know what page of results we are currently viewing.

 

In .net framework 3.5 no datagrid is available in the toolbox. So we have to make it available in the toolbox. To do that right click on the toolbox then click choose items, then check all the checkbox present near datagrid and click ok. You will notice that datagrid control is added to the toolbox.

 

Table structure

 

Create table employee(empid int primary key,empname varchar(50),empsal int,empadd varchar(50),empdes varchar(50))

 

 

Default.aspx code

 

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Datagrid paging with dropdown</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

 

    <asp:Datagrid id="dg1" runat="server"  AutoGenerateColumns="true"

     AllowPaging="True" AllowCustomPaging="False"

     PagerStyle-Visible = "False" HeaderStyle-BackColor="Red"

     HeaderStyle-ForeColor="White" BackColor="#FFCC66" >

    

<PagerStyle Visible="False"></PagerStyle>

 

        <AlternatingItemStyle BackColor="#FFFFCC" />

 

<HeaderStyle BackColor="Red" ForeColor="White"></HeaderStyle>

        </asp:Datagrid>

    

     <asp:linkbutton id="Firstbutton" Text="<< 1st Page" CommandArgument="0"

            runat="server" onClick="NavigateButtonClick" ForeColor="#333300" ToolTip="First"/>

     <asp:linkbutton id="Prevbutton" Text= "" CommandArgument="prev" runat="server"

            onClick="NavigateButtonClick" ForeColor="#333300" ToolTip="Previous"/>  &nbsp;&nbsp;

    

      <asp:linkbutton id="Nextbutton" Text= "" CommandArgument="next" runat="server"

            onClick="NavigateButtonClick" ForeColor="#333300" ToolTip="Next"/>

        <asp:linkbutton id="Lastbutton" Text="Last Page >>" CommandArgument="last"

            runat="server" onClick="NavigateButtonClick" ForeColor="#333300" ToolTip="Last"/>

      <br /><br />

    

     <asp:Label id="PageCount" runat="server" /><br />

     <asp:label id="TotalRecords" runat="server" /> <br />           <br /><br/><br />

     

      Change Pagesize <asp:DropDownList id="d1" runat="server" ForeColor="Maroon">

      <asp:ListItem>1</asp:ListItem> 

      <asp:ListItem>2</asp:ListItem> 

      <asp:ListItem>3</asp:ListItem> 

      <asp:ListItem  Selected="True">4</asp:ListItem> 

      <asp:ListItem>5</asp:ListItem> 

      <asp:ListItem>6</asp:ListItem>

      <asp:ListItem>7</asp:ListItem> 

      <asp:ListItem>8</asp:ListItem> 

      <asp:ListItem>9</asp:ListItem> 

      </asp:DropDownList> 

      <asp:button ID="showpage" text="Choose Pagesize" runat="server"/>

    </div>

    </form>

</body>

</html>

 

Default.aspx.vb code

 

Imports System.Data

Imports System.Data.SqlClient

Partial Class _Default

    Inherits System.Web.UI.Page

    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()

    Dim con As New SqlConnection(strConnString)

    Dim str As String

    Dim com As SqlCommand

    Dim sqlda As SqlDataAdapter

    Dim ds As DataSet

    Dim r1 As Integer

    Dim r2 As Integer

 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then

            Bindgrid()

        End If

    End Sub

 

    Sub Bindgrid()

        con.Open()

        str = ("SELECT * from employee")

        com = New SqlCommand(str, con)

        sqlda = New SqlDataAdapter(com)

        ds = New DataSet

        sqlda.Fill(ds, "employee")

        r2 = ds.Tables("employee").Rows.Count.ToString()

        dg1.PageSize = Convert.ToInt32(d1.SelectedItem.Value)

 

        If Not Page.IsPostBack Then

            dg1.CurrentPageIndex = 0

        End If

        r1 = r2

        TotalRecords.Text = "Total" & " " & " " & " " & "<b><font color=red>" & r2 & "</font> records found"

        Try

            dg1.DataSource = ds

            dg1.DataBind()

        Catch e As Exception

            dg1.CurrentPageIndex = 0

        End Try

 

        If dg1.CurrentPageIndex <> 0 Then

 

            Call Prev_Buttons()

            Firstbutton.Visible = True

            Prevbutton.Visible = True

        Else

            Firstbutton.Visible = False

            Prevbutton.Visible = False

        End If

 

        If dg1.CurrentPageIndex <> (dg1.PageCount - 1) Then

            Call Next_Buttons()

            Nextbutton.Visible = True

            Lastbutton.Visible = True

        Else

            Nextbutton.Visible = False

            Lastbutton.Visible = False

        End If

        PageCount.Text = "Page " & "<b><font color=red>" & dg1.CurrentPageIndex + 1 & "</font>" & "of " & "<b><font color=red>" & dg1.PageCount & "</font>"

        con.Close()

    End Sub

 

    Sub NavigateButtonClick(ByVal sender As Object, ByVal e As EventArgs)

 

        Dim flag As String = sender.CommandArgument

        Select Case flag

            Case "next"

                If (dg1.CurrentPageIndex < (dg1.PageCount - 1)) Then

                    dg1.CurrentPageIndex += 1

                End If

            Case "prev"

                If (dg1.CurrentPageIndex > 0) Then

                    dg1.CurrentPageIndex -= 1

                End If

            Case "last"

                dg1.CurrentPageIndex = (dg1.PageCount - 1)

            Case Else

                dg1.CurrentPageIndex = Convert.ToInt32(flag)

        End Select

        Bindgrid()

    End Sub

 

    Sub Prev_Buttons()

        Dim Prevflag As String

 

        If dg1.CurrentPageIndex + 1 <> 1 And r1 <> -1 Then

            Prevflag = dg1.PageSize

            Prevbutton.Text = ("< Prev " & Prevflag)

            If dg1.CurrentPageIndex + 1 = dg1.PageCount Then

                Firstbutton.Text = ("<< 1st Page")

            End If

        End If

    End Sub

 

    Sub Next_Buttons()

        Dim Nextflag As String

 

        If dg1.CurrentPageIndex + 1 < dg1.PageCount Then

            Nextflag = dg1.PageSize

            Nextbutton.Text = ("Next " & Nextflag & " >")

        End If

 

        If dg1.CurrentPageIndex + 1 = dg1.PageCount - 1 Then

            Dim Endflag As Integer = r1 - (dg1.PageSize * (dg1.CurrentPageIndex + 1))

            Nextbutton.Text = ("Next " & Endflag & " >")

        End If

    End Sub

 

    Protected Sub showpage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles showpage.Click

        dg1.CurrentPageIndex = 0

        Bindgrid()

    End Sub

End Class

 


 

Thanks for reading