I have a VB 2008 WinForms application that saves its data to an Access database, one parent table and several child tables with relations set up. One of the child tables always has at least one record per parent record; the others can have zero to many. Very intermittently, I will get an exception when I save a record (always a new record, I think, never for an update). I can get either of:
ption: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
* The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
I believe the first happens when the key linking the parent to the child record(s) is messed up so that no match is found. The second happens, I believe, when a new parent record has the same primary key (an Autonumber value) as an existing record, though it could be one of the child records (all use an Autonumber as the primary key).
I've started the Autonumber keys in both parent and child record at an absurdly high number (like 10,000,000), so that the temporary number in memory before saving the record to the database can't possibly conflict with Jet's created number in the Access database. I get the permanent number back from the DB with the following code on the OleDbDataAdapter RowUpdated event handler:
Private Sub HeaderUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
If args.StatementType = StatementType.Insert Then
gintLastID = CInt(idCMD.ExecuteScalar())
args.Row("ID") = gintLastID
Normally, once this code is executed, the internal dataset is updated with the ID generated by Access, and because the tables are related, the foreign key in the child tables is automatically updated as well. But it seems like sometimes something is breaking here, and the child record is orphaned.
Unfortunately, this is not reproducible at will. I've had it happen on clients' computers; I'll do what I think is the exact same thing on my computers (either development, in debug mode, or test machines) and everything runs fine. So I have a few questions to ask to deal with this:
1) Is there a better way for me to be saving data for related parent and child tables to avoid this error? I'm using a DataAdapter .Update method for the parent first, then use the code above to update the parent's primary key in the dataset (which is the foreign key for the child records), then do a .Update on the DataAdapter for the child table, and use the same code to update the child's primary key. It seems like a cumbersome way to operate, but it's the best I've found.
2) Is there a way to display and even fix the record that is giving the error? I can't figure out a way to get that information from the exception properties. And since my Try...Catch block covers several .Update calls, I can't even figure out a way to know which particular call is generating the error. But having a separate Try for each call seems wasteful, since I'd need the same series of Catch blocks for each one. If I could figure out a way to get the offending record, I could potentially tweak the values to resolve the error.
Any other ideas on what I can do?