"You cannot add or change a record" - intermittent error

My VB 2008 app uses Access databases, with multiple tables but two that are primarily used (the parent, of course, and one child table). I am intermittently getting the error "You cannot add or change a record because a related record is required in table 'Header'." Normally everything is fine; the code doesn't change between when it works and when it doesn't.

Looking at the situation in more detail with the debugger, I can see something that may be linked to the error. I'm using OleDbDataAdapters to .Fill and .Update the main database. The .Update for the parent table (Header) works fine. After I finish that update, I reset the .RowFilter on a Datagrid that contains the records for one of the child tables to match the new parent record's rowID that's coming from saving the record to disk (autoincrement field). When I .Update that child table, I get the error message. If I immediately stop with the debugger, I can see that the Datagrid's .RowFilter has been reset, but the child record is no longer displayed. Yet when I look at the child record in the datatable, it has the correct rowID for the foreign key.

The bizarre thing is that the error is very sporadic. I can run the exact code to save a new entry, the same day or different days, and it runs fine. I'm at a loss to figure out how to correct the save process. (It happens only when saving a new set of records, parent and children.) Any ideas on what could be happening and how to resolve it?
LVL 20
Who is Participating?
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
A single-user application can bring concurrency issues if the application works on the same table from different places simultaneously.

Your way of doing thing is the best one. I suspected that you did not know how to set the CascadeRule on a DataRelation, but you now your stuff :-).

Since it does not happen often, it might simply be a timing issue. I have seen it in a few instances all over the framework. Some methods are asynchronous. The system starts then on a new thread and does not wait for them to complete before going on with your code. You end up with synchronisation issues.

I would put a short delay before updating the child (step 4.5)). It is always awkward to define the interval. But Application.DoEvents very often do miracles in such situations. It forces the application to complete all pending operations before going on.

Jacques Bourgeois (James Burger)PresidentCommented:
Does your problem happen only when the parent is a new record/row? If so, this is a typical problem.

The AutoNumber generated in your DataTable is not aware of similar AutoNumber generated by other users (or updates on other DataAdapters linked to the same tables). Suppose that your last RowID is 231, so the new record in the parent is updated to 232. The child is also set at 232.

When you update the parent in the database, 232 has been used by another user. 233 is assigned to the database for your new parent row.

Many things can happen there.

From what you tell us, when you reset the filter that is used for the match, you lose the child. So that let me think that the following happens. From what you say, the new 233 value is sent back to the parent in your DataTable. This is the right thing to do in most cases. But the change is probably not cascaded to the child. When you reset the master/detail relation, since the child is still at 232, it is not included in the join.

Most of the time, the update of the child will seem to work. But in the database it will have been assigned to the wrong parent. From time to time (that would explain the sporadic nature of your problem), records 232 will have been deleted by the other user. So when you update the child, 232 does not exist anymore and you get your error.

This is a scenario that would explain your problem, and the fact that it happens only when you add a new parent only reinforce it.

But is this what happens in your case?

There is but one line in your explanation of the problem that contradicts my scenario: Yet when I look at the child record in the datatable, it has the correct rowID for the foreign key seems to contradict me however. Do I understand that the 233 has been cascaded to the child before you try to also update the child? If so, I am wrong, your child update should work.
ElrondCTAuthor Commented:
This is a single-user app, so other concurrent users are not at issue.

I set the .AutoIncrementSeed on the ID of the parent row to a large number (500000), so that there won't be any risk of a spurious match between the ID generated by the app and IDs previously generated by the Access database itself. I use the following sequence of events to save both parent and child records:

1) Header.Update
2) In the RowUpdated event, I get the ID generated by Access: OleDbCommand("SELECT @@IDENTITY", connData
3) I change the ID on the Header row in the datatable to Access's ID. This cascades automatically to the child row(s).
4) I change the .RowFilter for the DataView on the child table to the new ID
5) Child.Update

98% of the time, this works fine. Occasionally, I get the error described. The debugger indicates that the foreign key in the child table has been correctly changed to the Access-generated ID, and the DataView .RowFilter is set to the same, but the child row doesn't show up in the DataView, and trying to save the rows gives the error. It's as if the foreign key it's actually using isn't the same as what it's displaying.

This happens only with saving a new parent row, so it's almost certainly connected with the process of retrieving and using the new ID coming from the database. It most often happens with the first row I save after starting the application; if the first save is OK, rarely if ever do I have a problem with a later save.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

ElrondCTAuthor Commented:
A timing issue does seem plausible to me. One of my clients indicated that it was happening more frequently when they had the database on a server than when it was on a local disk. I've had it happen on the local disk too, though. Application.DoEvents seems like a reasonable way to deal with that potential issue. Of course, with an intermittent error, it'll be hard to know if it's actually working...
ElrondCTAuthor Commented:
No easy way to know for sure that it's working, but it seems like a reasonable solution. If I have further problems, I'll circle back...
ElrondCTAuthor Commented:
Sadly, the problem has recurred. I've reposted the problem at http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_27535367.html
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.