[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Random exceptions saving data to Access database

Posted on 2011-03-03
6
Medium Priority
?
275 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 1500 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

649 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