Solved

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

Posted on 2011-09-29
6
429 Views
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?
0
Comment
Question by:ElrondCT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
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.
0
 
LVL 20

Author Comment

by:ElrondCT
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.
0
 
LVL 40

Accepted Solution

by:
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.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Author Comment

by:ElrondCT
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...
0
 
LVL 20

Author Closing Comment

by:ElrondCT
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...
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 37429562
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
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses

630 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