Error Insert TSQLQuery Component (SOLVED)

[Root2]

Dear community, please could you help me with an error that I get when saving data, I am using the component "TSQLQuery" the code is this:

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

1. procedure TForm1.Button4Click(Sender: TObject);
2. begin
3.   SQLQuery1.Close;
4.   SQLQuery1.Clear;
5.   SQLQuery1.SQL.Add('insert into tdtag (dirtag,valtagint,nametag,typedata)');
6.   SQLQuery1.SQL.Add('values (:dirtag,:valtagint,:nametag,:typedata)');  
7.   SQLQuery1.ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress;
8.   SQLQuery1.ParamByName('valtagint').AsFloat:=PLCTagNumber1.Value;  
9.   SQLQuery1.ParamByName('nametag').AsString:=PLCTagNumber1.Name;
10.   SQLQuery1.ParamByName('typedata').AsString:='I';  
11.   SQLQuery1.Open;
12. end;
13.  

When I compile it doesn't give any error, but when I press on the save button I get this message:

"the project has thrown exception epqdatabaseerror preparation query failed, no parameter $1 line 3 values ($1,$2,$3)"

thank you very much for your time and attention.

[Root2]

I found this example:

https://lists.lazarus-ide.org/pipermail/lazarus-es/2009-September/001358.html

I have adjusted it to my requirement, it doesn't give me any error, but when I press the save button it does nothing, it doesn't save.
This is the code:

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

1. procedure TForm1.Button4Click(Sender: TObject);
2. var
3.   consulta: String;
4. begin
5.   consulta:='insert into tdatostag (dirtag,valortagint,nombretag,tipodato) values (:dirtag,:valortagint,:nombretag,:tipodato)';
6.   SQLQuery1.Close;
7.   SQLQuery1.SQL.Clear;
8.   SQLQuery1.SQL.Add(consulta);
9.   with SQLQuery1.Params do begin
10.         ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress;
11.         ParamByName('valortagint').AsFloat:=PLCTagNumber1.Value;
12.         ParamByName('nombretag').AsString:=PLCTagNumber1.Name;
13.         ParamByName('tipodato').AsString:='F';
14.   end;
15.   SQLQuery1.ExecSQL;
16. end;
17.  

Thank you very much for your kind attention.

Best regards.

[rvk]

I have adjusted it to my requirement, it doesn't give me any error, but when I press the save button it does nothing, it doesn't save.

How do you know nothing is saved? I don't see any reading of records here.

Also, how are you handling transaction (and what db are you using etc).
If the transaction isn't handled it could be rolled back at the end if your program, removing any changes you made.

[dseligo]

What rvk said is correct, so check that.

I just want to say that in your first example you should have 'ExecSQL' instead of 'Open', like this:

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

1. procedure TForm1.Button4Click(Sender: TObject);
2. begin
3.   SQLQuery1.Close;
4.   SQLQuery1.Clear;
5.   SQLQuery1.SQL.Add('insert into tdtag (dirtag,valtagint,nametag,typedata)');
6.   SQLQuery1.SQL.Add('values (:dirtag,:valtagint,:nametag,:typedata)');  
7.   SQLQuery1.ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress;
8.   SQLQuery1.ParamByName('valtagint').AsFloat:=PLCTagNumber1.Value;  
9.   SQLQuery1.ParamByName('nametag').AsString:=PLCTagNumber1.Name;
10.   SQLQuery1.ParamByName('typedata').AsString:='I';  
11.   SQLQuery1.ExecSQL;
12. end;

[Root2]

Hi rvk, thanks for replying.

After doing the test I checked in the database with a Select.
I am using PostgreSQL, the SQLTransacction action is caRollBack, the lazarus version is 2.2.2.2, I also include a link to the project in case this helps.

https://we.tl/t-oUHUuc9c2x

Best regards.

[rvk]

I am using PostgreSQL, the SQLTransacction action is caRollBack,

So, the default action is rollback.

Did you commit the transaction somehow (via button, tdbnavigator) or in code via transaction.commit?

(I did not look at your code yet)

[Root2]

dseligo, thanks for replying, I tried that and I get this error: "no parameter $1 values($1,$2,$3,$4)", although when reviewing more in detail the query I found a small error related to the number of columns, I corrected the error however it still gives error, anyway I would like to make the insert work with the second example as that will allow me to insert several records to the same fields which is what I want to do.

[Root2]

I think I misunderstood, but I don't think I'm using commit anywhere.

I modified the default action, I set it to caCommit, now the message I get is:
"Cannot open a non-select statement"

Regards.

[Root2]

it works, it was only to modify it to be commit, I didn't save correctly, I checked again and it worked, thank you very much.
Now I have a question about this mode, can I save several records at the same time with this mode using the following code?

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

1. procedure TForm1.Button4Click(Sender: TObject);
2. var
3.   consulta: String;
4. begin
5.   consulta:='insert into tdatostag (dirtag,valortagint,nombretag,tipodato) values (:dirtag,:valortagint,:nombretag,:tipodato)';
6.   SQLQuery1.Close;
7.   SQLQuery1.SQL.Clear;
8.   SQLQuery1.SQL.Add(consulta);
9.   with SQLQuery1.Params do begin
10.         ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress;
11.         ParamByName('valortagint').AsFloat:=PLCTagNumber1.Value;
12.         ParamByName('nombretag').AsString:=PLCTagNumber1.Name;
13.         ParamByName('tipodato').AsString:='F';
14.  
15.         ParamByName('dirtag').AsInteger:=PLCTagNumber2.MemAddress;
16.         ParamByName('valortagint').AsFloat:=PLCTagNumber2.Value;
17.         ParamByName('nombretag').AsString:=PLCTagNumber2.Name;
18.         ParamByName('tipodato').AsString:='I';
19.   end;
20.   SQLQuery1.ExecSQL;
21. end;
22.  

all are the same data types.

Thank you very much.

[rvk]

it works, it was only to modify it to be commit, I didn't save correctly, I checked again and it worked, thank you very much.
Now I have a question about this mode, can I save several records at the same time with this mode using the following code?

No, but you can do it like this: (each insert needs it's own ExecSQL)

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

1. procedure TForm1.Button4Click(Sender: TObject);
2. var
3.   consulta: String;
4. begin
5.   consulta:='insert into tdatostag (dirtag,valortagint,nombretag,tipodato) values (:dirtag,:valortagint,:nombretag,:tipodato)';
6.   SQLQuery1.Close;
7.   SQLQuery1.SQL.Clear;
8.   SQLQuery1.SQL.Add(consulta);
9.  
10.   SQLQuery1.ParamByName('dirtag').AsInteger := PLCTagNumber1.MemAddress;
11.   SQLQuery1.ParamByName('valortagint').AsFloat := PLCTagNumber1.Value;
12.   SQLQuery1.ParamByName('nombretag').AsString := PLCTagNumber1.Name;
13.   SQLQuery1.ParamByName('tipodato').AsString := 'F';
14.   SQLQuery1.ExecSQL;
15.  
16.   SQLQuery1.ParamByName('dirtag').AsInteger := PLCTagNumber2.MemAddress;
17.   SQLQuery1.ParamByName('valortagint').AsFloat := PLCTagNumber2.Value;
18.   SQLQuery1.ParamByName('nombretag').AsString := PLCTagNumber2.Name;
19.   SQLQuery1.ParamByName('tipodato').AsString := 'I';
20.   SQLQuery1.ExecSQL;
21.  
22. end;
23.  

(I don't like the with-statement that much but you could leave it in if you like it more)
You can ommit the extra .Params. because TSQLQuery also has the ParamByName function.

[Root2]

WOW¡, thank you very much, it works perfectly, now I just need to add the other records to start testing, thanks again.