Hi all,

DBISAM v4, D6

To start with I had a form with a 2 tables in a Master-Detail setup, a
TDBGrid with the Master table as the datasource, some buttons to do
New, Edit, Delete, Filter and Sort functions. All works great.

I now want to venture out and add the functionality of the user
sorting by clicking the grid's column headers. I read the 'sorting'
posts here and since the grid contains table Lookup Fields, and I am
doing editing, I have to change the grid's datasource to a Query (I am
using TMS's TDBAdvGrid by the way). I have done that and got the New
and Edit functions worked out OK.

But with the Delete function it looks a bit sloppy; when I delete a
record and do a MyQuery.Close and MyQuery.Active := True (needed to
reflect the changes in the table), the grid pointer always goes back
to the first record. What I would like the pointer to do is appear to
stay at a recrod that was adjacent to the deleted record.

So..., how do I get the RecordNo of the adjacent record in a Query? In
a long winded way I guess I have to test first if
MyQuey.FilterRecordCount is > 1, introduce another query based on
MyQuery and do a Query.Next and get the adjacent RecordNo, then do a
MyQuery.Locate at the end. Is there a cleaner technique?

Thanks.

Regards,
Pat

"Pat" <pat@downunder.com> wrote in message
news:nkeaf4d3slmmbf1l96b9sga89e9hhpad3c@4ax.com...
>
> But with the Delete function it looks a bit sloppy; when I delete a
> record and do a MyQuery.Close and MyQuery.Active := True (needed to
> reflect the changes in the table),

Why? You should not need to close and reopen the query after a delete.

Robert

>> But with the Delete function it looks a bit sloppy; when I delete a
>> record and do a MyQuery.Close and MyQuery.Active := True (needed to
>> reflect the changes in the table),
>
>Why? You should not need to close and reopen the query after a delete.

So as to reflect the deleted record from the table. The Grid displays
the records from the Query and since the query is not live (because of
the Lookup fields), I close and reopen the query.

I am treating the grid as a fancy GUI (for the purpose of sorting by
clicking on the column headers). When I edit a record, I get the
RecordNo from the grid/query, locate this selected record in the
table, edit the table, close & open the query to reflect the changes.
This way the grid looks 'live' but it is not actually.

Its my first time doing it this way, looks OK so far, except for this
current issue

Pat

Pat


A couple of thoughts for you.

1. Unless there are a lot of fields create an index for each column shown then switching sorting is a matter of switching indexname

2. Unless there are a lot of rows you might want to look at using the stringgrid rather than the dbgrid - sorting is built in. The downside is you have to manage updates to the table.

Roy Lambert [Team Elevate]

Roy

>1. Unless there are a lot of fields create an index for each column
> shown then switching sorting is a matter of switching indexname

That is an index in the table Roy? If its a Lookup field, how do I
create an index? I only use DBSYS to create the indexes. Can I create
an index on a Lookup field at run time somehow?

Pat

Pat


Sorry I missed the bit about lookup fields. You can't create an index on them. So its either SQL as you are doing which if there are lookup fields you want to sort on will probably involve JOINs and mean a canned result set so you have to manage the updates to the table or use an AdvStringGrid and manage the updates to the table.

If this is being done to give the users better sorting capabilities and the tables aren't to large I'd probably go for the latter you can then sort ascending & descending and across multiple columns with very little coding.

The only reason I don't use the stringgrid exclusively is that some of my tables have to many records and the time to populate the grid with all records is to long (in my view) for a user to sit and twiddle their thumbs.


Roy Lambert [Team Elevate]

"Pat" <pat@downunder.com> wrote in message
news:98taf4hceac5uhr2jc88fm364rjddo5hlv@4ax.com...
>>> But with the Delete function it looks a bit sloppy; when I delete a
>>> record and do a MyQuery.Close and MyQuery.Active := True (needed to
>>> reflect the changes in the table),
>>
>>Why? You should not need to close and reopen the query after a delete.
>
> So as to reflect the deleted record from the table. The Grid displays
> the records from the Query and since the query is not live (because of
> the Lookup fields), I close and reopen the query.

If you delete a record from the query, it will dissapear from the grid. All
of that is built in. You don't need to close and reopen the query. If you do
a query.delete, thet line in the grid goes away, the display gets repainted,
and that's that.

>
> I am treating the grid as a fancy GUI (for the purpose of sorting by
> clicking on the column headers). When I edit a record, I get the
> RecordNo from the grid/query, locate this selected record in the
> table, edit the table, close & open the query to reflect the changes.
> This way the grid looks 'live' but it is not actually.

For a delete, do a query.delete instead of closing and opening the query.
You'll get the effect you want - staying in the current position - and it
will be faster. Just put a navigator in your form, and test it out, you'll
see what I mean. You can still reflect the changes to the "real" table by
deleting the table record in the query's beforedelete. It's just that you
don't have to do the select all over again.

>
> Its my first time doing it this way, looks OK so far, except for this
> current issue
>

As far as sorting the display based on clicking column names, with DBISAM I
think it is much better to do the original select INTO a memory table and
add to that memory table all the required indexes. Adding an index with
DBISAM is almost instantaneus (I don't know how they do it, but it is), so
building a memory table with indexes vs just excuting a query takes about
the same time. Then when the user clicks on the column header, instead of
having to reselect everything, you just change the index name. Instant
redisplay in the new sequence.

Robert

Robert

>> So as to reflect the deleted record from the table. The Grid displays
>> the records from the Query and since the query is not live (because of
>> the Lookup fields), I close and reopen the query.
>
>If you delete a record from the query, it will dissapear from the grid. All
>of that is built in. You don't need to close and reopen the query. If you do
>a query.delete, thet line in the grid goes away, the display gets repainted,
>and that's that.

I think Pat's recently switched from a table to a query (at least that's my guess from the way I read it) and he'll need to delete from the original table as well.

>As far as sorting the display based on clicking column names, with DBISAM I
>think it is much better to do the original select INTO a memory table and
>add to that memory table all the required indexes.

That's a very good option which I forgot.

Roy Lambert [Team Elevate]

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:C05BEA51-34C9-48E4-AFF7-89F77204D429@news.elevatesoft.com...
> Robert
>
>>> So as to reflect the deleted record from the table. The Grid displays
>>> the records from the Query and since the query is not live (because of
>>> the Lookup fields), I close and reopen the query.
>>
>>If you delete a record from the query, it will dissapear from the grid.
>>All
>>of that is built in. You don't need to close and reopen the query. If you
>>do
>>a query.delete, thet line in the grid goes away, the display gets
>>repainted,
>>and that's that.
>
> I think Pat's recently switched from a table to a query (at least that's
> my guess from the way I read it) and he'll need to delete from the
> original table as well.
>

Sure. But a canned query is just another dataset. So you delete from the
query AND from the "real" table. On the before delete of the query, you fire
the delete for the table. You don't have to rerun the select.

>>As far as sorting the display based on clicking column names, with DBISAM
>>I
>>think it is much better to do the original select INTO a memory table and
>>add to that memory table all the required indexes.
>
> That's a very good option which I forgot.

You don't even have to close the table to change the display sequence, and
you don't lose position. Plus it happens instantly. There is a lot of
intelligence built in the tdataset and the tdbgrid, might as well use them.

Robert

>> I am treating the grid as a fancy GUI (for the purpose of sorting by
>> clicking on the column headers). When I edit a record, I get the
>> RecordNo from the grid/query, locate this selected record in the
>> table, edit the table, close & open the query to reflect the changes.
>> This way the grid looks 'live' but it is not actually.
>
>For a delete, do a query.delete instead of closing and opening the query.
>You'll get the effect you want - staying in the current position - and it
>will be faster. Just put a navigator in your form, and test it out, you'll
>see what I mean. You can still reflect the changes to the "real" table by
>deleting the table record in the query's beforedelete. It's just that you
>don't have to do the select all over again.

Great tip, that will fix me up for sure  What's that saying...
'can't see the forest for the trees'.

>Then when the user clicks on the column header, instead of
>having to reselect everything, you just change the index name. Instant
>redisplay in the new sequence.

thanks for that. One of the reasons I used TDBAdvGrid was that it had
a demo/sample code  showing the grid sorting both ASC and DESC by
clicking the column header. No doubt the standard TDBGrid could do
this, but it would take me far TOO long to do it.

Thanks Robert & Roy