Sorting records according to two or more fields

I have TTable component linked to FoxPro 2.6 table containing information about students. Some of the fields are indexed (single field, ascending order, no compound indexes). To sort the records in the table according to any indexed field (for example "FIRSTNAME") I simply assign its name to IndexName property of TTable. But how to do it when user of my database need to sort records according to two or more fields, for example in the following order: Surname->FirstName->BirthDate. Please help me.
Thanks, Ivo.
LVL 2
ivobauerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kretzschmarConnect With a Mentor Commented:
hi ivo,
tquery has a filter property, which works like the filter-property in the ttable!!
meikl
0
 
kretzschmarCommented:
hi ivo,
is it possible for you to work with a tquery instead with a ttable ?
meikl
0
 
manjushanCommented:
Hi,
  Use TQuery and give order by both the fields or how many ever fields u want to.It will work faster and better.
                        Manju
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
geobulCommented:
Hi,

As FoxPro 2.6 is not a SQL based system (I think), you have two possibilities:
1. Create new index at runtime and work with it or
2. Replace TTable with TQuery as Meikl said.

I recommend the second way too.

Regards, Geo
0
 
ivobauerAuthor Commented:
I cannot use TQuery because I need the possibility to filter records in the table (Tquery has not this ability). Also it is impossible to add each time new index because order of fields can vary (it is user-defined).
0
 
ivobauerAuthor Commented:
So I have looked to Delphi help and tquery really has Filter property (inherited from TDataset, therefore it's placed at the end of list) so I tried al the things and they worked well. Here are your points.
Thanks, Ivo.
0
 
kretzschmarCommented:
thanks for the points, ivo ;-)
glad to helped you
good luck again

meikl
0
 
ivobauerAuthor Commented:
Hi Kretzchmar. Since I have evaluated your question prematurely (was my fault) I am asking you (*please*) to help me again. I realized that TQuery component will be more suitable for me than TTable. But even I ask for live query (updatable) bde returns a readonly set to me due to ORDER BY clause present in the SQL and I still need to add, delete or modify existing record. Please help me again how to implement this feature...
Thanks, Ivo.
0
 
kretzschmarCommented:
yup, no problem,

you need additional a TUpDateSQL-Component as UpdateObject for your query.

this also needs that the query has cachedUpdates enabled.

well, i will try a StepByStep introduction, because there is no much coding than more property-adjustments.

lets start a sample:
(i do so, that the query is always there and is properly configured, so that opening is possible)
at designmode:
-set the cachedUpdate-Property of the query to true
-drop a TUpdateSQL on the form
-select on theUpDateObject-property of the query now the TUpdateSQL
-doubleclick on the TUpdateSQL
-on the appearing dialog select
  -the keyfield (normally the field of the primary key)
  -the fields (normally all fields), which should be updated
- press now the button [generate sql]
- in the tab sql of this dialog you can now see and edit (if needed) the sql-statements for insert,update and delete
- if all ok press[ok]

now the bit code:
assign to the query-events afterpost and afterdelete a procedure, which contains:
queryname.ApplyUpdates;
queryname.CommitUpdates;

thats all, run your app

if you have problems to implement this, then leave your eMail-address here and i send you a sample.

good luck

meikl
0
 
ivobauerAuthor Commented:
Hi again Meikl.
I have set up the things as you said (added TUpdateSQL and assigned calls to ApplyUpdates and CommitUpdates to AfterPost and AfterDelete events of the query). Modification of records via db-aware controls is ok, only when I insert new record to the query via dbnavigator, the new record is not placed according to field order specified in ORDER BY clause in TQuery's SQL. May I close query and reopen to get correct order of records? And second thing: I tried to update (insert new record) query by Apply method of tupdatesql (after assigning parameters for the Query[ukInsert]) and the new record will not appear in query until I close it and reopen. Then I discover that new record contains completely different values than I specified.
Maybe some samples will help (send them please to: Ivo.Bauer@upce.cz).
Many thanks, Ivo.
0
 
kretzschmarCommented:
hi ivo,

yup, i send you the sample this evening.

to dbnavigator:
 yes you have to refresh the query (close/open), because the dbnavigator allways use the insert-method at current-record.

the second thing:
you cannot do this with a TUpdateSQL, when it is assigned as an UpdateObject, because your given parameters may be overwritten with the current record of the query.

meikl
0
All Courses

From novice to tech pro — start learning today.