Recent

Author Topic: [solved] ExecSQL and Applyupdates problem  (Read 3200 times)

oldcoder

  • New Member
  • *
  • Posts: 13
[solved] ExecSQL and Applyupdates problem
« on: October 20, 2015, 03:28:12 pm »
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;                       
« Last Edit: October 22, 2015, 12:20:30 pm by oldcoder »

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: ExecSQL and Applyupdates problem
« Reply #1 on: October 21, 2015, 07:38:09 am »
If you construct SQL INSERT statement and execute it using ExecSQL then you do not need ApplyUpdates. (ApplyUpdates expects active dataset ... in your case SQLQuery3)

ApplyUpdates send to SQL server updates done in local dataset by TDataSet.Post, TDataSet.Delete etc.

oldcoder

  • New Member
  • *
  • Posts: 13
Re: ExecSQL and Applyupdates problem
« Reply #2 on: October 21, 2015, 11:32:42 pm »
Thanks for your reply LacaK. It helped to clarify what I was doing, and I was able to get the code working as I wanted, but I am still a little confused by the two methods of saving data.
Is it generally thought better to save to a database with SQL Insert or Post statements?

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: ExecSQL and Applyupdates problem
« Reply #3 on: October 22, 2015, 07:15:39 am »
Depend on your application and your needs, usage scenario.
May be the common situation is:

1. you select data using SQLQuery, you get local dataset
2. you browse localy this dataset and/or modify data (add new records, change fields in existing etc.)
3. you ApplyUpdates (send all locally cached changes to database)
4. you Commit changes

So all is handled by one dataset and you are not required to manually construct update statements.

oldcoder

  • New Member
  • *
  • Posts: 13
Re: ExecSQL and Applyupdates problem
« Reply #4 on: October 22, 2015, 12:19:58 pm »
Not having to write complicated SQL update statements is a definite bonus.
Thanks again for your help LacaK.