procedure TSQLForm.ResultsGridTitleClick(Column: TColumn);
var
ASC_IndexName,DESC_IndexName:string;
begin
ASC_IndexName:='ASC_'+Column.FieldName;
DESC_IndexName:='DESC_'+Column.FieldName;
//indexes cant sort binary types such as ftmemo, should add blob types to this as well
if(Column.Field.DataType = ftmemo) or (Column.Field.DataType = ftwidememo)then Exit;
//check if a Ascending index already exists for this column, if not create one
if OpenQuery.IndexDefs.IndexOf(ASC_IndexName) = -1 then
openquery.AddIndex(ASC_IndexName,column.FieldName,[]);
//check if a Descending index already exists for this column, if not create one
if OpenQuery.IndexDefs.IndexOf(DESC_IndexName) = -1 then
openquery.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]);
//ensure index defs are up to date
OpenQuery.IndexDefs.Updated:=false; {<<<----It won't work without this line}
OpenQuery.IndexDefs.Update;
//use the column tag to toggle ASC/DESC
column.tag := not column.tag;
if boolean(column.tag) then
begin
Column.Title.ImageIndex:=12;
openquery.IndexName:=ASC_IndexName;
end else
begin
Column.Title.ImageIndex:=13;
OpenQuery.IndexName:=DESC_IndexName;
end;
if (flastcolumn <> nil) and (flastcolumn <> Column) then
flastcolumn.Title.ImageIndex:=-1;
flastcolumn:=column;
end;
procedure TSQLForm.ResultsGridTitleClick(Column: TColumn);
var
ASC_IndexName,DESC_IndexName:string;
x:integer;
aindex:TIndexDef;
begin
ASC_IndexName:='ASC_';
DESC_IndexName:='DESC_';
//indexes cant sort binary types such as ftmemo, should add blob types to this as well
if(Column.Field.DataType = ftmemo) or (Column.Field.DataType = ftwidememo)then Exit;
//check if a Ascending index already exists for this column, if not create one
x:=OpenQuery.IndexDefs.IndexOf(ASC_IndexName);
if x = -1 then
openquery.AddIndex(ASC_IndexName,column.FieldName,[]) else
begin
aindex:= openquery.IndexDefs.Items[x];
aindex.Fields:=column.FieldName;
end;
//check if a Descending index already exists for this column, if not create one
x:= OpenQuery.IndexDefs.IndexOf(DESC_IndexName);
if x = -1 then
openquery.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]) else
begin
aindex:= openquery.IndexDefs.Items[x];
aindex.Fields:=column.FieldName;
end;
//ensure index defs are up to date
OpenQuery.IndexDefs.Updated:=false; {<<<<---Won't work without this line}
openquery.IndexDefs.Update;
//use the column tag to toggle ASC/DESC
column.tag := not column.tag;
if boolean(column.tag) then
begin
Column.Title.ImageIndex:=12;
openquery.IndexName:=ASC_IndexName;
end else
begin
Column.Title.ImageIndex:=13;
OpenQuery.IndexName:=DESC_IndexName;
end;
if (flastcolumn <> nil) and (flastcolumn <> Column) then
flastcolumn.Title.ImageIndex:=-1;
flastcolumn:=column;
end;
openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;
Try this:Code: [Select]openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )
Hi, Snorkel, I tried to use this enhanced wiki code with showing up and down arrow (Column.Title.ImageIndex:=ImageArrowDown and Column.Title.ImageIndex:=ImageArrowUp respectively) on sorting a column, but no arrow is shown.Try this:Code: [Select]openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )
That did the trick. Sorting works now with the original version I posted.
Thanks Lacak2, I just assumed that update would update it regardless.
I will update the wiki with my example.
Thanks again for helping me with this, much appreciated.
I updated the wiki for the original example by adding this line to it:
IndexDefs.Updated:=false; {<<<<---This line is critical as IndexDefs.Update will do nothing on the next sort if it's already true}
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSet (http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSet)
I have used just this code in my application, compiled with Lazarus 2.0.4 for Windows.
It was nice that when column callid is once sorted, newly loaded data becomes sorted, too.
It work fine for a couple of years, until I have upgraded Lazarus to 2.0.10.
Now it works the very first time only, attempt to load new data causes exception
DBQuery1: Index 'ASC_callid' not found.
I didn't investigate how the indexes work. Maybe there is quite a simple idea how to bring this code back to life?