[SOLVED] Database update not working

[pjtuloup]

Hi all,

I'm afraid I'll have to call on you again...

I have a problem updating two Mysql tables in succession. The first update on table 'eoperations' succeeds but the second on table 'ehistocreaop' fails (without error message: the update simply does not take place). Yet when I perform the live SQL query with a front end, the update is performed, so the query is correct.
Here is my code:

Code: Pascal  [Select][+][-]

1.    //++++++++++++++++++++++++++++++Enregistrement dans la Table operations
2.    Requete:=QuotedStr('INSERT INTO eoperations VALUES(');
3.    Requete+=IntToStr(NoNewOp);
4.    Requete+=QuotedStr(',');
5.    //Détermination Nature Opération
6.    for i:=0 To SelNature.Items.Count-1 do
7.       if(SelNature.Text=FormSaisMvtEmp.SelNature.Items[i]) then
8.          IdOpTyp:=i;
9.    Requete+=IntToStr(IdOpTyp);
10.    Requete+=QuotedStr(',');
11.    //Détermination Id Type Support
12.    for i:=0 To SelNatSup.Items.Count-1 do
13.       if(SelNatSup.Text=FormSaisMvtEmp.SelNatSup.Items[i]) then
14.          IdSupTyp:=i;
15.    Requete+=IntToStr(IdSupTyp);
16.    Requete+=QuotedStr(',');
17.    Requete+=SaisNatRef.Text;
18.    Requete+=QuotedStr(',');
19.    Requete+=RightStr(DateToStr(SelDateCPT.Date),4);
20.    Requete+='-';
21.    Requete+=copy(DateToStr(SelDateCPT.Date),4,2);
22.    Requete+='-';
23.    Requete+=LeftStr(DateToStr(SelDateCPT.Date),2);
24.    Requete+=QuotedStr(',');
25.    Requete+=RightStr(DateToStr(SelDateValid.Date),4);
26.    Requete+='-';
27.    Requete+=copy(DateToStr(SelDateValid.Date),4,2);
28.    Requete+='-';
29.    Requete+=LeftStr(DateToStr(SelDateValid.Date),2);
30.    Requete+=QuotedStr(',');
31.    Requete+=RightStr(DateToStr(SelDateSais.Date),4);
32.    Requete+='-';
33.    Requete+=copy(DateToStr(SelDateSais.Date),4,2);
34.    Requete+='-';
35.    Requete+=LeftStr(DateToStr(SelDateSais.Date),2);
36.    Requete+=QuotedStr(',');
37.    Requete+=RightStr(DateToStr(SelDateEffet.Date),4);
38.    Requete+='-';
39.    Requete+=copy(DateToStr(SelDateEffet.Date),4,2);
40.    Requete+='-';
41.    Requete+=LeftStr(DateToStr(SelDateEffet.Date),2);
42.    Requete+=QuotedStr(',');
43.    Requete+=Observations.Text;
44.    Requete+=QuotedStr(');');
45.  
46.    //Suppression des apostrophes de début et de fin dans la requête
47.    Requete:=copy(Requete,1,Requete.Length-1);
48.    Requete:=RightStr(Requete,Requete.Length-1);
49.  
50.    //Application.MessageBox(PAnsiChar(UserEnCours), 'Alerte Utilisateur');
51.    //Application.MessageBox(PAnsiChar(Requete), 'Alerte Utilisateur');
52.  
53.    //Changement de User et modification de la table
54.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
55.    DataModule1.SQLConnection1.Password := 'LugesAdm';
56.    with DataModule1.SQLQuery1 do
57.    begin
58.        Close;
59.        SQL.Text:=Requete;
60.        ExecSQL;
61.    end;
62.    //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
63.    NoNewHistOp:=StrToInt(DerNoHistOp);
64.    NoNewHistOp:=NoNewHistOp+1;
65.    Requete:=QuotedStr('INSERT INTO ehistocreaop VALUES(');
66.    Requete+=IntToStr(NoNewHistOp);
67.    Requete+=QuotedStr(',');
68.    Requete+=IntToStr(NoNewOp);
69.    Requete+=QuotedStr(',');
70.    Requete+=RightStr(DateToStr(Date),4);
71.    Requete+='-';
72.    Requete+=copy(DateToStr(Date),4,2);
73.    Requete+='-';
74.    Requete+=LeftStr(DateToStr(Date),2);
75.    Requete+=QuotedStr(',');
76.    Requete+=TimeToStr(Time);
77.    Requete+=QuotedStr(',');
78.    Requete+='DGFIP';
79.    Requete+=QuotedStr(',');
80.    Requete+=UserEnCours;
81.    Requete+=QuotedStr(',');
82.    Requete+='AdmTemp';
83.    Requete+=QuotedStr(',');
84.    Requete+='eoperations';
85.    Requete+=QuotedStr(');');
86.  
87.    //Suppression des apostrophes de début et de fin dans la requête
88.    Requete:=copy(Requete,1,Requete.Length-1);
89.    Requete:=RightStr(Requete,Requete.Length-1);
90.    Application.MessageBox(PAnsiChar(Requete), 'Alerte Utilisateur');
91.  
92.    //Changement de User et modification de la table
93.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
94.    DataModule1.SQLConnection1.Password := 'LugesAdm';
95.    with DataModule1.SQLQuery1 do
96.    begin
97.        Close;
98.        SQL.Text:=Requete;
99.        ExecSQL;
100.    end;
101.  
102.  

Can you tell me where I'm going wrong? many thanks in advance !

[vfclists]

Showing the SQL generated might help, and be sure that there is nothing happening server side that might cause the update to fail silently such as incorrect triggers and procedures.

[pjtuloup]

thanks ! No Mysql procedure or trigger used. And I am sure that the generated query is correct, because I had it displayed in a text field and copied and pasted it for execution in HeidiSQL and that execution was successful.

[paweld]

the way you write queries is very unreadable and will lead to errors very quickly. you should use parameters, then it is the component that takes care of the proper formatting of the data. in addition, after the name of the table you should list the names of the columns in exactly the same order as the parameters in the “VALUES” section, that is:
insert into table (col1, col2, col3) values (val1, val2, val3).

I took the liberty of rewriting your code, it is much more readable and avoids errors:

Code: Pascal  [Select][+][-]

1.   //Détermination Nature Opération
2.   for i := 0 to SelNature.Items.Count - 1 do
3.     if (SelNature.Text = FormSaisMvtEmp.SelNature.Items[i]) then
4.     begin
5.       IdOpTyp := i;
6.       break;
7.     end;
8.     //Détermination Id Type Support
9.   for i := 0 to SelNatSup.Items.Count - 1 do
10.     if (SelNatSup.Text = FormSaisMvtEmp.SelNatSup.Items[i]) then
11.     begin
12.       IdSupTyp := i;
13.       break;
14.     end;
15.   // query operations
16.   if SQLQuery1.Active then
17.     SQLQuery1.Close;
18.   SQLQuery1.SQL.Clear;
19.   SQLQuery1.SQL.Add(' INSERT INTO eoperations '); //add list of columns
20.   SQLQuery1.SQL.Add(' VALUES (:nno, :iot, :ist, :snr, :sdc, :sdv, :sds, :sde,); ');
21.   SQLQuery1.ParamByName('nno').AsInteger := NoNewOp;
22.   SQLQuery1.ParamByName('iot').AsInteger := IdOpTyp;
23.   SQLQuery1.ParamByName('ist').AsInteger := IdSupTyp;
24.   SQLQuery1.ParamByName('snr').AsString := SaisNatRef.Text;
25.   SQLQuery1.ParamByName('sdc').AsDate := SelDateCPT.Date;
26.   //or is must be TEXT then
27.   //SQLQuery1.ParamByName('sdc').AsString := FormatDateTime('yyyy-mm-dd', SelDateCPT.Date);
28.   SQLQuery1.ParamByName('sdv').AsDate := SelDateValid.Date;
29.   SQLQuery1.ParamByName('sds').AsDate := SelDateSais.Date;
30.   SQLQuery1.ParamByName('sde').AsDate := SelDateEffet.Date;
31.   SQLQuery1.ParamByName('o').AsString := Observations.Text;
32.   //Changement de User et modification de la table
33.   SQLConnection1.UserName := 'LugesAdm';
34.   SQLConnection1.Password := 'LugesAdm';
35.   SQLQuery1.ExecSQL;
36.  
37.   //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
38.   NoNewHistOp := StrToInt(DerNoHistOp);
39.   NoNewHistOp := NoNewHistOp + 1;
40.   SQLQuery1.SQL.Clear;
41.   SQLQuery1.SQL.Add(' INSERT INTO ehistocreaop '); //add list of columns
42.   SQLQuery1.SQL.Add(' VALUES (:nnho, :nno, :dt, :tm, :s1, :s2, :s3, :s4); ');
43.   SQLQuery1.ParamByName('nnho').AsInteger := NoNewHistOp;
44.   SQLQuery1.ParamByName('nno').AsInteger := NoNewOp;
45.   SQLQuery1.ParamByName('dt').AsDate := Date;
46.   SQLQuery1.ParamByName('tm').AsTime := Time;
47.   SQLQuery1.ParamByName('s1').AsString := 'DGFIP';
48.   SQLQuery1.ParamByName('s2').AsString := UserEnCours;
49.   SQLQuery1.ParamByName('s3').AsString := 'AdmTemp';
50.   SQLQuery1.ParamByName('s4').AsString := 'eoperations';
51.  
52.   //Changement de User et modification de la table
53.   SQLConnection1.UserName := 'LugesAdm';
54.   SQLConnection1.Password := 'LugesAdm';
55.   SQLQuery1.ExecSQL;

[pjtuloup]

Thanks, Paweld !

I'm just a beginner

[pjtuloup]

Well,

I rewrote my code but I'm still at the same point: the Insert to query has no effect !

In the following code I limited the update to a single element: no error but "insert to" does nothing:

Code: Pascal  [Select][+][-]

1. //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
2.    NoNewHistOp:=StrToInt(DerNoHistOp);
3.    NoNewHistOp:=NoNewHistOp+1;
4.    DataModule1.SQLQuery1.SQL.Clear;
5.  
6.    DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt) VALUES (:Chp1i);');
7.  
8.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
9.  
10.    //Change User for modification de la table
11.    DataModule1.SQLConnection1.Connected := false;
12.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
13.    DataModule1.SQLConnection1.Password := 'LugesAdm';
14.    DataModule1.SQLConnection1.Connected := true;
15.    DataModule1.SQLQuery1.Active:=false;
16.    DataModule1.SQLQuery1.ExecSQL;
17.    //DataModule1.SQLQuery1.Active:=true;
18.  
19.    DataModule1.SQLConnection1.Connected := false;
20.  
21.  

Note that I commented this line:

   //DataModule1.SQLQuery1.Active:=true;

Because otherwise I would get a “non select statement” error even though, as you can seein the code, the SQLQuery was closed before the ExecSQL!!!

In addition, compared to my primitive code, I closed the connection before changing User/pwd and reopened it afterwards because with the mysql "showprocesslist" command I noticed that otherwise the request was not carried out with the desired User. But that doesn't change the problem of the query having no effect.

I understand absolutely nothing and I spent the afternoon on this.

[dseligo]

I rewrote my code but I'm still at the same point: the Insert to query has no effect !

Did you set up and use transaction?

Note that I commented this line:

   //DataModule1.SQLQuery1.Active:=true;

Because otherwise I would get a “non select statement” error even though, as you can seein the code, the SQLQuery was closed before the ExecSQL!!!

Of course. You set query's Active property to True (or you can also call SQLQuery1.Open) when you want results to be returned, not when you execute queries with ExecSQL (they don't return results, they include insert, update and delete, among others).

I understand absolutely nothing and I spent the afternoon on this.

I suggest that you make some small, but complete project which shows what are you trying to do. It is hard to guess how you set it up with code fragments you posted.
Include SQL that create tables and some sample data (if needed).

[pjtuloup]

Did you set up and use transaction?

No, I don't use a transaction.

Of course. You set query's Active property to True (or you can also call SQLQuery1.Open) when you want results to be returned, not when you execute queries with ExecSQL (they don't return results, they include insert, update and delete, among others).

Yes, I know that the dataset must be close or inactive before an ExecSQL, but why can't I not open or active it after the ExecSQL ???

I suggest that you make some small, but complete project which shows what are you trying to do. It is hard to guess how you set it up with code fragments you posted.
Include SQL that create tables and some sample data (if needed).

I'm just trying to add a row by "Insert Into" in one table first (which works) then in another (which fails). I'll try to give you more information.

[pjtuloup]

Well... I am reposting here the code of the procedure in question (rewritten thanks to Paweld). This procedure inserts a (different) row into two tables, an eoperations table and an ehiscreaop table which records the creation history in the first table.

The data to be recorded in the first table comes from entering a form and the data to be recorded in the second are mainly either constants or hourly data.

The main point is that writing to the first table always succeeds and writing to the second always fails (without errors) even though the code is almost the same... Why?

Code: Pascal  [Select][+][-]

1. procedure TFormSaisMvtEmp.BtnCreerClick(Sender: TObject);
2. var
3.   SaisOk: Boolean;
4.   NoNewOp:Integer;
5.   NoNewHistOp:Integer;
6.   i:Integer;
7.   IdOpTyp:Integer;
8.   IdSupTyp:Integer;
9. begin
10. SaisOk:=true;
11. if(SelNature.Text='Choisissez...') OR (SelNature.Text='') then SaisOk:=false;
12. if(SelNatSup.Text='Choisissez...') OR (SelNatSup.Text='') then SaisOk:=false;
13. if(SaisNatRef.Text='')  then SaisOk:=false;
14. if(SaisNoOp.Text='')   then SaisOk:=false;
15.  
16. if(SaisOk=true) then
17.    begin
18.    if(StrToInt(SaisNoOp.Text)<=StrToInt(DerNoOp)) then
19.         begin
20.         Application.MessageBox('No déjà utilisé: rectification automatique!', 'Alerte Utilisateur');
21.         NoNewOp:=StrToInt(DerNoOp);
22.         NoNewOp:=NoNewOp+1;
23.         SaisNoOp.Text:=IntToStr(NoNewOp);
24.         end;
25.    //++++++++++++++++++++++++++++++Enregistrement dans la Table operations
26.    if DataModule1.SQLQuery1.Active then
27.       DataModule1.SQLQuery1.Close;
28.    DataModule1.SQLQuery1.SQL.Clear;
29.    DataModule1.SQLQuery1.SQL.Add(' INSERT INTO eoperations (IdOp, IdOpTyp, IdSupTyp, RefSup, DateCTP, DateValid, DateSaisie, DateEffet, Observations) ');
30.    DataModule1.SQLQuery1.SQL.Add(' VALUES (:Chp1i, :Chp2i, :Chp3i, :Chp4s, :Chp5d, :Chp6d, :Chp7d, :Chp8d, :Chp9b); ');
31.    //Détermination Nature Opération
32.    for i:=0 To SelNature.Items.Count-1 do
33.       if(SelNature.Text=FormSaisMvtEmp.SelNature.Items[i]) then
34.          IdOpTyp:=i;
35.    //Détermination Id Type Support
36.    for i:=0 To SelNatSup.Items.Count-1 do
37.       if(SelNatSup.Text=FormSaisMvtEmp.SelNatSup.Items[i]) then
38.          IdSupTyp:=i;
39.    //Paramètres de la Requête
40.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewOp;
41.    DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := IdOpTyp;
42.    DataModule1.SQLQuery1.ParamByName('Chp3i').AsInteger := IdSupTyp;
43.    DataModule1.SQLQuery1.ParamByName('Chp4s').AsString := SaisNatRef.Text;
44.    DataModule1.SQLQuery1.ParamByName('Chp5d').AsDate := SelDateCPT.Date;
45.    DataModule1.SQLQuery1.ParamByName('Chp6d').AsDate := SelDateValid.Date;
46.    DataModule1.SQLQuery1.ParamByName('Chp7d').AsDate := SelDateSais.Date;
47.    DataModule1.SQLQuery1.ParamByName('Chp8d').AsDate := SelDateEffet.Date;
48.    DataModule1.SQLQuery1.ParamByName('Chp9b').AsString := Observations.Text;
49.    //Changement de User et modification de la table
50.    DataModule1.SQLConnection1.Connected := false;
51.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
52.    DataModule1.SQLConnection1.Password := 'LugesAdm';
53.    DataModule1.SQLConnection1.Connected := true;
54.    //Exécution de la Requête
55.    DataModule1.SQLQuery1.ExecSQL;
56.  
57.    //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
58.    NoNewHistOp:=StrToInt(DerNoHistOp);
59.    NoNewHistOp:=NoNewHistOp+1;
60.    if DataModule1.SQLQuery1.Active then
61.       DataModule1.SQLQuery1.Close;
62.    DataModule1.SQLQuery1.SQL.Clear;
63.  
64.    //DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt,IdItem,Date,Heure,Domaine,User,Profil,Table) VALUES (:Chp1i, :Chp2i, :Chp3d, :Chp4t, :Chp5s, :Chp6s, :Chp7s, :Chp8s);');
65.    DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt) VALUES (:Chp1i);');
66.  
67.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
68.    //DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := NoNewOp;
69.    //DataModule1.SQLQuery1.ParamByName('Chp3d').AsDate := Date;
70.    //DataModule1.SQLQuery1.ParamByName('Chp4t').AsString := FormatDateTime('hh:nn:ss', Time);
71.    //DataModule1.SQLQuery1.ParamByName('Chp5s').AsString := 'DGFIP';
72.    //DataModule1.SQLQuery1.ParamByName('Chp6s').AsString := UserEnCours;
73.    //DataModule1.SQLQuery1.ParamByName('Chp7s').AsString := 'AdmTemp';
74.    //DataModule1.SQLQuery1.ParamByName('Chp8s').AsString := 'eoperations';
75.  
76.    //Changement de User et modification de la table
77.    DataModule1.SQLConnection1.Connected := false;
78.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
79.    DataModule1.SQLConnection1.Password := 'LugesAdm';
80.    DataModule1.SQLConnection1.Connected := true;
81.  
82.    try
83.    DataModule1.SQLQuery1.Active:=false;
84.    DataModule1.SQLQuery1.ExecSQL;
85.    //DataModule1.SQLQuery1.Active:=true;
86.  
87.    DataModule1.SQLConnection1.Connected := false;
88.    except
89.      on e : exception do
90.      ShowMessage('Erreur: '+e.Message);
91.    end;
92.  
93.    Application.MessageBox('La fiche opération a été prise en compte et enregistrée dans l''historique.', 'Alerte Utilisateur');
94.  
95.    end
96. else
97.    begin
98.    Application.MessageBox('Veuillez servir les champs obligatoires!', 'Alerte Utilisateur');
99.    end
100. end;  

[dseligo]

Did you set up and use transaction?

No, I don't use a transaction.

That is then probably a culprit.
Go through this article: https://wiki.freepascal.org/SQLdb_Tutorial1
If I'll have time, maybe I'll make example with transaction based on your code.

Of course. You set query's Active property to True (or you can also call SQLQuery1.Open) when you want results to be returned, not when you execute queries with ExecSQL (they don't return results, they include insert, update and delete, among others).

Yes, I know that the dataset must be close or inactive before an ExecSQL, but why can't I not open or active it after the ExecSQL ???

You didn't change SQL.Text - you still have 'insert' in SQL.Text and you are trying to return rows with Open.

You must do something like this:

Code: Pascal  [Select][+][-]

1. SQL.Text := 'insert into ... ...';
2. ExecSQL;
3.  
4. SQL.Text := 'select row1, row2 from ... ...';
5. Open;

You could also use more SQLQuery components (i.e. in one could be code for insert, and in another code for select).

And if you could give some information.
I guess you use SQLdb components for database access?
What version of FPC/Lazarus do you have, and what OS?
What database you have problems with?

[pjtuloup]

You didn't change SQL.Text - you still have 'insert' in SQL.Text and you are trying to return rows with Open.

Oh, I see now.

You could also use more SQLQuery components (i.e. in one could be code for insert, and in another code for select).

I thought about it

And if you could give some information.
I guess you use SQLdb components for database access?
What version of FPC/Lazarus do you have, and what OS?
What database you have problems with?

Yes, I use SQL_db components
Lazarus  3.4
FPC 3.2.2
Os Client Windows 11 10.0.22631
Os Server Linux Debian 12.4
Mysql 8.0.36

[dseligo]

OK, I looked at your code.

It is strange when you say you don't use transaction because TSQLConnection class of SQLdb components should emit error 'Transaction not set.'.
But because you didn't make compileable test project which shows your problem, it is guessing from side of people who are trying to help you.

Anyway, try to do following: put TSQLTransaction on your DataModule1 from SQLdb components in Lazarus - now you should have SQLTransaction1 there alongside with existing SQLConnection1 and SQLQuery1.

Now change your code as below.

I also moved closing connection and changing user to the top, no need to do that twice.
IMHO, part under comment 'transaction setup' should be earlier in the code, but it will work in your code. If you have two separate accounts for read only and for inserting into database, you could have two connections. That way you don't need to clutter code where you insert and select data.

Code: Pascal  [Select][+][-]

1. procedure TFormSaisMvtEmp.BtnCreerClick(Sender: TObject);
2. var
3.   SaisOk: Boolean;
4.   NoNewOp:Integer;
5.   NoNewHistOp:Integer;
6.   i:Integer;
7.   IdOpTyp:Integer;
8.   IdSupTyp:Integer;
9. begin
10. SaisOk:=true;
11. if(SelNature.Text='Choisissez...') OR (SelNature.Text='') then SaisOk:=false;
12. if(SelNatSup.Text='Choisissez...') OR (SelNatSup.Text='') then SaisOk:=false;
13. if(SaisNatRef.Text='')  then SaisOk:=false;
14. if(SaisNoOp.Text='')   then SaisOk:=false;
15.  
16. if(SaisOk=true) then
17.    begin
18.  
19.    //Changement de User et modification de la table
20.    DataModule1.SQLConnection1.Connected := false;
21.  
22.    // transaction setup
23.    DataModule1.SQLTransaction1.Action := caCommit;
24.    DataModule1.SQLConnection1.Transaction := DataModule1.SQLTransaction1;
25.    DataModule1.SQLQuery1.Transaction := DataModule1.SQLTransaction1;
26.  
27.    DataModule1.SQLConnection1.UserName := 'LugesAdm';
28.    DataModule1.SQLConnection1.Password := 'LugesAdm';
29.    DataModule1.SQLConnection1.Connected := true;
30.  
31.    if(StrToInt(SaisNoOp.Text)<=StrToInt(DerNoOp)) then
32.         begin
33.         Application.MessageBox('No déja utilisé: rectification automatique!', 'Alerte Utilisateur');
34.         NoNewOp:=StrToInt(DerNoOp);
35.         NoNewOp:=NoNewOp+1;
36.         SaisNoOp.Text:=IntToStr(NoNewOp);
37.         end;
38.    //++++++++++++++++++++++++++++++Enregistrement dans la Table operations
39.    if DataModule1.SQLQuery1.Active then
40.       DataModule1.SQLQuery1.Close;
41.    DataModule1.SQLQuery1.SQL.Clear;
42.    DataModule1.SQLQuery1.SQL.Add(' INSERT INTO eoperations (IdOp, IdOpTyp, IdSupTyp, RefSup, DateCTP, DateValid, DateSaisie, DateEffet, Observations) ');
43.    DataModule1.SQLQuery1.SQL.Add(' VALUES (:Chp1i, :Chp2i, :Chp3i, :Chp4s, :Chp5d, :Chp6d, :Chp7d, :Chp8d, :Chp9b); ');
44.    //Détermination Nature Opération
45.    for i:=0 To SelNature.Items.Count-1 do
46.       if(SelNature.Text=FormSaisMvtEmp.SelNature.Items[i]) then
47.          IdOpTyp:=i;
48.    //Détermination Id Type Support
49.    for i:=0 To SelNatSup.Items.Count-1 do
50.       if(SelNatSup.Text=FormSaisMvtEmp.SelNatSup.Items[i]) then
51.          IdSupTyp:=i;
52.    //Parametres de la Requete
53.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewOp;
54.    DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := IdOpTyp;
55.    DataModule1.SQLQuery1.ParamByName('Chp3i').AsInteger := IdSupTyp;
56.    DataModule1.SQLQuery1.ParamByName('Chp4s').AsString := SaisNatRef.Text;
57.    DataModule1.SQLQuery1.ParamByName('Chp5d').AsDate := SelDateCPT.Date;
58.    DataModule1.SQLQuery1.ParamByName('Chp6d').AsDate := SelDateValid.Date;
59.    DataModule1.SQLQuery1.ParamByName('Chp7d').AsDate := SelDateSais.Date;
60.    DataModule1.SQLQuery1.ParamByName('Chp8d').AsDate := SelDateEffet.Date;
61.    DataModule1.SQLQuery1.ParamByName('Chp9b').AsString := Observations.Text;
62.    //Exécution de la Requete
63.    DataModule1.SQLQuery1.ExecSQL;
64.  
65.    //++++++++++++++++++++++++++++++Enregistrement dans la Table Historique
66.    NoNewHistOp:=StrToInt(DerNoHistOp);
67.    NoNewHistOp:=NoNewHistOp+1;
68.    if DataModule1.SQLQuery1.Active then
69.       DataModule1.SQLQuery1.Close;
70.    DataModule1.SQLQuery1.SQL.Clear;
71.  
72.    //DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt,IdItem,Date,Heure,Domaine,User,Profil,Table) VALUES (:Chp1i, :Chp2i, :Chp3d, :Chp4t, :Chp5s, :Chp6s, :Chp7s, :Chp8s);');
73.    DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt) VALUES (:Chp1i);');
74.  
75.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
76.    //DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := NoNewOp;
77.    //DataModule1.SQLQuery1.ParamByName('Chp3d').AsDate := Date;
78.    //DataModule1.SQLQuery1.ParamByName('Chp4t').AsString := FormatDateTime('hh:nn:ss', Time);
79.    //DataModule1.SQLQuery1.ParamByName('Chp5s').AsString := 'DGFIP';
80.    //DataModule1.SQLQuery1.ParamByName('Chp6s').AsString := UserEnCours;
81.    //DataModule1.SQLQuery1.ParamByName('Chp7s').AsString := 'AdmTemp';
82.    //DataModule1.SQLQuery1.ParamByName('Chp8s').AsString := 'eoperations';
83.  
84.    try
85.    DataModule1.SQLQuery1.Active:=false;
86.    DataModule1.SQLQuery1.ExecSQL;
87.    //DataModule1.SQLQuery1.Active:=true;
88.  
89.    DataModule1.SQLConnection1.Connected := false;
90.    except
91.      on e : exception do
92.      ShowMessage('Erreur: '+e.Message);
93.    end;
94.  
95.    Application.MessageBox('La fiche opération a été prise en compte et enregistrée dans l''historique.', 'Alerte Utilisateur');
96.  
97.    end
98. else
99.    begin
100.    Application.MessageBox('Veuillez servir les champs obligatoires!', 'Alerte Utilisateur');
101.    end
102. end;

[pjtuloup]

Thanks, dseligo !

I'm going to look into it all.

[pjtuloup]

It worked !

It is strange when you say you don't use transaction because TSQLConnection class of SQLdb components should emit error 'Transaction not set.'.

In fact, I had a SQLTransaction component on my Datamodule but it was not used because I didn't knew how to use it.

I had to set "Action=CaCommit in the objects Inspector (F11) because in the code the line "DataModule1.SQLTransaction1.Action := CaCommit;" caused an error "identifier not found: CaCommit".

But It worked.

I still don't know why it didn't work without Transaction, which I didn't use because I didn't know how to use it; but I'm going to dig into the subject.
And thank you again because without you I was at a dead end and ready to give up definitely.

[dseligo]

I had to set "Action=CaCommit in the objects Inspector (F11) because in the code the line "DataModule1.SQLTransaction1.Action := CaCommit;" caused an error "identifier not found: CaCommit".

Probably you didn't have 'SQLDB' in your uses. But it is same if you set it in Object inspector.

I still don't know why it didn't work without Transaction, which I didn't use because I didn't know how to use it; but I'm going to dig into the subject.

You must use TSQLTransaction with SQLdb components. See https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html and https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.transaction.html.

They are useful i.e. when you are inserting with multiple statements (like you do it in your program).
You can start transaction in the beginning, and then, if error occurs in the middle or in the end, you can rollback and it will be as if you didn't insert anything.
If there is no error, you will commit and all inserts will be 'confirmed' in the database.

And thank you again because without you I was at a dead end and ready to give up definitely.

Np, I am glad it worked.