[SOLVED] TSqlite3Dataset (or DBGrid) reorder row

[totya]

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]

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]

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

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]

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]

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]

I 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     


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]

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]

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/topic,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!