[SOLVED] Updating table using UpdateSQL

[madref]

I am trying to update a table with he following code:

Code: [Select]


    dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = ' + IntToStr(Volgorde_Vorige) + ' ' +
                                  'WHERE Divisie_ID = ' + IntToStr(ID_Huidige) + ';';
    // Zet de juiste volgeorde bij het huidige ID
    TQ_Divisies.UpdateSQL.Clear;
    TQ_Divisies.UpdateSQL.Text := dgs;


    // Zet de juiste volgeorde bij het voorgaande ID
    dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = ' + IntToStr(Volgorde_Huidige) + ' ' +
                                  'WHERE Divisie_ID = ' + IntToStr(ID_Vorige) + ';';
    TQ_Divisies.UpdateSQL.Add (dgs);
    TQ_Divisies.Post;
    TQ_Divisies.ApplyUpdates;


    if Form_Information.transRefereeDB.Active then
      Form_Information.transRefereeDB.CommitRetaining;


But i am getting an error: Raised Exception Class 'Unknown'


What is wrong with this code?

[rvk]

You are constructing a SQL-update statement with direct values (Volgorde_Vorige / ID_Huidige). In that case you shouldn't use the UpdateSQL. The UpdateSQL is for records you retrieve and values you change after that (with .Edit and .Post). So either your UpdateSQL should have parameters (Like :volgorde_vorige and :OLD_Id) or you should just execute the statement directly.

For direct execution:

Code: [Select]

  // Zet de juiste volgeorde bij het voorgaande ID
  dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = ' + IntToStr(Volgorde_Huidige) + ' ' +
                                'WHERE Divisie_ID = ' + IntToStr(ID_Vorige) + ';';
  TQ_Divisies.SQL.Text := dgs;
  TQ_Divisies.ExecSQL;
  if Form_Information.transRefereeDB.Active then
    Form_Information.transRefereeDB.CommitRetaining;

This will only work if you don't need the TQ_Divisies afterwards because the SQL.Text is changed here.

But why are you not using the default generated UpdateSQL and just do this?

Code: [Select]

TQ_Divisies.Edit;
TQ_Divisies.FieldByName('Divisie_Volgorde').asInteger := Volgorde_Vorige;
TQ_Divisies.Post;
if Form_Information.transRefereeDB.Active then
  Form_Information.transRefereeDB.CommitRetaining;


[madref]

Beacuse i am trying to change 2 values at once.
The value that is being pointed to AND the previous one


and i am trying to do it with UPDATESQL instead of the SQL

[rvk]

Ah, I now notice the Add in "UpdateSQL.Add (dgs);"

But that won't work. The UpdateSQL (and SQL.Text for ExecSQL) only accepts one statement.

You can't change two records with one statement.

You can execute them after each other and if one fails you could do a rollback.

I also wouldn't use the TQ_Divisies for this purpose but would use a separate TSQLQuery. (You could also use TSQLScript but I think it's overkill for this situation.)

Code: [Select]

var
  dgs: string;
  TempSql: TSQLQuery;
begin
  TempSql := TSQLQuery.Create(nil);
  try
    TempSql.DataBase := TQ_Divisies.Database;
    TempSql.Transaction := TQ_Divisies.Transaction;
    // I take it Transaction of TempSql is the same as Form_Information.transRefereeDB
    // otherwise you would need:
    // TempSql.Transaction := Form_Information.transRefereeDB;

    dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = %d WHERE Divisie_ID = %d;';
    // only one string because we're using format later on

    TempSql.SQL.Text := format(dgs, [Volgorde_Vorige, ID_Huidige]);
    TempSql.ExecSQL;

    TempSql.SQL.Text := format(dgs, [Volgorde_Huidige, ID_Vorige]);
    TempSql.ExecSQL;

    if TempSql.Transaction.Active then
      TSQLTransaction(TempSql.Transaction).CommitRetaining;

  finally
    TempSql.Free;
  end;

end;