Solved

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

Posted on 2011-09-29
6
416 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
  • 4
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 20

Author Comment

by:ElrondCT
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

762 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

5 Experts available now in Live!

Get 1:1 Help Now