Delphi ClientDataSet Sorting by changing IndexName

Question

I've been learning about the ClientDataSet in delphi and how it can help sort my SQL database. The data is showing fine in my TDBGrid and i've enabled sorting by clicking on a header by changing the IndexField of the ClientDataset. I want to make it go descending on sorts sometimes though so have been trying to use 2 IndexNames outlined here https://stackoverflow.com/a/13130816/4075632

However, when I swap the IndexName from DEFAULT_ORDER to CHANGEINDEX, all data in my DBGrid disappears. I'm pretty new to all of this, and I know it will depend on my situation, but what are some of the ways this happens and I will try to troubleshoot them.

I have 1 TSQLConnection connected to TSQLQuery, and that connected to TDataSetProvider, and that connected to my ClientDataSet which leads to a TDataSource to the TDBGrid. Why might the ClientDataSet which is usually fine cause problems when I change its name? Please bear in mind that most of the settings are default because I'm not too sure about these components. Thanks, I hope you can provide some useful help and I'm sorry it may be difficult to se my situation.

Toby

score 3 · Accepted Answer · answered Jan 22 '15 at 10:00

3

I use the following code to build indexes for a clientdataset:

Procedure BuildIndices (cds: TClientDataSet);
var
 i, j: integer;
 alist: tstrings;

begin
 with cds do
  begin
   open;
   logchanges:= false;
   for i:= 0 to FieldCount - 1 do
    if fields[i].fieldkind <> fkCalculated then
     begin
      j:= i * 2;
      addindex ('idx' + inttostr (j), fieldlist.strings[i], [], '', '',  0);
      addindex ('idx' + inttostr (j+1), fieldlist.strings[i], [ixDescending], '', '', 0);
     end;
   alist:= tstringlist.create;
   getindexnames (alist);
   alist.free;
   close;
  end;
end;

As a result, there is an index 'idx0' for sorting column 0 ascending and 'idx1' for sorting column 0 descending; 'idx2' and 'idx3' for column 1, etc.

Then, in the grid's OnTitleClick event, I have the following

procedure Txxx.DBGrid1TitleClick(Column: TColumn);
var
 n, ex: word;

begin
 n:= column.Index;
 try
  dbgrid1.columns[prevcol].title.font.color:= clNavy
 except
 end;

 dbgrid1.columns[n].title.font.color:= clRed;
 prevcol:= n;
 directions[n]:= not directions[n];
 ex:= n * 2;
 if directions[n] then inc (ex);
 with clientdataset do
  try
   disablecontrols;
   indexname:= 'idx' + inttostr (ex);
  finally
   first;
   enablecontrols
  end;
end;

In each form, I define an array of booleans ('directions'), one element per grid column. These elements track whether a column should be sorted ascending or descending.

answered Jan 22 '15 at 10:00

No'am Newman

6,395
5
38
50

Thank you! Nearly there, but what is prevcoI? Im getting undeclared variable errors. Thanks – Toby Fox Jan 22 '15 at 15:05
@TobyFox: prevcol is an integer variable belonging to the form (forgot about that). It stores the value of the previous selected column. – No'am Newman Jan 22 '15 at 15:46
Hi thanks. I'm sure this will work because it seems right, but my database table is still not showing and every time I click on a heading I get error "cannot perform action on closed dataset". So i suppose my original problem still stands. Data isnt being shown in the DBGrid when the name of the CDS is anything other than the default. What thinks should I check? Thanks so much, Toby – Toby Fox Jan 22 '15 at 16:39
Ok I now know it breaks after this line: `with CustomerCDS do try disablecontrols; IndexName:= 'idx' + inttostr (AscDescSetBegin); showmessage('First Part'); finally first;` What is it with "first" that causes that message? Thanks – Toby Fox Jan 22 '15 at 16:51
@TobyFox: Judging by your comments, the dataset is closed - you should open it after the 'buildindices' procedure in order to display the data, or possibly remove the 'close' line from 'buildindices'. 'First' causes the dataset to be displayed from the first record in the new sorting order. – No'am Newman Jan 22 '15 at 19:25
Thank you so much! That works absolutely perfectly. I really appreciate that! – Toby Fox Jan 22 '15 at 19:54

score 0 · Answer 2 · answered Jan 22 '15 at 07:48

The ClientDataSet comes with two predefined indexes: DEFAULT_ORDER and CHANGEINDEX, which are of no real use for your task, because you cannot tweak them to your needs. So you have to create your own indexes. A comprehensive description by Cary Jensen can be found in this article as well as in his highly recommended book about ClientDataSets.

Delphi ClientDataSet Sorting by changing IndexName

2 Answers2