Asp.Net > Code Snippets

Custom Paging results in ASP.NET using A Better Way

Custom Paging results in ASP.NET using A Better Way

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<script language="VB" runat="server">

Public objCon As New SQLConnection("server=ASP;User id=SA;password=;database=Northwind")

Sub Page_Load(Source as Object, E as EventArgs)

dim currentPage as integer
if request.querystring("page") = "" then
currentpage = 1
else
currentpage = request.querystring("page")
end if
getRst(currentPage)
getPaging(currentPage)
end sub


public sub getRst(page as integer)

dim pageNo as integer
dim sql as string
pageNo = 10 * (page - 1)

Dim ds as DataSet=New DataSet() 

sql = "select * from Products"

Dim cmd As SQLDataAdapter = New SQLDataAdapter(sql, objCon)
cmd.Fill(ds, pageNo , 10, "results")

rst_table.DataSource = ds.Tables("results").DefaultView
rst_table.databind()
end sub

Sub getPaging(cPage as integer)
Dim cmd As SQLCommand = New SQLCommand("Select count(*) from Products", objCon)
objCon.Open()
Dim totalRecords as integer = cmd.ExecuteScalar().toString 
Dim totalPages As integer
if totalRecords MOD 10 = 0 then
totalPages = int(totalRecords / 10)
else
totalPages = int(totalRecords / 10) + 1
end if

Dim i as integer
Dim pageTxt As String = "More Pages :    "


if cpage > 1 then
pageTxt += "<a href=paging.aspx?page=" & cPage - 1 & ">Previous</a>    "
end if


for i = 1 to totalPages
if cPage = i then
pageTxt += "<b>" & i & "</b>    "
else 
pageTxt += "<a href=paging.aspx?page=" & i & ">" & i & "</a>    "
end if
next i

if cpage < totalPages then
pageTxt += "<a href=paging.aspx?page=" & cPage + 1 & ">Next</a>    "
end if


showpages.text = pageTxt
end sub


</script>
<html><body>

<asp:DataGrid runat="server" Id="rst_table" cellpadding="0" cellspacing="2" width="96%" font-size = "10pt" RepeatColumns="1" 
/>

<asp:label id="showpages" runat="server" Font-Names="Verdana" Font-Size="12px"/> 

</body></html>