Switching indexes in SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2007-12-10 02:12

One more SQLite question.
In my application I have a dbgrid based on a SQLite table and a button
which should change sorting order when clicked.
First click should sort grid on Name ascending and when clicked again
it should change sorting order on the same field but now descending.
I tried following code but it looks as if SQLite doesn't use the ASC
and DESC part of the query and always sorts on Naam ascending.

Code: [Select]
  if SortNaam = 'Ascending'
  then begin
       SortNaam := 'Descending';
       Sqlite3dsAdressen.ExecSQL('SELECT * FROM Adressen ORDER BY Naam
DESC');
       Sqlite3dsAdressen.RefetchData;
       dbgrid1.Refresh;
  end
  else begin
       SortNaam := 'Ascending';
       Sqlite3dsAdressen.ExecSQL('SELECT * FROM Adressen ORDER BY Naam
ASC');
       Sqlite3dsAdressen.RefetchData;
       dbgrid1.Refresh;
  end;


If that is true then how can I change the sorting order of the grid?

I also created two indexes in the SQLite database (Name-Asc and
Name_Desc) to try a different route but I still haven't found a way to
select a SQLite index in code.

Any suggestions are more than welcome!

----------------------------------------------------------------------
LuizAmérico · 2007-12-15 10:41

Modify to this:

if SortNaam = 'Ascending'
  then begin
       SortNaam := 'Descending';
       Sqlite3dsAdressen.SQL := 'SELECT * FROM Adressen ORDER BY Naam
DESC';
       Sqlite3dsAdressen.RefetchData;
  end
  else begin
       SortNaam := 'Ascending';
       Sqlite3dsAdressen.SQL := 'SELECT * FROM Adressen ORDER BY Naam
ASC';
       Sqlite3dsAdressen.RefetchData;
  end;

----------------------------------------------------------------------
JanRoza · 2007-12-15 21:34

Thank you Luiz,

That did the trick.
It's easy once you know it, but before you know it takes some
sweating.

 
Jan