I think your understanding is a little wrong. Yes, I am doing an insert, but here is what happens. The table in questions has the following fields:

ADL_KEY: Primary key for the table.
ADL_ADH_KEY: Foreign key to the order header table.
Other fields as needed, such as description, amount, etc.

Before the bug happens, ADL_KEY would be 1, and ADL_ADH_KEY would be 1. After the bug happens ADL_KEY would be 2, and ADL_ADH_KEY would be 2.

This effectively orphans the header record (parent with no children).

The insert that causes this would look something like this:

INSERT INTO ADLINES (ADL_KEY, ADL_ADH_KEY, DESCRIPTION, AMOUNT) VALUES (lnNextNum, ADHEADER.ADH_KEY, ADCOST.DESCRIPTION, ADCOST.AMOUNT)

All other tables (adheader and adcost) are positioned on the correct records.


0. Yes, the table is in a .DBC.
1. Yes, the primary key is an integer.
2. Yes, the primary key is the first field in the record.
3. No, the insert is not inside a transaction.
4. Yes, the table is buffered with Optimistic Table Buffering.
5. The insert format is fields/values.
6. Yes, when this bug happens the primary key always gets changed.
7. No, there is no relationship that is apparent between the overwritten key and the key that does the overwriting.
8. Not sure. The hardware people try to keep that stuff secret.
9. VFP 6 - Newest SP.

Thanks


>>Forgot to mention that part. I have rebuilt the indexes. Still same thing. This bug only happens about once a month.
>>
>>>>Ok, this is a weird one. I have an order entry system that has a Order Header Table and a Line Items Table. The user can start an order and then add multiple line items. This part works fine. Occasionally, however, adding a new line overwrites an existing line (always from a different order). When the user clicks the "New Line" button, the code finds the next available key and then performs a SQL Insert command to insert the new line. But, when this bugs crops up, even the Primary Key is changed to a new key. Nowhere in the system do I have a replace that replaces the primary key.
>
>If I understand correctly a SQL Insert is done, with a value in the Primary Key field already to add a new line to that Order (of the primary key) and a line does get written BUT it has a different Primary key than intended and so becomes associated with another order. Is that correct? And all information in the new record EXCEPT the Primary Key is exactly as intended?
>
>0. Is the table in question in a .DBC?
>1. Is the primary key a Integer field?
>2. Is the Primary key the first field in the record?
>3. Is the INSERT-SQL under a TRANSACTION?
>4. Is the table buffered and if so, which style?
>5. What format is the INSERT-SQL (fields/values or FROM ARRAY|MEMVAR|NAME or a sub-SELECT?
> - If FROM..., is there any chance that the primary key is being changed by something inadvertently?
>6. Can you be certain that this never happens within a set of new lines too (i.e. not the primary key mucked up but maybe the line key)
>7. Is there any relationship between the intended key and the actual written key, like maybe it's always nn away in value?
>8. What OS/SP for the workstation and NOS?
>9. What version/SP of VFP?
>
>Sounds like a bad bug for sure.
>
>
>>>>
>>>>I know that an old record is getting overwritten because I am using TakeNote's FoxAudit, and it shows when it happened and who did it, the value it was before and the value it changed to.
>>>>
>>>>By all reasoning, this can't be happening, but it is. An insert command is overwriting an existing record. Does anyone have any suggestions on this???
>>>
>>>Might be a corrupted index. Rebuild if possible (i.e. DELETE TAG ALL, etc.) (may not be easy if you have RI set up to use indices) rather than REINDEX.