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">

<title>Datagrid paging with dropdown</title>

</head>

<body>

<div>

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

AllowPaging="True" AllowCustomPaging="False"

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

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

</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"/>

<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"/>

<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 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