Recent

Author Topic: Error Insert TSQLQuery Component (SOLVED)  (Read 381 times)

Root2

  • New Member
  • *
  • Posts: 12
Error Insert TSQLQuery Component (SOLVED)
« on: June 27, 2022, 05:28:32 pm »
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.
« Last Edit: June 27, 2022, 10:33:55 pm by Root2 »

Root2

  • New Member
  • *
  • Posts: 12
Re: Error Insert TSQLQuery Component
« Reply #1 on: June 27, 2022, 07:11:45 pm »
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

  • Hero Member
  • *****
  • Posts: 4816
Re: Error Insert TSQLQuery Component
« Reply #2 on: June 27, 2022, 07:48:15 pm »
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

  • Hero Member
  • *****
  • Posts: 651
Re: Error Insert TSQLQuery Component
« Reply #3 on: June 27, 2022, 07:58:51 pm »
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

  • New Member
  • *
  • Posts: 12
Re: Error Insert TSQLQuery Component
« Reply #4 on: June 27, 2022, 08:16:09 pm »
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

  • Hero Member
  • *****
  • Posts: 4816
Re: Error Insert TSQLQuery Component
« Reply #5 on: June 27, 2022, 08:24:07 pm »
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

  • New Member
  • *
  • Posts: 12
Re: Error Insert TSQLQuery Component
« Reply #6 on: June 27, 2022, 08:36:06 pm »
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

  • New Member
  • *
  • Posts: 12
Re: Error Insert TSQLQuery Component
« Reply #7 on: June 27, 2022, 08:39:37 pm »
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.
« Last Edit: June 27, 2022, 08:52:58 pm by Root2 »

Root2

  • New Member
  • *
  • Posts: 12
Re: Error Insert TSQLQuery Component
« Reply #8 on: June 27, 2022, 09:30:43 pm »
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

  • Hero Member
  • *****
  • Posts: 4816
Re: Error Insert TSQLQuery Component
« Reply #9 on: June 27, 2022, 09:38:21 pm »
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.
« Last Edit: June 27, 2022, 09:41:16 pm by rvk »

Root2

  • New Member
  • *
  • Posts: 12
Re: Error Insert TSQLQuery Component
« Reply #10 on: June 27, 2022, 10:32:38 pm »
WOW¡, thank you very much, it works perfectly, now I just need to add the other records to start testing, thanks again.

 

TinyPortal © 2005-2018