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

Posted on 2011-09-29
Last Modified: 2012-05-12
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?
Question by:ElrondCT
  • 4
  • 2
LVL 40
ID: 36853765
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.
LVL 20

Author Comment

ID: 36892445
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.
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 500 total points
ID: 36892824
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.

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 20

Author Comment

ID: 36893080
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...
LVL 20

Author Closing Comment

ID: 36997332
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...
LVL 20

Author Comment

ID: 37429562
Sadly, the problem has recurred. I've reposted the problem at

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Round up to 100% in .NET 10 89
Create XML 5 46 2 37
Duplicating a tab page content to another in TabControl control 11 41
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now