Dynamic Update using TSQLQuery

[emaza]

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]

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]

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.