I have an invoice type string grid in my program, and I have got it saving the contents of the grid to the database OK, but I want now to add error checking code.
Having read forum messages and tutorials over the last 2 days I thought the right thing to do was add all the data in the grid to the SQLQuery using the code below, and when it has all the data from the grid, it should write it to the database with the Applyupdates command.
While the code is reading the grid lines and there is data to process, the ExecSQL statement works fine, but after it has found the last valid data line, ExecSQL appears to close the query, so that when Applyupdates is called after the i loop has finished, it causes the error 'Operation cannot be performed on an inactive dataset'. If I try to open or activate the query before the ApplyUpdates call I get a 'Cannot open a non-select statement' error message.
I have tried all sorts of things from forums and tutorials but I can't get this to work. I'm sure it's something very simple but I just can't find what the answer is.
Can anyone help please?
for i := 0 to SGrid1.Rowcount - 1 do
begin
If SGrid1.Cells[0,i] <> '' then
begin
SQLQuery3.SQL.Clear;
SQLQuery3.SQL.Lines.Add('INSERT INTO `itemsales`(`CUSCODE`, `DOCTYPE`,`DOCNUM`,');
SQLQuery3.SQL.Lines.Add('`QTY`, `ITEMCODE`, `DISCOUNT`, `PRICE`,`ORDERDATE`)');
SQLQuery3.SQL.Lines.Add('VALUES (+CHAR(39)+code.text+CHAR(39)+',');
SQLQuery3.SQL.Lines.Add(CHAR(39)+'Cash Sale'+CHAR(39)+',');
SQLQuery3.SQL.Lines.Add(CHAR(39)+docnum.text+CHAR(39)+',');
SQLQuery3.SQL.Lines.Add(CHAR(39)+SGrid1.Cells[5,i]+CHAR(39)+','); //QTY
SQLQuery3.SQL.Lines.Add(CHAR(39)+SGrid1.Cells[0,i]+CHAR(39)+','); //ITEMCODE
SQLQuery3.SQL.Lines.Add(CHAR(39)+discount.text+CHAR(39)+','); //DISCOUNT
SQLQuery3.SQL.Lines.Add(CHAR(39)+SGrid1.Cells[3,i]+CHAR(39)+','); //PRICE
SQLQuery3.SQL.Lines.Add(CHAR(39)+docdate.text+CHAR(39)+')'); //ORDERDATE
DM1.SQLQuery3.ExecSQL;
end;
end;
try
if DM2.SQLTransaction1.Active then
begin
DM1.SQLQuery3.ApplyUpdates;
DM2.SQLTransaction1.Commit;
end;
except
on E: Exception do
begin
DM2.SQLTransaction1.Rollback;
ShowMessage( E.Message );
end;
end;