I decided to use SQLite via SQLdb instead of via the sqlite3dataset, but I think I'm missing a point here.
I have a connection to my SQLite database, a transaction linked to the connection, a SQlquery to a table in my database and a datasource all placed on a datamodule.
When starting my program I activate the connection and start the transaction in the oncreate procedure of the datamodule.
My form opens and a dbgrid and some dbedits show data from the table correctly.
In the formclose procedure of my form I do a SQLquery.applyupdates and a connection.transaction.commitretaining.
This works well for existing records and all changes I make in the form to my table are correctly saved.
The problem starts when in my form I add a new record to the table (via a EXECSQL with an INSERT statement), as long as I stay in my form the new record is shown correctly but as soon as I close my form the connection.transaction.commitretaining breaks my program with an error telling me the database is locked.
I must be doing something fundamentally wrong but cannot get my finger on it.
What's the correct way to insert, update and delete data in a table via a form and commit all changes?
I really hope someone can point out my error, because this is driving me crazy.
I'm using Lazarus 1.04 with FPC 2.6.0.