Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

'index is being used to order table' error

Posted on 2005-05-04
19
Medium Priority
?
777 Views
Last Modified: 2008-01-09
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.


0
Comment
Question by:hibbidiji
  • 10
  • 9
19 Comments
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13933669
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13941569
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13943148
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:hibbidiji
ID: 13948348
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13957527
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13973209
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13975561
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13981703
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13981980
to further clarify - when I use the code from the other quesiton, the table will sort ascending but not descending
0
 
LVL 1

Author Comment

by:hibbidiji
ID: 13982126
even further - I've been defining ixExpression because someone else posted that code.   I dont know a real reason to use it.
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13984127
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13990325
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13993820
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13993959
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13994155
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13994303
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
 
LVL 11

Accepted Solution

by:
pcsentinel earned 2000 total points
ID: 13994381
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
 
LVL 1

Author Comment

by:hibbidiji
ID: 13994434
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13994499
Good, sorry it took so long,

regards
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This is an update to some code that someone else posted on Experts Exchange. It is an alternate approach, I think a little easier to use, & makes sure that things like the Task Bar will update.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview
Suggested Courses
Course of the Month10 days, 12 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question