SQlite3 database won't save records
lazarus / 16-Databases

----------------------------------------------------------------------
mrmikehicks · 2009-11-11 05:11

I'm using sqlit3 in an application. When I add a record to the
database, it works fine, but when I try to delete a record, it shows
that the record is deleted, but if I close the program and go back in
again, the deleted record is still there. It's like the delete
transaction is not sticking. The event handler for the dbnavigator
uses an after delete and an after post:
procedure Tmain_form.sqlite_ds_itemAfterDelete(DataSet: TDataSet);
begin
     dmr.sqlite_ds_item.UpdateRecord;
     dmr.sqlite_ds_item.ApplyUpdates;
end;

procedure Tmain_form.sqlite_ds_itemAfterPost(DataSet: TDataSet);
begin
     dmr.sqlite_ds_item.UpdateRecord;
     dmr.sqlite_ds_item.ApplyUpdates;
end;
I think this should work but don't know why the database is not being
saved when the program closes.
     Thanks for any and all advice on this problem.
     Mike

----------------------------------------------------------------------
davesimplewear · 2009-11-11 05:48

I don't know if you already tried this but assigning a primary key in
the db and in the sqlite dataset is important otherwise changes aren't
always done.
QuoteCREATE TABLE Debtor
(
   DrID       INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,and
Primary Key In Dataset in SQLITE3 table DrID

----------------------------------------------------------------------
LacaK · 2009-11-16 07:08

Quote from: mrmikehicks on November 11, 2009, 06:11:02 amI'm using
sqlit3 in an application.
Please specify, which components do you use ?
TSQLite3Connection+TSQLQuery
If yes, then you must also Commit changes ...
If no, there may be problem if some fields are NULL ...

----------------------------------------------------------------------
ercolesptr · 2022-10-27 18:54

I know it has been 13 years since the last post but since no one
provided the solution and I spent hours to find it I decided to post
it for the sake of other coders.

For the Delete button in DBNavigator to work you must set the
following 2 Option settings to True in your SQLQuery component:

sqoAutoApplyUpdates
sqoAutoCommit

Once these are ticked Delete works normally then.