• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Random exceptions saving data to Access database

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?
  • 3
  • 3
1 Solution
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.
ElrondCTAuthor Commented:
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).
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?
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

ElrondCTAuthor Commented:
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.
When debugging, I often use a message box that shows the entire exception (using the .ToString) using a pattern that looks like this:

     ' 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
End Try

Open in new window

ElrondCTAuthor Commented:
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.

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now