Thank you very much Dave. Your reply seems to have hit the nail right on! It is more or less what I had been doing (the manual production of the PK with a control table) since the days of VFP 6. Most of my apps since then have been changed to use autoincremental fields but then, the volumes involved were not very large or frequent as the ones I am experiencing with this particular routine.
I will go back to your approach in this one and see what happens. Thanks again Rafael Copquin El 22/04/2010 5:48, Dave Crozier escribió: > Rafael, > I got burnt in a similar way to you when I tried to convert our old system > from programmatically generater PK's to the autoincrement kind. I did it > initially as a test and to make the system "slicker" but in retrospect this > was a complete waste of time as the cdx corruption problems just seemed to > occur on a regular basis although not every day as you have encountered. No > amount of system testing could reproduce the errors and they seemed to occur > only under times of heavy throughput which is obviously difficult to > simulate. > > Since then I have reverted to the old fashioned way of having a control > table with the next PK for each table in separate records...as per the > original software and since the VFP9 SP1 installation have not had a single > case of cdx corruption (over 3 years now). > > Thankfully the initial change to autoincrement was of limited exposure in a > couple of the larger tables and the revert back was fairly painless. Why did > I do it? Well I was trying to pre-empt the current project which is the > transfer from VFP tables onto a SQL server back end which so far is working > well but the program conversion is a long job! > > Maybe it is worthwhile biting the bullet and going back to the older "tried > and tested but not as quick" method. > > Dave C > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf > Of Rafael Copquin > Sent: 21 April 2010 21:29 > To: [email protected] > Subject: taableupdate and autoincremental fields > > I have an app made for a cosmetics lab (they make lipsticks, nail > enamel, face creams of all kinds, you name it). For legal reasons, they > are subject to the local government department of health audits and they > must have a batch identification number, as well as a unit number, so in > case the Government needs to trace one particular item or one particular > batch, the program has all necessary data saved in ad hoc tables. > > The way it works is that every time a manufacturing order is processed > for say a batch of nail enamel, it will be made for so many bottles. > Each bottle will have a label stuck to it with the bottle contents, > formula, batch number, date manufactured and unit number. > > The table designed to contain all this info has a primary key, derived > from an autoincremental field. > > In the load event of the form I create an empty cursor adapter for this > table with a select statement that looks like > > select * from .... where 1=0 > > At the time of saving, this cursor adapter is part of a transaction. > > A for-next loop generates each record that will be saved (sequential > part number, etc) and saves with this snippet: > > for I = 1 to nNumberOfBoxes > cPartNo = 'XXXX'+alltrim(str(i))&& the code details don't matter here > > insert into (all fields) values(all values and cPartNo) > > endfor > tableupdate(.t. ,.t.)&& bulk update at the end of the loop (this is the > one I use) > > And alternative would be: > > for...... > cPartNo = ............. > insert ..... > tableupdate(1,.t.)&& individual update, one for each part number > endfor > > My problem is that very often the compound index for this table is > corrupted. I checked the network cards, the cable connections,put > exceptions in the antivirus (dbf, cdx, fpt, dbc,dbt,dbx) so it will not > affect my app, put a flush statement after the end transaction command. > The tables reside in a Windows 2003 Server and the terminals are XP Prof > and some W7. > > And yet, the index is screwed almost every day. This is a high volume > routine, there are dozens of manufacturing orders every day and several > users issue them, according to the production run of the day. > > I think that the way of bulk recording shown in the first example plus > the fact that I use an autoincremental field might be causing the problem. > > However, I am at a loss to determine the reason. > > Is there anything you guys could advise? > > (VFP 9 SP1) > > TIA > > Rafael Copquin > > > > > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
