SqlLite3 writing result twice
lazarus / 16-Databases

----------------------------------------------------------------------
davesimplewear · 2010-04-14 22:01

I have a test db with two tables, one called sales, the other called
balance, I use the following sql statement to insert the balance of
the sales table into the balance table, but it results in the balance
table being written to twice with the same answer? where am I going
wrong, or is this a bug?
Code: [Select] TSales.Close;
  TSales.SQL:=('Insert into Balance (Bal) select TOTAL(price) from
sales');
  TSales.Open;

Regards
Dave

----------------------------------------------------------------------
LuizAmérico · 2010-04-14 22:25

To call a insert command is necessary to use ExecSQL instead of Open.

I would recommend you test this command in a Sqlite database manager
like sqlitestudio or sqliteman to see if is the expected behavior

----------------------------------------------------------------------
davesimplewear · 2010-04-14 23:08

Thank you Luiz, that worked, but the total won't display until the
program is restarted, I have tried the following,
Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
  TSales.Close;
  TSales.ExecSQL('Insert into Balance (Bal) select TOTAL(price) from
sales');
  TSales.ExecSQL;
  TSales.Open;
  TBal.ApplyUpdates;
  TBal.Refresh;
end;
Regards
Dave