'index is being used to order table' error

This is a nasty one to try to fix.   Here shoots:



My dbgrid will return a value of Ascending (boolean) for if its being sorted asc or desc.    When I click to sort desc it works, when I click again to sort asc it returns that error and dies to the XXXX'd line below
begin

 if dbsortable = TRUE then begin

  if Ascending then
begin



   clientdataset1.AddIndex(Sortkind1,DBGridView1.Columns[ACol].FieldName,[ixExpression]);
XXXXX   clientdataset1.IndexName:= Sortkind1;


   end;

   if not Ascending then
begin



   clientdataset1.AddIndex(Sortkind1,DBGridView1.Columns[ACol].FieldName, [ixExpression,ixDescending]);
   clientdataset1.IndexName:= Sortkind1;




end;

end;

end;


I'm positive there is an obvious reason for this but its beyong me.  Any ideas?   also, if anyone has a fix for it sorting numbers badly (100000 is before 2 and 300 is before 4 etc) I could use it.


LVL 1
hibbidijiAsked:
Who is Participating?
 
pcsentinelCommented:
Ok well the
clientdataset1.IndexFieldNames :='';

is clearing out the previously set index, i.e. removing the index

ClientDataset1.AddIndex(DBGridView1.Columns[ACol].FieldName+'_asc', DBGridView1.Columns[ACol].FieldName,[]);
clientdataset1.IndexName := DBGridView1.Columns[ACol].FieldName+'_asc';

creates your ascending index and applies it.

the
       ClientDataset1.AddIndex(DBGridView1.Columns[ACol].FieldName+'_desc', DBGridView1.Columns[ACol].FieldName, [ixDescending]);
clientdataset1.IndexName := DBGridView1.Columns[ACol].FieldName+'_desc';

creates your descending index and applies it.

The odd thing about this line is that it does not declare any descending fields, but hey if it works great.

Something I would like you to check out though, just for completeness, after each change check the
ClientDataset1.IndexDefs.Items.Count and make sure it is not continually going up

regards
0
 
pcsentinelCommented:
You shouldn't be adding indexes on a button click.

Try just adding the 2 indexes when you open the table and then switching between the 2 indexes on the button click (using clientdataset1.IndexName).

The error may be being caused by trying to create 2 identical indexes.

re the sorting. make sure that the field is actually numeric and not string. sorting on string numbers results in very dodgy ordering

regards

0
 
hibbidijiAuthor Commented:
Ok, so I should create index types for each possible sort (asc and desc) for each sortable column.   What should I put in the events for the sorts?  

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
pcsentinelCommented:
The ideal is to create both asc and desc indexes on each field you want to sort by when you opne the clientdataset.

so for instance
clientdataset1.Open;
clientdataset1.AddIndex(Sortkind0Asc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixExpression]);
clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending,ixExpression],Clientdataset1.Fields[0].Name);

then on event



clientdataset1.IndexName:='Sortkind'+IntTostr(ACol)+'Asc';

or

clientdataset1.IndexName:='Sortkind'+IntTostr(ACol)+'Desc';


hope this helps, just bear in mind there are a number of ways to achieve this

regards


0
 
hibbidijiAuthor Commented:
Your code is giving me the same error.   I have removed all other code relevent to sorting.   When I do that, the app works. When I add your code in, I getthe same error as before.

Ideas?
0
 
pcsentinelCommented:
Ok just try adding the

clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending,ixExpression],Clientdataset1.Fields[0].Name);
clientdataset1.IndexName:='Sortkind'+IntTostr(ACol)+'Desc';


after opening the dataset,

ensure no other indexes are created anywhere else
do you still get the error?
0
 
hibbidijiAuthor Commented:
With that exact code I get an access violation when I attempt to run the app.  Might I point you at another likely abandoned ee question that I asked?
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21406773.html

That will sort ascending, but not descending.

0
 
pcsentinelCommented:
Can you post the code where you are opening the dataset and setting the indexes?

Also you have always been defining ixExpression, why is this?
0
 
hibbidijiAuthor Commented:
Here comes:

in formcreate() I'm executing loadxmldata:

first I load the file into a string, then run it through here:

 procedure TForm1.LoadXmlData(AStrXMLDoc: string);
begin
  XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;

end;

After this loadxmldata is where I've put the code you posted.  

The other EE question had some good code as well. it worked perfectly except that it wouldnt do descending.   I did see your post on the other question and I will address my transform.

0
 
hibbidijiAuthor Commented:
to further clarify - when I use the code from the other quesiton, the table will sort ascending but not descending
0
 
hibbidijiAuthor Commented:
even further - I've been defining ixExpression because someone else posted that code.   I dont know a real reason to use it.
0
 
pcsentinelCommented:
Ok, it may be that you were getting the earlier error because you hadn't opened the table before creating the index

try
XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Asc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending],Clientdataset1.Fields[0].Name);

I have excluded the ixExpression

this is  a rip from the delphi help

Warning:      Attempting to create an index using options that are not applicable to the table type causes AddIndex to raise an exception.

The ixExpression constant is only applicable to dBASE tables.

Let me know the result


0
 
hibbidijiAuthor Commented:
XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Asc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive]);
//clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending],Clientdataset1.Fields[0].Name);
This doesnt create an error, though I will need to create an index for each column.  clicking any column other than 0 produces an unknown index  error - not an issue


This does create the same "index is being used to order table" error. error.   Adding the second index seems to be the cause.
XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Asc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending],Clientdataset1.Fields[0].Name);
0
 
pcsentinelCommented:
Ok for this

XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Asc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive]);
//clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending],Clientdataset1.Fields[0].Name);
This doesnt create an error, though I will need to create an index for each column.  clicking any column other than 0 produces an unknown index  error - not an issue

I presume clicking the column headers is beacuse you havent created the indexes on the other columns

so add
clientdataset1.AddIndex(Sortkind1Asc,Clientdataset1.Fields[1].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind2Asc,Clientdataset1.Fields[2].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind3Asc,Clientdataset1.Fields[3].Name,[ixCaseInsensitive]);

etc

for the descending index, I have to admit I'm stumped.

try just creating the decending index i.e.


XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending],Clientdataset1.Fields[0].Name);

and see if it still throws an error, if it does then try

XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive,ixDescending]);

or

XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind0Desc,Clientdataset1.Fields[0].Name,[ixCaseInsensitive],Clientdataset1.Fields[0].Name);

so we can try and isolate the problem




0
 
hibbidijiAuthor Commented:
XMLTransform1.SourceXml := AStrXMLDoc;
  ClientDataSet1.XMLData := XMLTransform1.Data;
ClientDataSet1.Open;
clientdataset1.AddIndex(Sortkind1Asc,Clientdataset1.Fields[1].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind2Asc,Clientdataset1.Fields[2].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind3Asc,Clientdataset1.Fields[3].Name,[ixCaseInsensitive]);
clientdataset1.AddIndex(Sortkind4Asc,Clientdataset1.Fields[4].Name,[ixCaseInsensitive]);

Unfortunately this code causes the index error in the title of the question.  

Should we be looking back at the original question asked in the thread I posted?   That at least worked ascending and didnt cause any errors...  

I appreciate your continued help!
Thanks!
0
 
pcsentinelCommented:
Ok seems really strange but ok lets go back to your origianal code and try

if dbsortable = TRUE then begin

  if Ascending then
begin


   if clientdataset1.IndexDefs.Items.IndexOf(Sortkind1)>-1 then
     DeleteIndex(Sortkind1)
   clientdataset1.AddIndex(Sortkind1,DBGridView1.Columns[ACol].FieldName,[ixExpression]);
  clientdataset1.IndexName:= Sortkind1;


   end;

   if not Ascending then
begin


   if clientdataset1.IndexDefs.Items.IndexOf(Sortkind1)>-1 then
     DeleteIndex(Sortkind1)

   clientdataset1.AddIndex(Sortkind1,DBGridView1.Columns[ACol].FieldName, [ixExpression,ixDescending]);
   clientdataset1.IndexName:= Sortkind1;




end;

end;

end;
0
 
hibbidijiAuthor Commented:
SUCCESS!

procedure TForm1.DBGridView1SortColumn(Sender: TObject; ACol: Integer; Ascending: Boolean);

begin
if dbsortable = true then begin
clientdataset1.IndexFieldNames :='';
 if Ascending then
begin
    ClientDataset1.AddIndex(DBGridView1.Columns[ACol].FieldName+'_asc', DBGridView1.Columns[ACol].FieldName,[]);

clientdataset1.IndexName := DBGridView1.Columns[ACol].FieldName+'_asc';
end;

if not Ascending then
begin
       ClientDataset1.AddIndex(DBGridView1.Columns[ACol].FieldName+'_desc', DBGridView1.Columns[ACol].FieldName, [ixDescending]);
clientdataset1.IndexName := DBGridView1.Columns[ACol].FieldName+'_desc';
end;


end;
end;

CRAZY huh?  I am doing this on each click of the header

It is working like a charm

thanks again for your help. Please sum up my code here and I will accept it as your answer for future reader's clarity

0
 
hibbidijiAuthor Commented:
The debug code that I've been tossing out on the backend has been checking that already and the count is remaining steady.   Really odd eh?
0
 
pcsentinelCommented:
Good, sorry it took so long,

regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.