Solved

Random exceptions saving data to Access database

Posted on 2011-03-03
6
263 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
  • 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

21 Experts available now in Live!

Get 1:1 Help Now