How to catch CONSTRAINT Exception when adding duplicate unique id with VB

Question

I have a problem that adding a new ROW with duplicate UNIQUE id throws CONSTRAINTException that I cannot always catch.. Randomly general exception halts my VB software, sometimes catching works. here is my code.

Private Sub BindingNavigatorAddNewItem_MouseUp()
      try
      DataGridView1.CurrentRow.Cells(0).Value = InputBox("give new product id")
      Catch ex As Exception
        MessageBox.Show("error ") <-- never comes here!!
      End Try

end sub
Private Sub DataGridView1_DataError(..) Handles DataGridView1.DataError

        e.ThrowException = False <-- randomly comes here!!
        Dim v As DataGridView = CType(sender, DataGridView)
        v.Rows(e.RowIndex).Cells(e.ColumnIndex).ErrorText = "The value is wrong"
        v.BeginEdit(False)
        MessageBox.Show("error 2") 

end sub

Answer 1

You should correct this code to avoid ever even adding duplicate rows. Just swallowing the error (even if you can) is a bad idea.

Answer 2

Two things, first how do you know that the database INSERT command is being executed within the context of the first procedure? I don't see anything there that would force the changes back to the database. Are you getting an unhandled exception with a stack dump that shows that it was called from that routine (and yet not handled)? Otherwise, I would say that there is a good chance that the INSERT is not being executed until sometime after that routine.

Secondly, it appears to me that the DataGridView1_DataError will squelch the error anyway. I beleive that the _DataError event will be invoked before it returns to the code that caused the INSERT to be executed and that "e.ThrowException = False" is spposed to prevent any repropagation of the exception.