Dynamic Update using TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
emaza · 2011-11-20 01:13

Hi,

Have been trying to write a  program to edit records in a MS ACCESS
table using TSQLQuery. In the TSQL Tstring window I have a select
statement that locates the record to edit. In the UpdateSQL
TStringList window I can write for example:

UPDATE myTable
SET CashierId = '101'
Money = 250.99
CutZ = True
Date = '05/05/2011'
WHERE IdCut = 5050 //the ID of the record located

and on button Click:
SQLQuery1.Edit
SQLQuery1.Post
SQLQuery1.ApplyUpdates

Wich works fine as a test, but if I try to use variables, for example:
SET Money = :X, and on button click:
SQLQuery1.Params.ParamByName('X').Value:= 250.99; I get a parameter
"X" not found error.

I know this is due to some very basic newbe ignorance, but I have
googled this for weeks and read all Delphi tutorials I could find with
no answer. Will very much appreciate if someone can point me in the
right direction.

Note: I have tried naming X in the Params property window of the
TSQLQuery, but this makes no difference and disapears after compiling.

----------------------------------------------------------------------
ludob · 2011-11-20 09:42

InsertSQL, UpdateSQL and DeleteSQL can only use predefined parameters.
These are :fieldname and :old_fieldname.
The TSQLQuery.Params property is affecting only TSQLQuery.SQL. So
setting SQLQuery1.Params.ParamByName('X').Value will try to find the
parameter :X in SQLQuery1.SQL which isn't there.
To update a record with "random" parameters you have to set the
TSQLQuery.SQL to the update query, assign the parameters and call
TSQLQuery.ExecSQL. You can re-use your SQLQuery1 to do that but this
will close your active query. Better use another TSQLQuery to do this.
In my programs I have always a "spare" TSQLQuery, without any dataset
associated, to run these type of queries.

----------------------------------------------------------------------
emaza · 2011-11-22 05:57

Many thanks for your explanation and solution lodob, I could have
spent more than a couple of months trying to solve this on my own.

I placed another TsqlQuery in the form with the update statement
inside the SQL string property instead of in the UpdateSQL string, to
test it, and it worked fine. Still having problems implementing in the
real app using variables instead of constants, but I think I will soon
get right the proper syntax and number of apostrophes for the string
parameters. By the way, DataType and other Params elements properties
seem not to be implemented yet, although it also seems this makes no
difference.

Thanks again.