Handle foreign key violation

When you delete a record that is being referenced by other tables, you will have a foreign key violation. The following code handles the error:

Protected Sub gvCompetencies_OnRowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)

Dim TrainingCompetencyId As Integer = Convert.ToInt32(gvCompetencies.DataKeys(e.RowIndex).Value)

Try

TrainingCompetencyElementDAL.Instance.DeleteByTrainingCompetencyID(TrainingCompetencyId)

TrainingCompetency_EventTypesDAL.Instance.DeleteByTrainingCompetencyID(TrainingCompetencyId)

TrainingCompetencyDAL.Instance.Delete(TrainingCompetencyId)

bind()

Catch ex As System.Data.SqlClient.SqlException

If ex.Number = 547 Then 'handle foreign key violation(547)

Dim comp As TrainingCompetency = TrainingCompetencyDAL.Instance.GetTrainingCompetency(TrainingCompetencyId)

lblMsg.Text = comp.Name + " cannot be deleted as it is being used by other tables."

lblMsg.ForeColor = Drawing.Color.Red

End If

End Try

End Sub