This step-by-step article provides guidelines about how to implement paging. This article provides a few examples of paging and describes the advantage of each method.
Methods to Implement Paging
There are many approaches to paging. Paging is typically an architectural issue. Paging largely depends on your database design and how many records you have to page. You can use either of the following methods to implement paging:
- Use the intrinsic paging that is available in the DataGrid control. This method of paging is easy to use and works well when you have a small number of records to page. However, performance decreases when the number of records increase. In Microsoft Visual Basic .NET, there is no intrinsic paging.For additional information about how to implement paging, click the article number below
to view the article in the Microsoft Knowledge Base:
HOW TO: Custom Page a DataGrid Windows Control by Using Visual Basic .NET
- To speed up performance, design queries or stored procedures to retrieve the number of records that you want. The Custom Paging Sample section demonstrates how to implement custom paging.
You can also use the TOP and the WHERE clauses in your SQL statement to design queries or stored procedures to retrieve the number of records that you want. For more information about how to use TOP and WHERE to implement custom paging, visit the following Microsoft Developer Network (MSDN) Web site:
Custom Paging Sample
This sample demonstrates how to use a DataGrid
control to implement custom paging through ASP.NET. When you use this method, you can retrieve the records that you want in any page order. As a result, this method is highly efficient and versatile.
This sample requires that the database include tables that have an index. Although this sample uses Microsoft SQL Server and the SQL Server Northwind
database, you can adapt this sample to use with any database.
- Follow these steps to create a new Visual Basic ASP.NET Web Application:
- Start Microsoft Visual Studio .NET.
- On the File menu, point to New, and then click Project.
- In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
- Drag a DataGrid control from the toolbox to WebForm1.aspx.
- Right-click DataGrid1, and then click Property Builder.
- Follow these steps in the Property Builder dialog box:
- Click Paging.
- Click to select the AllowPaging and the AllowCustomPaging check boxes.
- Set the PageSize property to 3.
- In the Mode list, click Page Numbers.
- Click Apply, and then click OK.
- Click HTML at the bottom of WebForm1.aspx to open the HTML source view. Replace the <asp:DataGrid> tag that is added by default with the following code:
<asp:DataGrid id="Datagrid1" runat="server" AllowCustomPaging="True" PageSize="3" AllowPaging="True" PagerStyle-Mode="NumericPages" OnPageIndexChanged="DataGrid1_PageIndexChanged">
- Double-click in an empty area of the WebForm1.aspx page to open the Code window, and then add the following namespaces to the top of the page:
- Add the following declaration under Public Class Webform1:
Dim cn As SqlConnection
Dim StartIndex As Integer
Dim EndIndex As Integer
- Add the following code in the Page_Load event of the form:
Dim myCommand As SqlCommand
cn = New SqlConnection("server = localhost; uid=userid; pwd=password; database=Northwind ")
If Not IsPostBack Then
myCommand = New SqlCommand()
myCommand.CommandText = "Select Count(*) from Products"
myCommand.Connection = cn
DataGrid1.VirtualItemCount = myCommand.ExecuteScalar()
- Add the following code after the Page_Load subroutine:
Dim myAdapter As SqlDataAdapter
Dim DS As DataSet
EndIndex = StartIndex + DataGrid1.PageSize
myAdapter = New SqlDataAdapter("Select * From Products Where ProductID > @startIndex And ProductID <= @endIndex Order by ProductID", cn)
DS = New DataSet()
DataGrid1.DataSource = DS
Public Sub datagrid1_PageIndexChanged(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)
StartIndex = (e.NewPageIndex * DataGrid1.PageSize)
DataGrid1.CurrentPageIndex = e.NewPageIndex
- Press F5 to run the application.
- To test the project, select any page number that is listed below the grid.
Article ID: 318131 - Last Review: October 26, 2013 - Revision: 3.0
|kbnosurvey kbarchive kbhowtomaster kbsqlclient kbsystemdata KB318131|