Bind choices to stable record IDs

{
  "schemaVersion": 1,
  "projectId": "b72b7cbe-988d-4ca1-8a68-c2cb8221f89e",
  "name": "ChoiceBindingSample",
  "kind": "DesktopApp",
  "language": {
    "dialect": "McBasic1",
    "requireExplicit": true,
    "compare": "Binary",
    "defaultArrayBase": 0
  },
  "application": {
    "bundleIdentifier": "com.mcbasic.samples.choicebinding",
    "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": []
}

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

    cmdDeleteDraft.Enabled = False
    cmdCancelDraft.Enabled = False
    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("choice-binding.db"))
        Set migrations = Sqlite.CreateMigrationSet()
        migrations.Add 1, "CREATE TABLE IF NOT EXISTS Products (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Category TEXT NOT NULL)"
        ignored = Await mDb.ApplyMigrationsAsync(migrations)
        Set command = mDb.CreateCommand("INSERT OR IGNORE INTO Products (ID, Name, Category) VALUES (1, 'Apple', 'Fruit'), (2, 'Apricot', 'Fruit'), (3, 'Carrot', 'Vegetable')")
        ignored = Await command.ExecuteAsync()
        Set command = mDb.CreateCommand("SELECT ID, Name, Category FROM Products ORDER BY ID")
        Set rows = Await command.QueryAsync()
        Set mItems = rows.Bind()
        Set cboProducts.DataSource = mItems
        Set lstProducts.DataSource = mItems
        cboProducts.SelectedValue = 2
        lstProducts.SelectedValue = 3
        cmdDeleteDraft.Enabled = True
        lblStatus.Caption = "Name and Category are display columns; ID is the typed value."
        UpdateSelection
    Catch failure As McException
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
    End Try
End Sub

Private Sub cmdDeleteDraft_Click()
    If mItems Is Nothing Then Exit Sub
    If mItems.Dirty Then Exit Sub
    mItems.BeginEdit
    mItems.Delete 0
    cmdDeleteDraft.Enabled = False
    cmdCancelDraft.Enabled = True
    lblStatus.Caption = "Apple removed from the draft only; SQLite is unchanged."
    UpdateSelection
End Sub

Private Sub cmdCancelDraft_Click()
    If mItems Is Nothing Then Exit Sub
    mItems.CancelEdit
    cmdDeleteDraft.Enabled = True
    cmdCancelDraft.Enabled = False
    lblStatus.Caption = "Draft cancelled; all three rows restored."
    UpdateSelection
End Sub

Private Sub UpdateSelection()
    If cboProducts.SelectedIndex >= 0 And lstProducts.SelectedIndex >= 0 Then
        lblSelected.Caption = "Combo ID " & CStr(cboProducts.SelectedValue) & " | List ID " & CStr(lstProducts.SelectedValue)
    Else
        lblSelected.Caption = "Select a row in either control."
    End If
End Sub

Private Sub cboProducts_Change()
    UpdateSelection
End Sub

Private Sub lstProducts_Change()
    UpdateSelection
End Sub

Private Sub cmdQuit_Click()
    App.Quit
End Sub

Option Explicit

Private mDb As Database
Private mItems As BindingSource

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

    cmdDeleteDraft.Enabled = False
    cmdCancelDraft.Enabled = False
    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("choice-binding.db"))
        Set migrations = Sqlite.CreateMigrationSet()
        migrations.Add 1, "CREATE TABLE IF NOT EXISTS Products (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Category TEXT NOT NULL)"
        ignored = Await mDb.ApplyMigrationsAsync(migrations)
        Set command = mDb.CreateCommand("INSERT OR IGNORE INTO Products (ID, Name, Category) VALUES (1, 'Apple', 'Fruit'), (2, 'Apricot', 'Fruit'), (3, 'Carrot', 'Vegetable')")
        ignored = Await command.ExecuteAsync()
        Set command = mDb.CreateCommand("SELECT ID, Name, Category FROM Products ORDER BY ID")
        Set rows = Await command.QueryAsync()
        Set mItems = rows.Bind()
        Set cboProducts.DataSource = mItems
        Set lstProducts.DataSource = mItems
        cboProducts.SelectedValue = 2
        lstProducts.SelectedValue = 3
        cmdDeleteDraft.Enabled = True
        lblStatus.Caption = "Name and Category are display columns; ID is the typed value."
        UpdateSelection
    Catch failure As McException
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
    End Try
End Sub

Private Sub cmdDeleteDraft_Click()
    If mItems Is Nothing Then Exit Sub
    If mItems.Dirty Then Exit Sub
    mItems.BeginEdit
    mItems.Delete 0
    cmdDeleteDraft.Enabled = False
    cmdCancelDraft.Enabled = True
    lblStatus.Caption = "Apple removed from the draft only; SQLite is unchanged."
    UpdateSelection
End Sub

Private Sub cmdCancelDraft_Click()
    If mItems Is Nothing Then Exit Sub
    mItems.CancelEdit
    cmdDeleteDraft.Enabled = True
    cmdCancelDraft.Enabled = False
    lblStatus.Caption = "Draft cancelled; all three rows restored."
    UpdateSelection
End Sub

Private Sub UpdateSelection()
    If cboProducts.SelectedIndex >= 0 And lstProducts.SelectedIndex >= 0 Then
        lblSelected.Caption = "Combo ID " & CStr(cboProducts.SelectedValue) & " | List ID " & CStr(lstProducts.SelectedValue)
    Else
        lblSelected.Caption = "Select a row in either control."
    End If
End Sub

Private Sub cboProducts_Change()
    UpdateSelection
End Sub

Private Sub lstProducts_Change()
    UpdateSelection
End Sub

Private Sub cmdQuit_Click()
    App.Quit
End Sub