How can i capture a duplicate record error before trying to save it?

Question

0.00/5 (No votes)

See more:

I am having a problem entering new records into the database. There is a key field called 'Title' and sometimes I screw up and try to enter something that is already there. after entering the full data I save it but if there is a duplicate it shuts down with an error message. How can I capture this error and allow the user to abort the routine?

The The entry code I believe is through BindingNavigator and below and the error below that. is my record save code. The error shows at the 'Me.DVD_ListBindingSource.EndEdit()' statement. If it makes a difference I am working in Visual Basic 2015.

VB

Private Sub DVD_ListBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles DVD_ListBindingNavigatorSaveItem.Click

 If MessageBox.Show(&quot;SAVE THIS RECORD TO THE DATABASE&quot; &amp;
                             vbCrLf &amp; vbLf &amp; &quot;Are you sure?&quot;,     
                             MessageBoxButtons.YesNo, MessageBoxIcon.Stop) =
                             DialogResult.Yes Then

            Me.Validate()
            Me.DVD_ListBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me._D__DATA_FILES_DVD_LIST_MDFDataSet)
            MessageBox.Show(&quot;RECORD  SAVED&quot;, &quot;AS REQUESTED&quot;,  
        Else
            MessageBox.Show(&quot;RECORD NOT SAVED&quot;, &quot;AS REQUESTED&quot;,                    
        End If

The error msg.

An unhandled exception of type #39;System.Data.ConstraintException' occurred in System.Data.dll

What I have tried:

I originally thought of a Try-Catch method of catching the error and displaying it, but I'm not sure of what I'm looking for.

Posted 7-Mar-16 8:40am

Member 10376725

Updated 7-Mar-16 9:07am

Patrice T

v2

Add a Solution

2 solutions

Solution 2

Maybe you want to reconsider making Title a key field. Theoretically there could be different DVD's with the same title.

Regardless if you follow this suggestion or not I would solve the main issue like this:

After the user has entered the title (that is, after the input field loses focus) query the database for potential duplicates. If there's potentially network latency involved this should be done asynchronously to not block the UI. If any duplicates are found, show them to the user so he can decide whether it's an actual duplicate or just an identical title by chance. If he decides that it's a duplicate, clear the new row.

The beauty of it: If there is a duplicate the user knows early and not only after filling in all other fields as well for nothing. And you don't have to handle insert errors (of course, if you don't follow my first suggestion you would then have to not ask the user if it's a duplicate but clear the row right away).

Posted 7-Mar-16 9:07am

Sascha Lefèvre

Comments

Member 10376725 9-Mar-16 14:07pm

Sascha Lefèvre,

I am still using the 'Title' field as the unique key, and have coded in a Try/Catch block and it seems to work. Now when the record is saved to the datset how can resort the dataset to put the record in it's proper place. I have tried:

Me._D__DATA_FILES_DVD_LIST_MDFDataSet.Tables(0).DefaultView.Sort = "Title"

but it doesn't work. The new record still appears at the end of the list. If I restart the program the new record appears in the correct sequence.

Private Sub DVD_ListBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles DVD_ListBindingNavigatorSaveItem.Click

If MessageBox.Show("SAVE THIS RECORD TO THE DATABASE" &
vbCrLf & vbLf & "Are you sure?", "SAVE RECORD",
MessageBoxButtons.YesNo, MessageBoxIcon.Stop) =
DialogResult.Yes Then
Try
Me.Validate()
Me.DVD_ListBindingSource.EndEdit()
Catch ex As Exception
If MessageBox.Show("Can not save record " & vbCrLf & ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error) Then
Exit Sub
End If
End Try

Me.TableAdapterManager.UpdateAll(Me._D__DATA_FILES_DVD_LIST_MDFDataSet)
MessageBox.Show("RECORD SAVED", "AS REQUESTED", MessageBoxButtons.OK, MessageBoxIcon.Information)
Me._D__DATA_FILES_DVD_LIST_MDFDataSet.Tables(0).DefaultView.Sort = "Title"

Else
MessageBox.Show("RECORD NOT SAVED", "AS REQUESTED", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If

End Sub

Sascha Lefèvre 9-Mar-16 14:56pm

Sorry, I don't know. Maybe setting DefaultView.ApplyDefaultSort to false and then back to true again does the trick. Otherwise I'd take a look at the BindingSource, maybe there's a method to re-sort.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-03-07T08:47:00

Solution 1

Use a Try...Catch block: Try...Catch...Finally Statement (Visual Basic)[^]

Posted 7-Mar-16 8:47am

OriginalGriff