[SOLVED] Switching problem with table
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2015-06-16 15:56

This is my table
Code: [Select]    // tbl_Divisies
    cSQL := '';
    cSQL := cSQL + 'CREATE TABLE tbl_Divisie ';
    cSQL := cSQL + '(';
    cSQL := cSQL + 'Divisie_ID Word PRIMARY KEY ASC,';
    cSQL := cSQL + 'Divisie_Tekst varchar(25),';
    cSQL := cSQL + 'Divisie_Gebruiken Boolean,';
    cSQL := cSQL + 'Divisie_Volgorde Word,';
    cSQL := cSQL + 'Divisie_Voluit varchar(75),';
    cSQL := cSQL + 'Divisie_Vergoeding Float';
    cSQL := cSQL + ');';
    connRefereeDB.ExecuteDirect(cSQL);

In the attachment is a screen dump of the table as its filled.


With this code i switch to division from there place.
Code: [Select]procedure TForm_Divisies.BT_DivisieOmhoogClick(Sender:
TObject);
var dis: integer;
    ID_Huidige, ID_Vorige: integer;
    Volgorde_Huidige, Volgorde_Vorige: integer;
    dgs, cSQL: string;
    TempSql: TSQLQuery;
    Row, RowCount, x: Integer;
begin
  if CheckBox_Tonen.Checked = True then dgs := '1'
                                   else dgs := '0';
  dis := StrToIntDef(MLookup('MIN(Divisie_Volgorde)','tbl_Divisie','AB
S(Divisie_Gebruiken)=' + dgs), 0);
  ID_Huidige := TQ_Divisies.FieldByName('Divisie_ID').AsInteger;
  if ID_Huidige <= dis then
    Form_Message.MsgWindow(mInfo, bOk, 'The division is already at' +
                  chr(13) + chr (10) + 'the top of the list !!',
Prgnaam)
  else begin
    Volgorde_Huidige :=
TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger;
    Volgorde_Vorige := Volgorde_Huidige - 1;
    ID_Vorige := MLookUp('Divisie_ID', 'tbl_Divisie' ,
'(ABS(Divisie_Gebruiken)=' + dgs + ') AND (Divisie_Volgorde=' +
IntToStr(Volgorde_Vorige) + ')');
    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;


      cSQL := '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(cSQL, [Volgorde_Vorige, ID_Huidige]);
      TempSql.ExecSQL;


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


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


    finally
      TempSql.Free;
    end;  // try
    Row := TCrackDBGrid(DBGrid_Overzicht).Row;
    RowCount := TCrackDBGrid(DBGrid_Overzicht).VisibleRowCount;
    x := -(Row - 1);
    if Row <= RowCount div 2 then x := RowCount - Row;
    TQ_Divisies.Refresh;
    TQ_Divisies.Locate('Divisie_ID', ID_Huidige, []);
    x := DBGrid_Overzicht.DataSource.DataSet.MoveBy(x);
    x := DBGrid_Overzicht.DataSource.DataSet.MoveBy(-x);
  end;  // if
end;     // BT_DivisieOmhoogClick

But here's the problem when the Divisie_Volgorde=11 it should switch
with Divisie_Volgorde=5 but it switches with Divisie_Volgorde=10.
How can i correct this problem?

----------------------------------------------------------------------
rvk · 2015-06-16 17:14

Well, you have this code:
Code: [Select]    Volgorde_Huidige :=
TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger;
    Volgorde_Vorige := Volgorde_Huidige - 1;

So your Volgorde_Vorige is always Vorgorde_Huidige minus 1. So 11 - 1
= 10.

In the next line you do a lookup for ID_Vorige:
Code: [Select]    ID_Vorige := MLookUp('Divisie_ID', 'tbl_Divisie' ,
'(ABS(Divisie_Gebruiken)=' + dgs + ') AND (Divisie_Volgorde=' +
IntToStr(Volgorde_Vorige) + ')');
You should also do a lookup of what the Divisie_Volgorde is of that
ID_Vorige and you need to switch with that number (and not with the
number -1)

Why not just use .Prior in the TQ_Divisies, get the ID_Vorige and
Divisie_Volgorde and do change these and do .Next and change these.

I've recently given an example... let me look...
Here it is with a small example:
http://forum.lazarus.freepascal.org/index.php/topic,28183.msg175671.ht
ml#msg175671

----------------------------------------------------------------------
madref · 2015-06-16 18:35

Here is the complete finish procedure
Code: [Select]procedure TForm_Divisies.BT_DivisieOmhoogClick(Sender:
TObject);
var ID_Huidige, ID_Vorige: integer;
    Volgorde_Huidige, Volgorde_Vorige: integer;
    cSQL: string;
    TempSql: TSQLQuery;
begin
  ID_Huidige := TQ_Divisies.FieldByName('Divisie_ID').AsInteger;
  Volgorde_Huidige :=
TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger;
  TQ_Divisies.Prior;
  if TQ_Divisies.BOF then
    Form_Message.MsgWindow(mInfo, bOk, 'The division is already at' +
                  chr(13) + chr (10) + 'the top of the list !!',
Prgnaam)
  else begin
    ID_Vorige := TQ_Divisies.FieldByName('Divisie_ID').AsInteger;
    Volgorde_Vorige :=
TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger;
    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;
      cSQL := '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(cSQL, [Volgorde_Vorige, ID_Huidige]);
      TempSql.ExecSQL;
      TempSql.SQL.Text := format(cSQL, [Volgorde_Huidige, ID_Vorige]);
      TempSql.ExecSQL;
      if TempSql.Transaction.Active then
        TSQLTransaction(TempSql.Transaction).CommitRetaining;
    finally
      TempSql.Free;
    end;  // try
    TQ_Divisies.Refresh;
    TQ_Divisies.Locate('Divisie_ID', ID_Huidige, []);
  end;  // if
end;     // BT_DivisieOmhoogClick
[/code/

----------------------------------------------------------------------
rvk · 2015-06-17 07:42

Quote from: madref on June 16, 2015, 08:35:27 pmHere is the complete
finish procedure
Looks good

But now that I see the whole procedure I think it can be made even
simpler (without the TempSQL) if your TQ_Divisies has an UpdateSQL.
(like I showed in the other topic)

Code: [Select]procedure TForm_Divisies.BT_DivisieOmhoogClick(Sender:
TObject);
var
  Huidige, Vorige: integer;
begin
  Huidige := TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger;
  TQ_Divisies.Prior;
  if TQ_Divisies.BOF then
    Form_Message.MsgWindow(mInfo, bOk, 'The division is already at' +
      chr(13) + chr(10) + 'the top of the list !!', Prgnaam)
  else
  begin
    Vorige := TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger;
    TQ_Divisies.Edit;
    TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger := Huidige;
    TQ_Divisies.Post;
    TQ_Divisies.Next;
    TQ_Divisies.Edit;
    TQ_Divisies.FieldByName('Divisie_Volgorde').AsInteger := Vorige;
    TQ_Divisies.Post;
    TQ_Divisies.ApplyUpdates;
    if TQ_Divisies.Transaction.Active then
      TSQLTransaction(TQ_Divisies.Transaction).CommitRetaining;
  end;  // if
end;     // BT_DivisieOmhoogClick

Because of the .Prior and .Next it's probably not even necessary to do
a refresh and lookup because you changed the data in the datasource of
the DBGrid itself (and nog via a separate SQLQuery).

(If you see the DBGrid flickering, that's can be avoided too, so if it
does let me know)