Custom Paging in DataList Control Using VB.NET

In this article we will learn how to do paging in a DataList control.

Program

Create an employee table, as in:

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

Table-in-SQL-Server.gif

Datalistpaging.aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="datalistpaging.aspx.vb"Inherits="datalistpaging" %>
<!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>Datalist Paging</title>
    <style type="text/css">
    .wdt
    {
      width:10%;
    }
    </style>
</head>
<
body>
    <form id="form1" runat="server">
    <div>
<a name="this"></a>
<asp:DataList ID="datalist_paging" Runat="server">
 <HeaderTemplate>
<
table>
<
tr>
<
th><font color="red">Employee ID</font></th>
<th><font color="red">Employee Name</font></th>
<th><font color="red">Employee Salary</font></th>
<th><font color="red">Employee Address</font></th>
<th><font color="red">Employee Degisnation</font></th>
</tr>
</
HeaderTemplate>
<
ItemTemplate>
<
tr>
<
td><%#Eval("empid")%></td>
<td><%#Eval("empname")%></td>
<td><%#Eval("empsal")%></td>
<td><%#Eval("empadd")%></td>
<td><%#Eval("empdes")%></td>
</tr>
</
ItemTemplate>
 <HeaderStyle BackColor="#FFCC66" />
<FooterTemplate>
</
table>
</
FooterTemplate>
</
asp:DataList>
<
table>
<
tr>
<
td>
<
asp:label ID="lblshow" Runat="server" Font-Bold="False" />
</td>
<
td class="wdt">
<a href="#this"
ID="Firstrecord"
onserverclick="First_record"
runat="server"><b><<</b></a>
</td>
<
td class="wdt">
<a href="#this"
ID="Previousrecord"
onserverclick="Previous_record"
runat="server"><b><</b></a>
</td>
<
td class="wdt">
<a href="#this"
ID="Nextrecord"
onserverclick="Next_record"
runat="server"><b>></b></a>
</td>
<
td class="wdt">
<a href="#this"
ID="Lastrecord"
onserverclick="Last_record"
runat="server"><b>>></b></a>
</td>
</
tr>
</
table>
<
asp:label ID="start_page" Visible="False" Runat="server" />
<asp:label ID="page_length" Visible="False" Runat="server" />
<asp:label ID="count_data" Visible="False" Runat="server" />
    </div>
    </form>
</body>
</html>

Datalistpaging.aspx.vb code

Imports System.Data
Imports System.Data.SqlClient
Partial Class datalistpaging
    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
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) HandlesMe.Load
        If Not Page.IsPostBack() Then
            page_length.Text = "5"
            start_page.Text = "0"
            bindlist()
        End If
    End Sub
    Public Sub First_record(ByVal s As Object, ByVal e As EventArgs)
        start_page.Text = "0"
        bindlist()
    End Sub
    Public Sub Previous_record(ByVal s As Object, ByVal e As EventArgs)
        start_page.Text = CStr(CInt(start_page.Text) - CInt(page_length.Text))
        If CInt(start_page.Text) < 0 Then
            start_page.Text = "0"
        End If
        bindlist()
    End Sub
 
    Public Sub Next_record(ByVal s As Object, ByVal e As EventArgs)
        If CInt(start_page.Text) + 1 < CInt(count_data.Text) Then
            start_page.Text = CStr(CInt(start_page.Text) + CInt(page_length.Text))
        End If
        bindlist()
    End Sub
     Public Sub Last_record(ByVal s As Object, ByVal e As EventArgs)
       Dim i As Integer
 
        i = CInt(count_data.Text) Mod CInt(page_length.Text)
        If i > 0 Then
            start_page.Text = CStr(CInt(count_data.Text) - i)
        Else
            start_page.Text = CStr(CInt(count_data.Text) - CInt(page_length.Text))
        End If
        bindlist()
    End Sub
 
    Sub bindlist()
        con.Open()
        str = "select * from employee"
        sqlda = New SqlDataAdapter(str, con)
        ds = New DataSet()
 
        If Not Page.IsPostBack() Then
            sqlda.Fill(ds)
            count_data.Text = CStr(ds.Tables(0).Rows.Count)
        End If
        sqlda.Fill(ds, CInt(start_page.Text), CInt(page_length.Text), "employee")
        datalist_paging.DataSource = ds
        datalist_paging.DataMember = "employee"
        datalist_paging.DataBind()
        con.Close()
        navigate()
    End Sub
    Private Sub navigate()
        lblshow.ForeColor = Drawing.Color.Tomato
        lblshow.BackColor = Drawing.Color.Yellow
        lblshow.Text = "Total Records:<b>" & count_data.Text
        lblshow.Text += "</b> - Showing Page:<b> "
        lblshow.Text += CStr(CInt(CInt(start_page.Text) / CInt(page_length.Text) + 1))
        lblshow.Text += "</b> of <b>"
 
        If (CInt(count_data.Text) Mod CInt(page_length.Text)) > 0 Then
            lblshow.Text += CStr(CInt(CInt(count_data.Text) / CInt(page_length.Text) + 1))
        Else
            lblshow.Text += CStr(CInt(count_data.Text) / CInt(page_length.Text))
        End If
        lblshow.Text += "</b>"
    End Sub
End Class

Output

paging-in-DataList-in-VB.net.gif

Next Recommended Readings