Accessing cursors after Delete

[lcmg62]

I am new to VFP and was handed an emergency project that was being developed in it, and want to say that this forum has been immensely helpful to me thus far - thanks to all of you. I'm stuck using v3.0 and it can be hard to find information.

I am working on an inventory application which involves several tables - 3 of which are Box, Owner, and Item. Owner and Item are both children of Box, and are linked by a key of FID, SFX, YR, and BOX #. BOX # is sequential and corresponds to the number of boxes for a particular FID. The records on the child tables have sequence numbers as part of their keys, also, to keep them unique. I have filters set on the child tables in the forms' data enviornments to display the appropriate records on the screens. For the deletes, I am using a DELETE command, followed by PACK. When deleting a child record, I renumber the remaining sequence numbers to avoid gaps - if I delete Item #3 out of 5, I replace the sequence numbers with 1 thru 4. All is well and good for a delete on a child table. However I am having trouble when I perform a delete on the Box. The DELETE and PACK work fine - the Box record is deleted as well as the associated child records on OWNER and ITEM. But to do this, I had to put in a USE statement on the child tables - SELECT or USE alone both give me buffering errors. (This is not a mult-user enviornment and the table access is set as EXCLUSIVE in the data enviornment of the form). Apparently this destroys the cursors from my data enviornment as I cannot access the tables by their aliases after that point, whether or not I close the tables after the PACK command. I can USE the tables again but my filtering is gone - I end up accessing incorrect records. I need to access the correct records to update the remaining box and sequence numbers after the delete, as well as to restore the form after the delete. I tried refreshing the form and saving the aliases, neither of which worked, so I've been working on using SQL to access the reords but thought maybe there is a better way - does anyone know of a way to restore / reset the data enviornment for the form after the cursors have been lost? Or is there some way to prevent this loss in the first place?

lcmg62

 

[ramani]

Hi
I am sorry.. but I cant help with my comments...
Sooner the better... get into latest version..
atleast...........into VFP6 (huh)

Based on the whole details you have put on the above, I can say.. it is better you use the form wizard and build afresh.

I am not convinced that you have to continue with what is the current status. It will take far lesser time to build this, than correct the situation.

When you rebuild, dont think it is too difficult to build multiuser forms. You have to stop PACK etc at every level. You can have maintenance routines, where you can enforce single user (exclusive use) and do the packing.

Hope this helps you ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[ThomC]

You're working too hard... Don't PACK until you exit the app. As long as DELETE is SET ON, the deleted records will not be visibile... and I'm not just talking about the screen. Queries, seeks, etc. will not be able to "see" the deleted records. Also, repeated PACKs slow down the app and increase the risk of breaking any associated indexes.

I would advise against resetting the sequence numbers in the child tables after a delete. If the Box# + Child-Sequence number are your functional prime key for the child records, you run a real risk of orphaning child records or generating duplicate child keys. If the child-sequence number is displayed, then I would recommend adding a display field to the record and reorder that field, but once you've got a key value, don't change it.

If this is an emergency fix, don't go to SQL, as you'll have to rewrite most of the app. However, if the app is ever up for a major upgrade, I would strongly recommend moving towards a multi-tier design, where there are seperate layers of code for user interface, business logic and data access.

Regards,
Thom C.

 

[lcmg62]

Thanks, guys. I'll try your suggestions and see what I can come up with. Thom - Perhaps my clients would be willing to accept gaps in sequence numbers - I am not sure. The data entered here will eventually be incorporated into an existing mainframe application - it might depend on the ramifications there and how they plan on tracking the records in the meantime. I will try doing the PACK on exit now that I know that the deleted records will be ignored by the rest of the app.
Ramani - I would love to upgrade to the current version of VFP - unfortunately around here programmer = peon and we have little say in such matters. :-( However you are not the first person to advise this and I will let it be known that I've gotten this advice yet another time - it may have an effect eventually!

 

[fluteplr]

Based on about 25 years of doing this. Avoid the no gaps in the numbers at all costs. It is a nightmare, you have not even begun to realize the problems that trying to meet that (usually nonsensical) requirement causes in a multi user application.

Also there is really no reason to keep packing the data to get rid of the deleted records. One they are not hurting anything, two you may want to recall them as in (Oops I deleted that by mistake.) Add a mainenance routine that can be run once in awhile (like once a month) that packs the tables.

 

[lcmg62]

This is not a multi-user application, rather a stand-alone which will be run on laptop PC's in a dark corner of a warehouse. The data entered in each location will be unique and returned on a CD. However I am going to see if there is some way we can work around the sequence number problems at a later point, rather than in this application. Thanks again for the suggestions! I'd never had gotten as far as I have with this had it not been for this forum, and up until now I was able to find what I needed without even posting a question.