Solved

Random exceptions saving data to Access database

Posted on 2011-03-03
6
267 Views
Last Modified: 2012-05-11
I have a VB 2008 WinForms application that saves its data to an Access database, one parent table and several child tables with relations set up. One of the child tables always has at least one record per parent record; the others can have zero to many. Very intermittently, I will get an exception when I save a record (always a new record, I think, never for an update). I can get either of:

* System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

* The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I believe the first happens when the key linking the parent to the child record(s) is messed up so that no match is found. The second happens, I believe, when a new parent record has the same primary key (an Autonumber value) as an existing record, though it could be one of the child records (all use an Autonumber as the primary key).

I've started the Autonumber keys in both parent and child record at an absurdly high number (like 10,000,000), so that the temporary number in memory before saving the record to the database can't possibly conflict with Jet's created number in the Access database. I get the permanent number back from the DB with the following code on the OleDbDataAdapter RowUpdated event handler:

 
Private Sub HeaderUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
        If args.StatementType = StatementType.Insert Then
            gintLastID = CInt(idCMD.ExecuteScalar())
            args.Row("ID") = gintLastID
        End If
End Sub

Open in new window


Normally, once this code is executed, the internal dataset is updated with the ID generated by Access, and because the tables are related, the foreign key in the child tables is automatically updated as well. But it seems like sometimes something is breaking here, and the child record is orphaned.

Unfortunately, this is not reproducible at will. I've had it happen on clients' computers; I'll do what I think is the exact same thing on my computers (either development, in debug mode, or test machines) and everything runs fine. So I have a few questions to ask to deal with this:

1) Is there a better way for me to be saving data for related parent and child tables to avoid this error? I'm using a DataAdapter .Update method for the parent first, then use the code above to update the parent's primary key in the dataset (which is the foreign key for the child records), then do a .Update on the DataAdapter for the child table, and use the same code to update the child's primary key. It seems like a cumbersome way to operate, but it's the best I've found.

2) Is there a way to display and even fix the record that is giving the error? I can't figure out a way to get that information from the exception properties. And since my Try...Catch block covers several .Update calls, I can't even figure out a way to know which particular call is generating the error. But having a separate Try for each call seems wasteful, since I'd need the same series of Catch blocks for each one. If I could figure out a way to get the offending record, I could potentially tweak the values to resolve the error.

Any other ideas on what I can do?
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
  • 3
  • 3
6 Comments
 
LVL 41

Expert Comment

by:graye
ID: 35109121
I'd take a look to see if there is an inner exception that is thrown during the System.Data.ConstraintException.  It would probably contain the information you need to see the values for the row that's causing the problem.

This is pretty easy to do when running in the Debug mode (so don't cover this in a try/catch block).  The execption will halt the application, and let you "poke around" to see the inner exceptions (if any) and see all of the local variables that were involved.
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 35109319
Thanks for responding. (I was disappointed no one else had commented.) But part of the challenge here is that it's not reproducible at will--I don't know that I've seen it recently on my development machine, but one of my clients is having a lot of problems. I can't put a debug version of the app on his computer.

Beyond that, I'm not sure where I would find any information on the rows that are being saved/updated. Even in Debug mode, I don't know a way to see which particular row is being updated when the exception happens (particularly if I'm updating several rows at once).
0
 
LVL 41

Expert Comment

by:graye
ID: 35109556
Oh, yeah.. that doe make things difficult.  Not only would you have to have the debug version, you'd also have to have Visual Studio installed on that same PC.   So, let's table that idea, and move on...

Are there other users who are reading/writing to the same database?  Is the database located on a network share?
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: 35111085
Solo user of the database. Database is on a network drive.

I'd be willing to send them a new version of the app that displays additional information (like the inner exception) if I could figure out what to display that would be useful.
0
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 35112785
When debugging, I often use a message box that shows the entire exception (using the .ToString) using a pattern that looks like this:


Try
     ' do something here
Catch ex As Exception
     Dim msg As String
     While Not (ex Is Nothing)
          msg &= ex.ToString & vbCr
          ex = ex.InnerException
     End While
     MsgBox(msg)
End Try

Open in new window

0
 
LVL 20

Author Closing Comment

by:ElrondCT
ID: 35165544
The exception message alone doesn't provide everything I'm looking for, but it looks like it'll be a while before I can try anything else, so I'll close out the question at this point.
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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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