Recent

Author Topic: Switching indexes in SQLite  (Read 3290 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 585
    • http://www.silentwings.nl
Switching indexes in SQLite
« on: December 10, 2007, 03:12:01 am »
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!
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.10 FPC 3.2.0
       CodeTyphon 7.2 FPC 3.3.1

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
RE: Switching indexes in SQLite
« Reply #1 on: December 15, 2007, 11:41:02 am »
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

  • Hero Member
  • *****
  • Posts: 585
    • http://www.silentwings.nl
Switching indexes in SQLite
« Reply #2 on: December 15, 2007, 10:34:09 pm »
Thank you Luiz,

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

 :D
Jan
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.10 FPC 3.2.0
       CodeTyphon 7.2 FPC 3.3.1

 

TinyPortal © 2005-2018