Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 10 total |
Finding the adjacent record in a Query after a Delete |
Tue, Oct 14 2008 8:49 PM | Permanent Link |
Pat | 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 |
Tue, Oct 14 2008 11:22 PM | Permanent Link |
"Robert" | "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 |
Wed, Oct 15 2008 1:05 AM | Permanent Link |
Pat | >> 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 |
Wed, Oct 15 2008 2:33 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Oct 15 2008 4:26 AM | Permanent Link |
Pat | 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 |
Wed, Oct 15 2008 5:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Oct 15 2008 7:59 AM | Permanent Link |
"Robert" | "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 |
Wed, Oct 15 2008 8:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Oct 15 2008 9:34 AM | Permanent Link |
"Robert" | "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 |
Thu, Oct 16 2008 1:21 AM | Permanent Link |
Pat | >> 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 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |