Query SQLite without guessing

Open, migrate, then query#

Open a connection with Set db = Await Sqlite.OpenAsync(path). A writable app-owned path comes from AppPaths.DataFile. Apply the project’s migrations before reading a table they create. The connection serializes work through its worker; asynchronous calls keep the UI from waiting synchronously.

The CustomerDesk tutorial supplies the startup handler, migration file, declarations and complete close/cancellation behavior. Do not paste a query into Form_Load and make that unsupported event Async.

Keep user text out of SQL syntax#

Create the command from a SQL string containing named parameters and supply the values with AddParameter. Names beginning with @, : or $ are supported; anonymous ? placeholders are not. Bind each required parameter exactly once. Extra or duplicate parameters are argument errors, not a way to append query options.

A search pattern may intentionally contain % wildcards. That is a product decision separate from SQL injection: a bound parameter is still a pattern when used by LIKE. Decide whether the UI promises literal text or SQL wildcard matching.

A complete query helper from CustomerDesk#

This helper requires mDb, mRows, txtFilter, grdCustomers and lblStatus from the full project. It refreshes the grid only after the query returns, resets selection and reports the new row count.

Private Async Function ReloadCustomers() As Boolean
    Dim command As DbCommand
    Dim pattern As String

    pattern = "%" & Trim$(txtFilter.Text) & "%"
    Set command = mDb.CreateCommand("SELECT id, name, email, version FROM customers WHERE name LIKE @pattern ORDER BY name, id")
    command.AddParameter "@pattern", pattern
    Set mRows = Await command.QueryAsync()
    Set grdCustomers.DataSource = mRows.Bind()
    grdCustomers.SelectedIndex = -1
    lblStatus.Caption = CStr(mRows.RowCount) & " customer(s)"
    ReloadCustomers = True
End Function

Choose the result contract#

Recordset rows are zero-based. Read RowCount before accessing row zero. Use Value(index, columnName) or Rows(index).Value(columnName). Do not invent cursor-style EOF, MoveNext, Fields or RecordCount members.

Handle nullable values before displaying them#

A database Null is carried as a nullable value, not automatically converted into an empty String. Use IsNull before CStr or a numeric conversion. Unknown columns and out-of-range row indexes are different failures: confirm the SELECT list and the actual snapshot size.

The precise types and method signatures are in SQLite APIs and records and binding APIs. For a change involving several commands, continue with transactions.