SQLite and AutoInc field

[essence-ciel]

Hi,
Sorry for my bad English.

A few years ago, I wrote a program in Delphi + SQLite, which I use on a daily basis for my professional needs. I'm working with a Database which have 60 tables I open/close as needed. It works perfectly, but I'm looking to migrate to Lazarus.

I'm working under Windows 10/11, Lazarus 2.2.6, Zeos 7.14.2, SQlite 3.43.2. Before writing these topic, I spend time to read about SQLite and AutoCommit and I've tried the proposed solutions without success. I can't solve my probleme.

Here my Table describer :

Code: Select all

CREATE TABLE AFFECTIONS (
    ID_AFFECTION INTEGER PRIMARY KEY ASC ON CONFLICT ROLLBACK AUTOINCREMENT
                              UNIQUE ON CONFLICT ROLLBACK
                              NOT NULL
                              DEFAULT (0),
    AFFECTION     VARCHAR (75) DEFAULT ('') 

In the DataModule I have TConnection (Zconnection), TDataSource DAffections which "pointed" on TDataSet (TZQuery) TAffections, IndexFieldNames : Affection. No UpDateSql, I read I don't need it. TDataSet points to TConnection.

In the DataModule, I have :

Code: Select all

procedure TDataModule1.TAffectionsAfterPost(DataSet: TDataSet);
begin
     DataModule1.TAffections.Edit;
     DataModule1.TAffections.Post;
     DataModule1.Connexion.AutoCommit := False;   
     DataModule1.Connexion.Commit;
     DataModule1.Connexion.AutoCommit := True;
end;

procedure TDataModule1.ConnexionCommit(Sender: TObject);
begin
  ShowMessage('Données enregistrées'); (Data registered...)
end;  

If I don't have the line DataModule1.TAffections.Edit, an error message says "table not in insert or edit mode"... And I never see the Message...

SQL :

Code: Select all

SELECT ID_AFFECTION, AFFECTION
FROM AFFECTIONS

I just use dBNavigator for enter datas in a dBGrid. But when I try to Insert/Append datas in the dBGrid a white line is insert in the dBGrid and, when I exit the first Column (ID_Affection) the new ID_Affection is not written even though it is an incremented field...

In Delphi, Id_Affection appear when I click on Post button but not here... So I have to write it by hand or it indicate 0 (see below).


I therefore enter the data by hand, including the number of the incremented field. If I don't enter it and I Post, it displays 0 (the normal default value...). If I enter a number (last_rowid + 1), I can enter Affection, but the program freezes after...

When I quit the program and open it, data are there, but if I try to modify them, the program freezes too. I see them in SQLite Studio too.

Better still, the dBNavigator blocks if I want to modify, refresh or delete... the input!

Where is my mistake? Why does dBNavigator block when adding, modifying, cancelling or refreshing?

I'm not a programmer, but I do a bit of programming. Thanks for help.

[marsupilami]

Hello essence-ciel,

i am not sure, what you try to do. But:

• Your code in TDataModule1.TAffectionsAfterPost doesn't make sense. Even worse - usually this will put you into an infinite loop because you call post, which will call AfterPost which will call Post which will call AfterPost ...........
• Calling Edit and then Calling Post doesn't make sense. Usually you call Post after making Canges. But you didn't do any changes after you called Edit. So remove the Edit- and Post- Lines.
• Also modifying AutoCommit in After Post doesn't make sense. According to your code you already are in AutoCommit mode. AfterPOst is called *after* the data has been sent to the database. All you do with the three lines of code is generating an empty transaction. Remove the AutoCommit- and Commit- Lines too.
• Test if you still have problems.

Best regards,

Jan

[essence-ciel]

Hi,
Sorry for delay, a family problem.
Thanks, I've modify code and It works fine.
Best regards,