TSQLQuery InsertSQL does not accept params.
lazarus / 16-Databases

----------------------------------------------------------------------
jack · 2012-08-02 19:48

It seems that one cannot set ParamByName in the InsertSQL. <<Error
Parameter Not Found>>

How then might one make use of the InsertSQL property of TSQLQuery?

----------------------------------------------------------------------
Knipfty · 2012-08-02 19:58

Hi Jack,

Can you provide an example of the SQL you are using to do the insert? 
Are you inserting 1 record or multiple?

Thanks

Knipfty

----------------------------------------------------------------------
jack · 2012-08-03 01:00

To answer your question directly:
Here is the code that handles my save button:
====================================================
procedure TForm1.SaveClick(Sender: TObject);
begin
  // Save Button
  // Fill in Insert Alternative
  SQLQuery1.Params.ParamByName('aid').AsInteger:=10;
  SQLQuery1.Params.ParamByName('aname').AsString:='Hogwarts';
  SQLQuery1.Params.ParamByName('asize').AsString:='Medium';
  //
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
  SQLQuery1.Active:=true;
end;
==========================================================
The SQLQuery1.SQL is:
SELECT * FROM  ANIMALS ORDER BY A_NAME
==========================================================
The SQLQuery1.InsertSQL is:
INSERT INTO ANIMALS
(A_ID, A_NAME, A_SIZE)
VALUES
(:aid, :aname, :asize);
==========================================================
The Failure occurs when the
SQLQuery1.Params.ParamByName('aid').AsInteger:=10;
is executed.

----------------------------------------------------------------------
Lacak2 · 2012-08-03 06:02

You can not set param values in InsertSQL using ParamByName ...
(only in SQL)
Params in InsertSQL are handled automaticaly by sqlDB in ApplyUpdates
and are set using values in record which you are applied.

So in you example you should:
with SQLQuery1 do begin
  Edit;
  FieldByName('aid').AsInteger := 10;
  ...
  Post;
  ApplyUpdates;
end;
...

IIRC there is also some topic about this in wiki

----------------------------------------------------------------------
jack · 2012-08-06 01:52

Thanks I'll give this a try.

Appreciate the help!