Handling SQLException with a DataGridView

Handling SQLException with a DataGridView

• Shotmaker

  Thanks,

  I figured out a way to do it last night, not sure if its the best way but if anyone else is interested I changed the code of my binding navigator SaveItem_Click event as follows (had to add imports of system.data and system.data.sqlclient to the class): -

  Private Sub Publishers_SeriesCountBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Publishers_SeriesCountBindingNavigatorSaveItem.Click

  Try

  Me.Validate()

  Me.Publishers_SeriesCountBindingSource.EndEdit()

  Me.Publishers_SeriesCountTableAdapter.Update(Me.ComicsDBDataSet.Publishers_SeriesCount)

  Catch SQLExp As SqlException

  MessageBox.Show("An SQL Server Error Occurred: " & e.ToString(), "SQL Error", MessageBoxButtons.OK)

  End Try

  End Sub

  
  

• R.S

  I should also mention that I have now built code into the CellValidating code to check for duplicate values, again not sure if this is the best way to do this but seems to work!

  The check basically uses the find command on the binding source and checks for a value >= 0 that is not the same as the current row index, as below: -

  Private Sub Publishers_SeriesCountDataGridView_CellValidating(ByVal sender As Object, _

  ByVal e As DataGridViewCellValidatingEventArgs) _

  Handles Publishers_SeriesCountDataGridView.CellValidating

  ' Validate the PublisherTitle entry

  If Publishers_SeriesCountDataGridView.Columns(e.ColumnIndex).Name = "PublisherTitle" Then

  Publishers_SeriesCountDataGridView.Rows(e.RowIndex).ErrorText = Nothing

  ' Disallow empty strings

  If String.IsNullOrEmpty(e.FormattedValue.ToString()) Then

  Publishers_SeriesCountDataGridView.Rows(e.RowIndex).ErrorText = _

  "Publisher Title must not be empty"

  e.Cancel = True

  End If

  ' Disallow duplicate value

  Dim foundIndex As Integer = Publishers_SeriesCountBindingSource.Find("PublisherTitle", _

  e.FormattedValue.ToString())

  If foundIndex >= 0 And foundIndex <> e.RowIndex Then

  Publishers_SeriesCountDataGridView.Rows(e.RowIndex).ErrorText = _

  "Publisher Title must be unique"

  e.Cancel = True

  End If

  End If

  End Sub

  
  

• voko

  Take a look at the datagridview's dataerror event.
  
  
  

• LabibaSeif

  I forgot to mention you have to define a primary key for the table. You will get the error than.

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassprimarykeytopic.asp

  
  
  

• Allison

  I did, as I mentioned above, it doesnt seem to catch this type of exception.

  
  

• alan lan

  The underlying table has a primary key and also a second unique index.

  As I mentioned i tried the DataError and this trapped some errors, such as leaving the column blank but it did not trap entering a duplicate value, this still generated the defauly SQLexception when I clicked on Save.

  However, with the above code in the CellValidating to check for duplicates and the final trap of SQLException errors in the SaveItem_Click event (just to catch anything else I havent thought of yet!) - I think i have this pretty much covered now, thanks.

  
  

• Jenny Lo - MSFT

  hi,

  i guess your dataset that suppose to throw exception if you did that not your datagridview , even it will not wait till you save changes may be as soon as you leave this cell you will get exception

  datagridview have nothing to do with your data it just display and capture changes , you can disable that keyvalue column and don't allow user to change it.

  also during saving try catch(exception ex) messagebox.show(ex.message) will give you a nicer exception

  hope this helps

  
  
  
• Handling SQLException with a DataGridView