How To Perform Paging with the DataGrid Windows Control by Using Visual Basic .NET

Article translations Article translations
Article ID: 305271 - View products that this article applies to.
This article was previously published under Q305271
Expand all | Collapse all

On This Page

SUMMARY

The DataGrid Web control has built-in Automatic or Custom Paging functionalities; however, the DataGrid Windows control lacks these features. This article demonstrates how to build a simple paging mechanism for the DataGrid Windows control.

The code samples in this article make use of DataSet objects. In ADO.NET, DataSet objects are filled in a single operation and reside in memory all of the time. If you are working with a large DataSet, this article describes how to display the data in chunks or pages programmatically.

This technique has some limitations. Please see the Troubleshooting section for more information.

Requirements

  • Microsoft Visual Basic .NET
  • Microsoft SQL Server Northwind sample database

Steps to Add Paging to a DataGrid Windows Control

When you page a DataGrid, you display data in page-size "chunks," that is, one page of records at a time. The sample code to follow copies the DataRow objects for each page from the DataSet in memory to a temporary table. The temporary table is then bound to the DataGrid control.
  1. Open a new Visual Basic .NET Windows Application. Form1 is created by default.
  2. Add DataGrid control, and set its ReadOnly property to True.
  3. Place the following additional controls on Form1, and set their properties as shown below:
    Collapse this tableExpand this table
    ControlName PropertyText Property
    ButtonbtnFirstPageFirst Page
    ButtonbtnNextPageNext Page
    TextBoxtxtDisplayPageNo
    ButtonbtnPreviousPagePrevious Page
    ButtonbtnLastPageLast Page
    TextBoxtxtPageSize5
    ButtonbtnFillGridFill Grid

  4. Copy and paste the following code into Form1's General Declaration section:
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    					
  5. Copy and paste the following code before the "Windows Form Designer generated code" region to declare form-level variables for Form1:
        Private da As SqlDataAdapter
        Private ds As DataSet
        Private dtSource As DataTable
        Private PageCount As Integer
        Private maxRec As Integer
        Private pageSize As Integer
        Private currentPage As Integer
        Private recNo As Integer
    					
  6. Delete the following auto-generated code for Load event of Form1.
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
      Handles MyBase.Load
    
    End Sub
    					
  7. Copy and paste the following code after the "Windows Form Designer generated code" region:
    Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
    
        'Open Connection.
        Dim conn As SqlConnection = New SqlConnection( _ 
    "Server=(local)\netsdk;uid=sa;pwd=;database=northwind")
    
        'Set the DataAdapter's query.
        da = New SqlDataAdapter("select * from customers", conn)
        ds = New DataSet()
    
        ' Fill the DataSet.
        da.Fill(ds, "customers")
    
        ' Set the source table.
        dtSource = ds.Tables("customers")
    
    End Sub
    
    Private Sub btnNextPage_Click(ByVal sender As System.Object, _ 
        ByVal e As System.EventArgs) Handles btnNextPage.Click
    
        'If the user did not click the "Fill Grid" button then Return
        If Not CheckFillButton() Then Return
    
        'Check if the user clicked the "Fill Grid" button.
        If pageSize = 0 Then
            MessageBox.Show("Set the Page Size, and then click the ""Fill Grid"" button!")
            Return
        End If
    
        currentPage = currentPage + 1
    
        If currentPage > PageCount Then
            currentPage = PageCount
    
            'Check if you are already at the last page.
            If recNo = maxRec Then
                MessageBox.Show("You are at the Last Page!")
                Return
            End If
        End If
    
        LoadPage()
    End Sub
    
    Private Sub btnPreviousPage_Click(ByVal sender As System.Object, _ 
        ByVal e As System.EventArgs) Handles btnPreviousPage.Click
    
        If Not CheckFillButton() Then Return
    
        If currentPage = PageCount Then
            recNo = pageSize * (currentPage - 2)
        End If
    
        currentPage = currentPage - 1
    
        'Check if you are already at the first page.
        If currentPage < 1 Then
            MessageBox.Show("You are at the First Page!")
            currentPage = 1
            Return
        Else
            recNo = pageSize * (currentPage - 1)
        End If
    
        LoadPage()
    End Sub
    
    Private Sub LoadPage()
        Dim i As Integer
        Dim startRec As Integer
        Dim endRec As Integer
        Dim dtTemp As DataTable
        Dim dr As DataRow
    
        'Duplicate or clone the source table to create the temporary table.
        dtTemp = dtSource.Clone
    
        If currentPage = PageCount Then
            endRec = maxRec
        Else
            endRec = pageSize * currentPage
        End If
    
        startRec = recNo
    
        'Copy the rows from the source table to fill the temporary table.
        For i = startRec To endRec - 1
            dtTemp.ImportRow(dtSource.Rows(i))
            recNo = recNo + 1
        Next
    
        DataGrid1.DataSource = dtTemp
        DisplayPageInfo()
    
    End Sub
    
    Private Sub btnFirstPage_Click(ByVal sender As System.Object, _ 
        ByVal e As System.EventArgs) Handles btnFirstPage.Click
    
        If Not CheckFillButton() Then Return
    
        ' Check if you are already at the first page.
        If currentPage = 1 Then
            MessageBox.Show("You are at the First Page!")
            Return
        End If
    
        currentPage = 1
        recNo = 0
    
        LoadPage()
    
    End Sub
    
    Private Sub btnLastPage_Click(ByVal sender As System.Object, _ 
        ByVal e As System.EventArgs) Handles btnLastPage.Click
    
        If Not CheckFillButton() Then Return
    
        ' Check if you are already at the last page.
        If recNo = maxRec Then
            MessageBox.Show("You are at the Last Page!")
            Return
        End If
    
        currentPage = PageCount
    
        recNo = pageSize * (currentPage - 1)
    
        LoadPage()
    
    End Sub
    
    Private Sub DisplayPageInfo()
        txtDisplayPageNo.Text = "Page " & currentPage.ToString & "/ " & PageCount.ToString
    End Sub
    
    Private Sub btnFillGrid_Click(ByVal sender As System.Object, _ 
        ByVal e As System.EventArgs) Handles btnFillGrid.Click
    
        'Set the start and max records. 
        pageSize = txtPageSize.Text
        maxRec = dtSource.Rows.Count
        PageCount = maxRec \ pageSize
    
        ' Adjust the page number if the last page contains a partial page.
        If (maxRec Mod pageSize) > 0 Then
            PageCount = PageCount + 1
        End If
    
        'Initial seeings
        currentPage = 1
        recNo = 0
    
        ' Display the content of the current page.
        LoadPage()
    
    End Sub
    
    Private Function CheckFillButton() As Boolean
    
        'Check if the user clicks the "Fill Grid" button.
        If pageSize = 0 Then
            MessageBox.Show("Set the Page Size, and then click the ""Fill Grid"" button!")
            CheckFillButton = False
        Else
            CheckFillButton = True
        End If
    End Function
    					
  8. Modify the ConnectionString parameter in the code so that it points to an existing instance of the Northwind database.
  9. Press the F5 key to build and run the project.
  10. By default, the Page Size is set to 5 records, so you can change it in the text box.
  11. Click Fill Grid. Notice that the Grid is filled with 5 records.
  12. Click First Page, Next Page, Previous Page, and Last Page to browse between pages.

Troubleshooting

  • This technique only works for read-only DataGrid controls. When you import a row to a temporary DataTable object, you make a copy. Thus, changes that you make will not be saved to the main table.
  • This technique does not work (and neither does a collection or an array) if you want the user to be able to navigate to child records through a DataRelation object or if you have records that are linked in a parent-child relation that appear on the form at the same time.

REFERENCES

For more information about ADO.NET, see the following MSDN Web site:
Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx
For more information, see the Microsoft .NET Framework SDK documentation:
.NET Framework SDK
http://msdn2.microsoft.com/en-us/library/aa719465(VS.71).aspx

Properties

Article ID: 305271 - Last Review: May 13, 2007 - Revision: 4.2
APPLIES TO
  • Microsoft ADO.NET 1.0
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbdatabinding kbhowtomaster kbwindowsforms KB305271

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com