1 Previous Next 

DataGrid: Paging without N Tile

In the orginal version of this article we used the Sql Server 2005 NTile function to break the data into pages.
We have since found a DataAdapter fill overload that allows you to specify the records you want to load.
This example pages data from the NorthWind access database.
Dim da As OleDbDataAdapter
Dim conn As OleDbConnection
Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim cmd As OleDbCommand
        With nuPage
            .Maximum = 10
            .Minimum = 1
            .Increment = 1
            .Value = 1
        End With

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = c:\Northwind.mdb;"

        conn = New OleDbConnection(strConn)
        cmd = New OleDbCommand("Select count(ProductName) From Products", conn)

            da = New OleDbDataAdapter("Select * from Products", conn)
            With nuPage
                .Maximum = Math.Ceiling(cmd.ExecuteScalar / 10)
                .Minimum = 1
                .Increment = 1
                .Value = 1
            End With
            da.Fill(ds, 0, 10, "Products")
            ds.Tables("Products").DefaultView.AllowNew = False
            DataGrid1.DataSource = ds.Tables("Products")
        Catch ex As Exception
        End Try

        Dim ts As New DataGridTableStyle
        ts.MappingName = "Products"
        ts.SelectionBackColor = Color.Yellow
        ts.SelectionForeColor = Color.Red
        Dim colName As New DataGridTextBoxColumn

        With colName
           .MappingName = "ProductName"
            .HeaderText = "Name"
            .Width = 200
            .ReadOnly = True
        End With

        Dim cm As CurrencyManager = CType(Me.BindingContext(DataGrid1.DataSource), CurrencyManager)
        ' Use the ListManager to get the PropertyDescriptor for the new column.
        Dim pd As PropertyDescriptor = cm.GetItemProperties()("UnitPrice")
        ' Create a new DataTimeFormat object.
        Dim colBDay As New DataGridTextBoxColumn(pd, "c")

        With colBDay
            .MappingName = "UnitPrice"
            .HeaderText = "Price"
            .Width = 50
        End With

        ts = Nothing
        colName = Nothing
End Sub

Private Sub nuPage_ValueChanged(ByVal sender As System.Object,  _
                   ByVal e As System.EventArgs) Handles nuPage.ValueChanged
        If da Is Nothing Then Return
        Dim intStart As Integer = (nuPage.Value - 1) * 10
        da.Fill(ds, intStart, 10, "Products")
End Sub

1 Previous Next