Solved

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

Posted on 2011-09-29
6
426 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
Independent Software Vendors: 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.

739 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