Member Avatar for lukasb

Hi
I am useing Lazarus and a odbc connection to connect to a MS Access database

I connect fine and get all records displayed in a dbgrid .
My problem is to get the changes written back to the db.

When i try to write the data back to the db I get the error :

DBConnection: No update query specified and failed to generate one.

I do the changes in the dbgrid and do a SQLQuery1.ApplyUpdates;

Please help

Thanks

  • 2 Contributors
  • 1 Reply
  • 687 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

— that error means SQLDB could not find a ready-to-run UPDATE/INSERT/DELETE to apply. TSQLQuery will try to generate those statements automatically, but if it cannot (no UpdateSQL set and insufficient metadata) ApplyUpdates raises "No update query specified and failed to generate one." (see the SQLDB docs on automatic UpdateSQL generation and the UpdateSQL property). (freepascal.org)

The usual causes are: the SELECT is not a simple, single updatable table (joins, aliases, aggregates), the primary-key/unique column is not part of the result set, or the ODBC/Access side does not expose a unique index/primary key so SQLDB cannot mark key fields. Microsoft Access will not allow updates from queries that lack a unique index or primary key, and linked ODBC tables without a unique index are not updatable. (learn.microsoft.com)

Practical fixes (in roughly this order):

  1. Make sure the Access table actually has a primary key and that the key column is included in your SELECT (SELECT * FROM MyTable is the simplest test).
  2. Let SQLDB mark the primary key fields automatically: set SQLQuery.UsePrimaryKeyAsKey := True and choose an UpdateMode (upWhereKeyOnly is a common choice). Note: UsePrimaryKeyAsKey only updates provider flags for default (non-persistent) fields; if you created persistent fields, either recreate them or set each field’s ProviderFlags (e.g. include pfInKey).
  3. If auto-generation still fails, supply UpdateSQL / InsertSQL / DeleteSQL explicitly with parameterized statements (old values use :OLD_FIELDNAME). (freepascal.org)

A minimal example to try (adjust names to match your table/fields):

SQLQuery1.Close;
SQLQuery1.SQL.Text := 'SELECT * FROM MyTable';
SQLQuery1.UsePrimaryKeyAsKey := True;
SQLQuery1.UpdateMode := upWhereKeyOnly;
SQLQuery1.Open;

// after edits in the grid
SQLQuery1.ApplyUpdates;
SQLTransaction1.Commit;

// If needed, provide explicit UpdateSQL:
SQLQuery1.UpdateSQL.Text := 'UPDATE MyTable SET FieldA=:FieldA WHERE ID=:OLD_ID';

If changes still won’t write: confirm the DSN/ODBC connection and file are writable, that the query is updatable (no aggregates/unsupported joins), and that the transaction component is active/assigned. As noted, posting the actual component/property code (your SQL, which fields you declared at design time, and the connection/transaction setup) will make it much easier to spot the remaining issue. (learn.microsoft.com)

Member Avatar for rproffitt

I took a moment to google "DBConnection: No update query specified and failed to generate one." and found priors. In those prior discussions folk would provide code blocks for others to pick over and comment.

That said, I don't use that compiler and have actively avoided MS Access for over a decade for reasons. I don't want you to think your issue is being ignored.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.