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.
