McBasic™ 0.7.881 · Build 881 · 13 min read

Hands-on tutorial · Data & services

Page records and distinguish two kinds of search

Combine provider paging with a local filter and a parameterized database search.

1 Form / control designs1 complete BASIC filesGuided sample project

Download complete project Complete event code

FilterText operates on loaded rows. A database search changes the SQL query and may match rows that are not loaded. QueryPageAsync uses a stable order, Name,ID, and a page size of five. Reset to page zero when the database search changes; do not mistake the visible row count for the total database size.

Step 1Prepare a safe practice project#

Extract the archive into a new writable folder, separate from your own application. Open DataGridPaging.mcproj in McBasic™. Keep every declared source, layout, resource and migration file together.

To reconstruct it yourself, create a DesktopApp project and add the files declared below. In the complete project, follow these steps as a guided rebuild: replace each named source file once and Check after all its procedures are present.

Exact project configuration

DataGridPaging.mcproj

{
  "schemaVersion": 1,
  "projectId": "2dfc93c6-21e4-4ee4-a797-5d599fa346fb",
  "name": "DataGridPagingSample",
  "kind": "DesktopApp",
  "language": {
    "dialect": "McBasic1",
    "requireExplicit": true,
    "compare": "Binary",
    "defaultArrayBase": 0
  },
  "application": {
    "bundleIdentifier": "com.mcbasic.samples.datagridpaging",
    "version": "0.1.0",
    "buildNumber": "1",
    "startupFormId": "form-main",
    "shutdownMode": "OnStartupFormClosed"
  },
  "forms": [
    {
      "id": "form-main",
      "typeName": "MainForm",
      "layout": "Forms/MainForm.form.json",
      "code": "Forms/MainForm.bas"
    }
  ],
  "modules": [],
  "classes": [],
  "tests": [],
  "references": [],
  "resources": [],
  "targets": [
    "osx-arm64"
  ],
  "capabilities": []
}

Step 2Build the Form and set its properties#

MainForm

Select the Form first and set its properties. Add containers before their children. These are initial Designer values, not statements to paste into a procedure.

Form property	Value
`Caption`	`"DataGrid paging — McBasic"`
`Width`	`820`
`Height`	`520`
`MinWidth`	`700`
`MinHeight`	`500`

Type	Name	Parent
Label	`lblTitle`	Form
Label	`lblStatus`	Form
CommandButton	`cmdQuit`	Form
DataGrid	`grdProducts`	Form
Label	`lblFilter`	Form
TextBox	`txtFilter`	Form
CommandButton	`cmdPrevious`	Form
CommandButton	`cmdNext`	Form
Label	`lblServerFilter`	Form
TextBox	`txtServerFilter`	Form
CommandButton	`cmdSearchAll`	Form
CommandButton	`cmdClearSearch`	Form
Label	`lblSelection`	Form

lblTitle · exact Inspector values

Property	Value
`Left`	`24`
`Top`	`20`
`Width`	`500`
`Height`	`32`
`Caption`	`"Products"`
`TabIndex`	`0`
Anchor edges	Left, Top

lblStatus · exact Inspector values

Property	Value
`Left`	`24`
`Top`	`54`
`Width`	`440`
`Height`	`30`
`Caption`	`"Starting..."`
`TabIndex`	`1`
Anchor edges	Left, Top

cmdQuit · exact Inspector values

Property	Value
`Left`	`680`
`Top`	`20`
`Width`	`110`
`Height`	`36`
`Caption`	`"Quit"`
`TabIndex`	`2`
Anchor edges	Right, Top

grdProducts · exact Inspector values

Property	Value
`Left`	`24`
`Top`	`184`
`Width`	`766`
`Height`	`292`
`TabIndex`	`3`
`IsReadOnly`	`True`
`CanUserSortColumns`	`True`
`CanUserResizeColumns`	`True`
`HeadersVisible`	`True`
`KeyColumn`	`"ID"`
`Columns`	`"Name,Stock,Price"`
`FilterColumn`	`"Name"`
Anchor edges	Left, Top, Right, Bottom

lblFilter · exact Inspector values

Property	Value
`Left`	`24`
`Top`	`98`
`Width`	`75`
`Height`	`32`
`Caption`	`"This page"`
`TabIndex`	`4`
Anchor edges	Left, Top

txtFilter · exact Inspector values

Property	Value
`Left`	`105`
`Top`	`94`
`Width`	`235`
`Height`	`36`
`Text`	`""`
`TabIndex`	`5`
Anchor edges	Left, Top

cmdPrevious · exact Inspector values

Property	Value
`Left`	`480`
`Top`	`94`
`Width`	`120`
`Height`	`36`
`Caption`	`"Previous"`
`TabIndex`	`6`
Anchor edges	Right, Top

cmdNext · exact Inspector values

Property	Value
`Left`	`612`
`Top`	`94`
`Width`	`120`
`Height`	`36`
`Caption`	`"Next"`
`TabIndex`	`7`
Anchor edges	Right, Top

lblServerFilter · exact Inspector values

Property	Value
`Left`	`24`
`Top`	`138`
`Width`	`75`
`Height`	`32`
`Caption`	`"All rows"`
`TabIndex`	`8`
Anchor edges	Left, Top

txtServerFilter · exact Inspector values

Property	Value
`Left`	`105`
`Top`	`134`
`Width`	`225`
`Height`	`36`
`Text`	`""`
`TabIndex`	`9`
Anchor edges	Left, Top

cmdSearchAll · exact Inspector values

Property	Value
`Left`	`340`
`Top`	`134`
`Width`	`120`
`Height`	`36`
`Caption`	`"Search all"`
`TabIndex`	`10`
Anchor edges	Left, Top

cmdClearSearch · exact Inspector values

Property	Value
`Left`	`470`
`Top`	`134`
`Width`	`120`
`Height`	`36`
`Caption`	`"Clear search"`
`TabIndex`	`11`
Anchor edges	Left, Top

lblSelection · exact Inspector values

Property	Value
`Left`	`600`
`Top`	`54`
`Width`	`190`
`Height`	`30`
`Caption`	`"No selection"`
`TabIndex`	`12`
Anchor edges	Right, Top

Step 3Connect the events explicitly#

MainForm

Object	Event	Procedure	Parameters
`cmdQuit`	`Click`	`cmdQuit_Click`	`Click()`
`grdProducts`	`SelectionChanged`	`grdProducts_SelectionChanged`	`SelectionChanged()`
`txtFilter`	`Change`	`txtFilter_Change`	`Change()`
`cmdPrevious`	`Click`	`cmdPrevious_Click`	`Click()`
`cmdNext`	`Click`	`cmdNext_Click`	`Click()`
`cmdSearchAll`	`Click`	`cmdSearchAll_Click`	`Click()`
`cmdClearSearch`	`Click`	`cmdClearSearch_Click`	`Click()`
Form	`Shown`	`Form_Shown`	`()`

Step 4Enter the complete behavior#

Open the named source file. Enter declarations first, then every procedure. Expand a procedure for its full body, or copy the complete file. Helpers are required: do not stop after entering the first Click handler.

`Forms/MainForm.bas`

Enter these declarations at file scope, before the procedures.

Forms/MainForm.bas · declarations

Option Explicit

Private mDb As Database
Private mRows As BindingSource
Private mPageIndex As Long
Private mDatabaseSearch As String

Private Async Sub Form_Shown()

Initialize state before enabling dependent actions. Keep the shared declarations and startup dependencies with this procedure.

Forms/MainForm.bas · Form_Shown

Private Async Sub Form_Shown()
    Dim migrations As MigrationSet
    Dim command As DbCommand
    Dim ignored As Variant

    lblStatus.Caption = "Opening SQLite database..."
    cmdPrevious.Enabled = False
    cmdNext.Enabled = False
    cmdSearchAll.Enabled = False
    cmdClearSearch.Enabled = False
    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("datagrid-paging.db"))
        Set migrations = Sqlite.CreateMigrationSet()
        migrations.Add 1, "CREATE TABLE IF NOT EXISTS Products (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Stock INTEGER NOT NULL, Price REAL NOT NULL)"
        ignored = Await mDb.ApplyMigrationsAsync(migrations)
        Set command = mDb.CreateCommand("INSERT OR IGNORE INTO Products (ID, Name, Stock, Price) VALUES (1, 'Apple', 42, 1.25), (2, 'Apricot', 18, 2.10), (3, 'Banana', 73, 0.85), (4, 'Blueberry', 31, 3.40), (5, 'Cherry', 26, 4.15), (6, 'Grape', 64, 2.75), (7, 'Kiwi', 14, 1.90), (8, 'Mango', 22, 2.45), (9, 'Orange', 57, 1.15), (10, 'Pear', 39, 1.60), (11, 'Plum', 28, 2.05), (12, 'Raspberry', 16, 3.85)")
        ignored = Await command.ExecuteAsync()
        ignored = Await LoadPage(0)
    Catch failure As McException
        grdProducts.Loading = False
        grdProducts.ErrorText = failure.Message
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
    End Try
End Sub

Private Async Function LoadPage(ByVal requestedIndex As Long) As Boolean

Follow the success, failure and cleanup paths together. Do not omit a guard or Finally block when entering this procedure.

Forms/MainForm.bas · LoadPage

Private Async Function LoadPage(ByVal requestedIndex As Long) As Boolean
    Dim command As DbCommand
    Dim result As Recordset

    grdProducts.Loading = True
    grdProducts.ErrorText = ""
    cmdPrevious.Enabled = False
    cmdNext.Enabled = False
    cmdSearchAll.Enabled = False
    cmdClearSearch.Enabled = False
    Try
        Set command = mDb.CreateCommand("SELECT ID, Name, Stock, Price FROM Products WHERE instr(lower(Name), lower(@needle)) > 0")
        command.AddParameter "@needle", mDatabaseSearch
        Set result = Await command.QueryPageAsync("Name,ID", requestedIndex, 5)
        Set mRows = result.Bind()
        Set grdProducts.DataSource = mRows
        mPageIndex = requestedIndex
        grdProducts.FilterText = txtFilter.Text
        UpdateSelectionStatus
        cmdPrevious.Enabled = mPageIndex > 0
        cmdNext.Enabled = mRows.HasMore
        cmdSearchAll.Enabled = True
        cmdClearSearch.Enabled = True
        grdProducts.Loading = False
        UpdatePageStatus
        LoadPage = True
    Catch failure As McException
        grdProducts.Loading = False
        grdProducts.ErrorText = failure.Message
        cmdSearchAll.Enabled = True
        cmdClearSearch.Enabled = True
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
        LoadPage = False
    End Try
End Function

Private Sub UpdatePageStatus()

Enter this helper in the named source file, outside every other procedure. Its callers are included in the same project.

Forms/MainForm.bas · UpdatePageStatus

Private Sub UpdatePageStatus()
    If mDatabaseSearch = "" Then
        lblStatus.Caption = "Database: all rows | provider page " & CStr(mPageIndex + 1)
    Else
        lblStatus.Caption = "Database search: " & mDatabaseSearch & " | provider page " & CStr(mPageIndex + 1)
    End If
    If txtFilter.Text <> "" Then
        lblStatus.Caption = lblStatus.Caption & " | local " & CStr(grdProducts.VisibleRowCount) & "/" & CStr(grdProducts.LoadedRowCount)
    Else
        lblStatus.Caption = lblStatus.Caption & " | " & CStr(grdProducts.LoadedRowCount) & " loaded"
    End If
End Sub

Private Async Sub cmdPrevious_Click()

Enter the complete action, including its input checks and calls to helper procedures.

Forms/MainForm.bas · cmdPrevious_Click

Private Async Sub cmdPrevious_Click()
    Dim loaded As Boolean
    If mPageIndex <= 0 Then Exit Sub
    loaded = Await LoadPage(mPageIndex - 1)
End Sub

Private Async Sub cmdNext_Click()

Enter the complete action, including its input checks and calls to helper procedures.

Forms/MainForm.bas · cmdNext_Click

Private Async Sub cmdNext_Click()
    Dim loaded As Boolean
    If mRows Is Nothing Then Exit Sub
    If Not mRows.HasMore Then Exit Sub
    loaded = Await LoadPage(mPageIndex + 1)
End Sub

Private Sub txtFilter_Change()

Read the current value or selection and update its dependent interface. The binding above determines which object calls this handler.

Forms/MainForm.bas · txtFilter_Change

Private Sub txtFilter_Change()
    grdProducts.FilterText = txtFilter.Text
    If mRows Is Nothing Then Exit Sub
    UpdatePageStatus
End Sub

Private Async Sub cmdSearchAll_Click()

Enter the complete action, including its input checks and calls to helper procedures.

Forms/MainForm.bas · cmdSearchAll_Click

Private Async Sub cmdSearchAll_Click()
    Dim loaded As Boolean
    Dim previousSearch As String
    If mDb Is Nothing Then Exit Sub
    previousSearch = mDatabaseSearch
    mDatabaseSearch = txtServerFilter.Text
    loaded = Await LoadPage(0)
    If Not loaded Then mDatabaseSearch = previousSearch
End Sub

Private Async Sub cmdClearSearch_Click()

Enter the complete action, including its input checks and calls to helper procedures.

Forms/MainForm.bas · cmdClearSearch_Click

Private Async Sub cmdClearSearch_Click()
    Dim loaded As Boolean
    Dim previousSearch As String
    If mDb Is Nothing Then Exit Sub
    previousSearch = mDatabaseSearch
    mDatabaseSearch = ""
    txtServerFilter.Text = ""
    loaded = Await LoadPage(0)
    If Not loaded Then mDatabaseSearch = previousSearch
End Sub

Private Sub grdProducts_SelectionChanged()

Read the current value or selection and update its dependent interface. The binding above determines which object calls this handler.

Forms/MainForm.bas · grdProducts_SelectionChanged

Private Sub grdProducts_SelectionChanged()
    UpdateSelectionStatus
End Sub

Private Sub UpdateSelectionStatus()

Enter this helper in the named source file, outside every other procedure. Its callers are included in the same project.

Forms/MainForm.bas · UpdateSelectionStatus

Private Sub UpdateSelectionStatus()
    Dim row As Long
    If mRows Is Nothing Then
        lblSelection.Caption = "No selection"
        Exit Sub
    End If
    row = CInt(grdProducts.SelectedIndex)
    If row < 0 Or row >= mRows.RowCount Then
        lblSelection.Caption = "No selection"
        Exit Sub
    End If
    lblSelection.Caption = "Selected: " & CStr(mRows.Value(row, "Name"))
End Sub

Private Sub cmdQuit_Click()

Enter the complete action, including its input checks and calls to helper procedures.

Forms/MainForm.bas · cmdQuit_Click

Private Sub cmdQuit_Click()
    App.Quit
End Sub

Copy the complete Forms/MainForm.bas file

Replace the file once; do not append duplicate procedures.

Forms/MainForm.bas

Option Explicit

Private mDb As Database
Private mRows As BindingSource
Private mPageIndex As Long
Private mDatabaseSearch As String

Private Async Sub Form_Shown()
    Dim migrations As MigrationSet
    Dim command As DbCommand
    Dim ignored As Variant

    lblStatus.Caption = "Opening SQLite database..."
    cmdPrevious.Enabled = False
    cmdNext.Enabled = False
    cmdSearchAll.Enabled = False
    cmdClearSearch.Enabled = False
    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("datagrid-paging.db"))
        Set migrations = Sqlite.CreateMigrationSet()
        migrations.Add 1, "CREATE TABLE IF NOT EXISTS Products (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Stock INTEGER NOT NULL, Price REAL NOT NULL)"
        ignored = Await mDb.ApplyMigrationsAsync(migrations)
        Set command = mDb.CreateCommand("INSERT OR IGNORE INTO Products (ID, Name, Stock, Price) VALUES (1, 'Apple', 42, 1.25), (2, 'Apricot', 18, 2.10), (3, 'Banana', 73, 0.85), (4, 'Blueberry', 31, 3.40), (5, 'Cherry', 26, 4.15), (6, 'Grape', 64, 2.75), (7, 'Kiwi', 14, 1.90), (8, 'Mango', 22, 2.45), (9, 'Orange', 57, 1.15), (10, 'Pear', 39, 1.60), (11, 'Plum', 28, 2.05), (12, 'Raspberry', 16, 3.85)")
        ignored = Await command.ExecuteAsync()
        ignored = Await LoadPage(0)
    Catch failure As McException
        grdProducts.Loading = False
        grdProducts.ErrorText = failure.Message
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
    End Try
End Sub

Private Async Function LoadPage(ByVal requestedIndex As Long) As Boolean
    Dim command As DbCommand
    Dim result As Recordset

    grdProducts.Loading = True
    grdProducts.ErrorText = ""
    cmdPrevious.Enabled = False
    cmdNext.Enabled = False
    cmdSearchAll.Enabled = False
    cmdClearSearch.Enabled = False
    Try
        Set command = mDb.CreateCommand("SELECT ID, Name, Stock, Price FROM Products WHERE instr(lower(Name), lower(@needle)) > 0")
        command.AddParameter "@needle", mDatabaseSearch
        Set result = Await command.QueryPageAsync("Name,ID", requestedIndex, 5)
        Set mRows = result.Bind()
        Set grdProducts.DataSource = mRows
        mPageIndex = requestedIndex
        grdProducts.FilterText = txtFilter.Text
        UpdateSelectionStatus
        cmdPrevious.Enabled = mPageIndex > 0
        cmdNext.Enabled = mRows.HasMore
        cmdSearchAll.Enabled = True
        cmdClearSearch.Enabled = True
        grdProducts.Loading = False
        UpdatePageStatus
        LoadPage = True
    Catch failure As McException
        grdProducts.Loading = False
        grdProducts.ErrorText = failure.Message
        cmdSearchAll.Enabled = True
        cmdClearSearch.Enabled = True
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
        LoadPage = False
    End Try
End Function

Private Sub UpdatePageStatus()
    If mDatabaseSearch = "" Then
        lblStatus.Caption = "Database: all rows | provider page " & CStr(mPageIndex + 1)
    Else
        lblStatus.Caption = "Database search: " & mDatabaseSearch & " | provider page " & CStr(mPageIndex + 1)
    End If
    If txtFilter.Text <> "" Then
        lblStatus.Caption = lblStatus.Caption & " | local " & CStr(grdProducts.VisibleRowCount) & "/" & CStr(grdProducts.LoadedRowCount)
    Else
        lblStatus.Caption = lblStatus.Caption & " | " & CStr(grdProducts.LoadedRowCount) & " loaded"
    End If
End Sub

Private Async Sub cmdPrevious_Click()
    Dim loaded As Boolean
    If mPageIndex <= 0 Then Exit Sub
    loaded = Await LoadPage(mPageIndex - 1)
End Sub

Private Async Sub cmdNext_Click()
    Dim loaded As Boolean
    If mRows Is Nothing Then Exit Sub
    If Not mRows.HasMore Then Exit Sub
    loaded = Await LoadPage(mPageIndex + 1)
End Sub

Private Sub txtFilter_Change()
    grdProducts.FilterText = txtFilter.Text
    If mRows Is Nothing Then Exit Sub
    UpdatePageStatus
End Sub

Private Async Sub cmdSearchAll_Click()
    Dim loaded As Boolean
    Dim previousSearch As String
    If mDb Is Nothing Then Exit Sub
    previousSearch = mDatabaseSearch
    mDatabaseSearch = txtServerFilter.Text
    loaded = Await LoadPage(0)
    If Not loaded Then mDatabaseSearch = previousSearch
End Sub

Private Async Sub cmdClearSearch_Click()
    Dim loaded As Boolean
    Dim previousSearch As String
    If mDb Is Nothing Then Exit Sub
    previousSearch = mDatabaseSearch
    mDatabaseSearch = ""
    txtServerFilter.Text = ""
    loaded = Await LoadPage(0)
    If Not loaded Then mDatabaseSearch = previousSearch
End Sub

Private Sub grdProducts_SelectionChanged()
    UpdateSelectionStatus
End Sub

Private Sub UpdateSelectionStatus()
    Dim row As Long
    If mRows Is Nothing Then
        lblSelection.Caption = "No selection"
        Exit Sub
    End If
    row = CInt(grdProducts.SelectedIndex)
    If row < 0 Or row >= mRows.RowCount Then
        lblSelection.Caption = "No selection"
        Exit Sub
    End If
    lblSelection.Caption = "Selected: " & CStr(mRows.Value(row, "Name"))
End Sub

Private Sub cmdQuit_Click()
    App.Quit
End Sub

Step 5Keep resources and storage reproducible#

No external resource or migration files are declared. Any inline migration, seed data or local JSON is present in the code above. Runtime application data and private preferences are not included in the archive.

Step 6Check, run and test the result#

Save all files and run Check. Resolve diagnostics before Run. Developer CLI users can work from the extracted folder:

Developer CLI

mcbasic check DataGridPaging.mcproj
mcbasic run DataGridPaging.mcproj

Acceptance checklist

Move from the first page to the next and back.
Apply a local filter and inspect visible versus loaded counts.
Search all records for a value on another page.
Clear the database search and confirm paging resets.

Step 7Make one change and repeat the tests#

Change the page size and test the final partial page and the disabled Next command.

Save a working Git checkpoint first. Change one behavior, Check again and repeat the acceptance checklist. Compare with the downloaded solution if a regression appears.

Source baseline 20f4bfed6b · Build manifest

Page records and distinguish two kinds of search

Step 1Prepare a safe practice project#

Step 2Build the Form and set its properties#

MainForm

Step 3Connect the events explicitly#

MainForm

Step 4Enter the complete behavior#

Forms/MainForm.bas

Step 5Keep resources and storage reproducible#

Step 6Check, run and test the result#

Acceptance checklist

Step 7Make one change and repeat the tests#

Continue learning#

`Forms/MainForm.bas`