[SOLVED] TSqlite3Dataset (or DBGrid) reorder row
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-04-26 08:33

Hi!

What is the simplest way, if I want change order of one row (ex. with
up/down button) via TSqlite3Dataset  or DBGrid?

ex:
1. lol_1
2. lol_2
3  lol_3

after up button is pressed on #2:
2. lol_2
1. lol_1
3  lol_3

Thanks!

----------------------------------------------------------------------
Ocye · 2015-04-26 08:42

Guess there isn't a more simple (and probably none at all) way than
doing it with the query http://www.w3schools.com/sql/sql_orderby.asp

----------------------------------------------------------------------
totya · 2015-04-26 09:09

Quote from: Ocye on April 26, 2015, 10:42:38 amGuess there isn't a
more simple (and probably none at all) way than doing it with the
query http://www.w3schools.com/sql/sql_orderby.asp

Thanks, but I guess this isn't answer for my question. I want move
only the one row (line/record) in database (firstly: in dbgrid) via
up/down button. See example from the first post in this topic.

----------------------------------------------------------------------
rvk · 2015-04-26 10:27

When using a DBGrid you can't control the order. TSQLQuery (and
consequently the database via the ORDER BY cluase) does that for you.
So in your case you would need to change the first number in your
record. In this case you could need to give lol_2 the number of lol_1
and lol_1 the number of lol_2. In that case they will be switched.

Code: [Select]1. lol_2
2. lol_1
3  lol_3
You see the ordering of the first number stays correct.

This will, however, mean you have to change two records in your
database. It's not easy but doable.

First you would need to store the number of the lol_x you're currently
on. Then you would need to do a .prior to get on the previous record.
Also save that number. Change the current record and do a next and
change that record. 
Code: [Select]    num1 :=
SQLQuery1.FieldByName('ordernumber').AsInteger;
    SQLQuery1.Prior;
    if SQLQuery1.BOF then exit;
    num2 := SQLQuery1.FieldByName('ordernumber').AsInteger;
    SQLQuery1.Edit;
    SQLQuery1.FieldByName('ordernumber').AsInteger := num1;
    SQLQuery1.Post;
    SQLQuery1.Next;
    SQLQuery1.Edit;
    SQLQuery1.FieldByName('ordernumber').AsInteger := num2;
    SQLQuery1.Post;
    SQLQuery1.ApplyUpdates;
    SQLTransaction1.CommitRetaining;
    SQLQuery1.Refresh;
    SQLQuery1.Locate('ordernumber', num2,[]);

I have attached a small example-project. It will create a test.db
(SQLite3) and shows you exactly what you asked for

Of course you can hide the ordernumber-field and only show the fields
you find relevant. (ordernumber does need to be in the select-
statement though to facilitate the ordering and changing of that
ordering)

If you have questions, don't hesitate to ask.

----------------------------------------------------------------------
totya · 2015-04-26 11:06

Thanks rvk!

Your code is works. I think before simpler solution is exists (with
SQL command or any way)

Okay, I can see, you change (swap) the order number fields only. This
is okay. But I can't undersand, why swapped the two rows. I can't see
SQL command in your code similar of this:

Code: [Select]'SELECT * FROM testtable ORDER BY ordernumber ASC;'

----------------------------------------------------------------------
rvk · 2015-04-26 11:15

Quote from: totya on April 26, 2015, 01:06:47 pmI can't see SQL
command in your code similar of this:
Code: [Select]'SELECT * FROM testtable ORDER BY ordernumber ASC;'
It's in the SQL-property of SQLQeury1. I set it at design-time. You
could also do that in run-time but for this example I did it in
design-time. See below for the image where I did it.
Code: [Select]select
*
from
testtable
order by ordernumber     

Quote from: totya on April 26, 2015, 01:06:47 pm think before simpler
solution is exists (with SQL command or any way)
Yeah, unfortunately when dealing with a DBGrid all the fields and
ordering are done by the database. So there is very little options to
manipulate it at the client-side (other than changing the order-field
in the records themselves).

Advantage of the method I showed you is that the ordering is kept in
the database. So after exiting your program and starting again the
ordering stays the way you left it the last time. (In my example you
would need to comment out the DeleteFile-line because there I always
build the test.db with each start of the program but obviously you
won't do that in your own program)

----------------------------------------------------------------------
totya · 2015-04-26 11:23

Hi!

Thanks, I found this "missing code" in object inspector
SQLQuery1.SQL.... about one minutes ago, before I read your answer.
Now this is clean now. I'm use TSqlite3Dataset, I try to convert your
idea/code myself...

Very thanks for your help!

----------------------------------------------------------------------
totya · 2015-04-26 13:12

TSqlite3Dataset version:

Code: [Select]unit Unit_Sqlite3Dataset_Utils;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Sqlite3DS;

// Thanks to: rvk, see http://forum.lazarus.freepascal.org/index.php/t
opic,28183.msg175671.html#msg175671
// TSqlite3Dataset version, converted by: totya
procedure Sqlite3DatasetKez_RowUp (const
Sqlite3Dataset:TSqlite3Dataset;
                                   const OrderNumberField: string);

procedure Sqlite3DatasetKez_RowDown (const
Sqlite3Dataset:TSqlite3Dataset;
                                     const OrderNumberField: string);

implementation

procedure Sqlite3DatasetKez_RowUp (const
Sqlite3Dataset:TSqlite3Dataset;
                                   const OrderNumberField: string);
var
  num1, num2: integer;
begin
  with Sqlite3Dataset do
  begin
    if not(TableExists) then exit;

    num1:= FieldByName(OrderNumberField).AsInteger;

    Prior;
    if BOF then exit;

    num2:= FieldByName(OrderNumberField).AsInteger;

    Edit;
      FieldByName(OrderNumberField).AsInteger:= num1;
    Post;

    Next;

    Edit;
      FieldByName(OrderNumberField).AsInteger:= num2;
    Post;

    ApplyUpdates;
    //SQLTransaction1.CommitRetaining;

    Close;
      SQL:='SELECT * '+
           'FROM '+
           TableName+' '+
           'ORDER BY '
           +OrderNumberField+' '+
           'ASC;';
    Open;

    //Refresh;
    Locate(OrderNumberField, num2,[]);
  end;
end;

procedure Sqlite3DatasetKez_RowDown (const
Sqlite3Dataset:TSqlite3Dataset;
                                     const OrderNumberField: string);
var
  num1, num2: integer;
begin
  with Sqlite3Dataset do
  begin
    if not(TableExists) then exit;

    num1:= FieldByName(OrderNumberField).AsInteger;

    Next;
    if EOF then exit;

    num2:= FieldByName(OrderNumberField).AsInteger;

    Edit;
      FieldByName(OrderNumberField).AsInteger:= num1;
    Post;

    Prior;

    Edit;
      FieldByName(OrderNumberField).AsInteger:= num2;
    Post;

    ApplyUpdates;
    //SQLTransaction1.CommitRetaining;

    Close;
      SQL:='SELECT * '+
           'FROM '+
           TableName+' '+
           'ORDER BY '
           +OrderNumberField+' '+
           'ASC;';
    Open;

    //Refresh;
    Locate(OrderNumberField, num2,[]);
  end;
end;

end.

Thanks again!