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 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.
My code:
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?",
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("RECORD SAVED", "AS REQUESTED",
Else
MessageBox.Show("RECORD NOT SAVED", "AS REQUESTED",
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.
You might take the direct approach and just check for duplicates (or valid entry) after the 'Title' field is updated before going on with the rest of the edit.
Don't know if my approach would suit you, but I use a Stored Procedure for that type of thing.
The APP calls the SProc with parameters for each column (in the table), and (for me) I use another parameter to indicate Insert (must not exist), Update (must exist) or "UpSert" (insert if not exists, otherwise update).
I have an Output Parameter to return Success / Fail and an Output Parameter for Fail Message. The SProc first performance Data Validation checks (such as your "Title must not already exist") and then performs the Insert / Update.
If the Result of the SProc is "Fail" then I can use the Fail Message in the APP to tell the user what went wrong.
As such I encapsulate as much logic relating to saving a record into the SProc (rather than having it in the APP) as possible. If the SProc needs to use a TRANSACTION it is held for the minimum amount of time possible (no delays for round-trip to the Client / APP)
I would very much like to test for duplicates immediately after entering the Title, but I have no idea where to do this. I am a newbie (kinda) so apparently not very fluent. I started the form, attached the database and built the form from there. At that point I had 3 files, form1.vb, form1.vb(dasigner) and form1,designer.vb and I could do most things I wanted from there. The Problem arose when I accidently tried to add a movie I already had and it threw an exception for a duplicate record. I looked through all the code and nowhere does it have any code to show where Title is entered so I cant put a Try/Catch to catch the error at that point.. I don't want to go back and redesign the whole thing if I don't have to. I wrote a Try/Catch element that worked AFTER I had entered the entire record, it caught the error and presented the user the opportinity to abort or go back and make a change but by this time I had entered the entire record. Not sure where to go from here.
Change your code so that instead of UPDATE you call a Stored Procedure, and then put the "Check for duplicate title" logic in the Procedure (along with the UPDATE if no validation errors are detected)
Just to describe what I was saying, a stored procedure can take the concept of a "Try..Catch" as a transaction. Your UPDATE or INSERT would be within the BEGIN TRANSACTION / END TRANSACTION. You can test the result of the UPDATE / INSERT and either COMMIT TRANSACTION or ROLLBACK TRANSACTION. This could send it back to the user to make changes or alter the result. A basic description is here: https://msdn.microsoft.com/en-us/library/ms188929.aspx
The transaction could even use a CURSOR if you wanted to do it serially instead of as a batch. Since this is in the SQL for the INSERT / UPDATE, the code in Visual Studio can take the request results and present them to the user if they need to make a change.
Another idea is to run a query (stored procedure or function) to check for duplicates (using Title as a parameter) before attempting the INSERT / UPDATE. This way you would have direct feedback as a result from the stored procedure to do the UPDATE / INSERT based on what the form is sending as a parameter. This could be triggered by the event for leaving the 'Title' edit field. The point where you would trigger this in your existing code could be after:
Private Sub DVD_ListBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles DVD_ListBindingNavigatorSaveItem.Click
and before
If MessageBox.Show("SAVE THIS RECORD TO THE DATABASE" &
vbCrLf & vbLf & "Are you sure?",
Yet another way would be to present your records in a datagrid and validate changes upon leaving a field. 'They' could then see what was already there and have the ability to enter either a new record or update an existing record.
That would be my least-favourite, because of the window-of-opportunity for someone else to add the duplicating title between the Check and the Update (ways around that locking things and so on, but that up's the complexity and chance of more things going wrong, or slipping-through-the-cracks IMHO)