Update SQLite3 database with DBNavigator: cannot commit transaction...

[jeanchristophe]

I want to make a simple program out of the box, and I am running into several problems. I search on the forum and find a lot of post on the subject, that's why I decided to write it all in the following lines.
Maybe should it be on the wiki page, but I am not a professional and works mainly on one program, so my knowledge is not that deep. The second reason is that my explanation is just based on ”Make it works,  that's it."
So if a redactor find these lines useful, please use them as you want! :-)

I want to make this program to modify a field in a table by copying the file name of the picture into a field, so I can use it afterwards in an another program.

Code: [Select]

How to use Sqlite3 with a DBGrid and a DBNavigator
Components:
SQLite3Connection:
DatabaseName: full path of my database
Transaction: SQLTransaction
Connected: True
DataSource:
DataSet: SQLQuery
Enabled: True
SQLQuery
Database: SQLite3Connection
SQL: select * from whatever;
Transaction: SQLTransaction
Active: True
SQLTransaction
Database: SQLite3Connection
Active: True
OpenPictureDialog
DBGrid
Datasource: DataSource
DBNavigator
Datasource: DataSource

I add one more component to make my project:
DBEdit
Datasource: DataSource
Image

And three buttons related to my project:
Choosefile (onClick):
if OpenPictureDialog1.Execute then
begin
  filename := OpenPictureDialog1.Filename;
    Image1.Picture.LoadFromFile(OpenPictureDialog1.FileName);
end;
FileName (onClick):
filename  := StringReplace(filename, 'something', '..', [rfReplaceAll, rfIgnoreCase]);
  DBEdit2.Text:= filename;
Commit (onClick):
SQLQuery1.ApplyUpdates;
SQLTransaction1.CommitRetaining;

That's it!
How do I use it:
F9 -> Program runs
Choose the picture with ChooseFile button
Push the edit button on the DBNavigator
Push the FileName button
Push the post button on the DBNavigator
Push the Commit button

Unfortunately I get this message:

Code: [Select]

cannot commit transaction - SQL statements in progress.

I found 21 threads on the subject (SQLite3 DBNavigator), and I still have some to read, but if somebody could tell me, what I have not done right, it will be nice

Best Regards
Jean-Christophe

[kpeters58]

Zip up your project (without executable(s)) and attach it so we can help you

[jeanchristophe]

Hello,

Here is the zip file!

Best Regards
Jean-Christophe

[kpeters58]

Have a look at the changes I made to get it to work. BTW, you forgot to supply the 32bit SQLite DLL and also, more importantly, your database file.
I reverse-engineered something that may or may not make sense - my Danish isn't that great.

[BigChimp]

Not supplying the dll (or any executable) is a good thing IMO for security reasons...

[jeanchristophe]

Thank you for your answer kpeters58! 

I try to run it, and I have now the following message:

Code: [Select]

...exception class 'EDatabaseError'...
Missing (compatible) underlying dataset. can not open

Best Regards
Jean-Christophe

[kpeters58]

Downloaded and tested: Works just fine.....


You realize that you have to adjust this line in FormCreate to match your setup?


  SQLite3Connection1.DatabaseName := 'c:\temp\jc\picture\stamps.sqlite';