CODEWITHSUNDEEP
c#sqlasp.net
LeeO123
LeeO123

Reputation: 5

Handling SQLException for Foreign Key Constraint

I am trying to handle the exception when I delete a record where my KitID is referenced by other records in my Hardware table. I have set up the relationships in the SQL Server Express database.

The error I am receiving is

"The DELETE statement conflicted with the REFERENCE constraint "FK_Hardware_Kit". The conflict occurred in database "C:\WEBSITES\GEOQ\APP_DATA\ASPNETDB.MDF", table "dbo.Hardware", column 'KitID'. The statement has been terminated."

I expect to receive that error but I am wondering why my code to handle the error is not working.

I have set up some breakpoints in the TRY and the CATCH. The SQL Statement executes just fine when there are no conflicts but when there is a conflict it will not go into the catch block at all.

I feel that I am missing something very trivial.

CODE:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class Controls_Control_Admin_Kit : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void KitListView_OnItemDeleting(object sender, ListViewDeleteEventArgs e)
    {
        try
        {
            string deleteCommand = "DELETE FROM [Kit] WHERE [KitID] = @KitID";
            SqlDataSource1.DeleteCommand = deleteCommand;
        }
        catch (SqlException SqlEx)
        {
            switch (SqlEx.Number)
            {
                case 547:
                    // Do something.
                    ErrorLabel.Text = "Error: There are hardware items associated with this Kit. <br />You must change the Kit field of each of the hardware items prior to deleting it. " + HttpUtility.UrlEncode("http://www.google.com/search?q=Kits") + " that require changing";
                    break;
                default:
                    throw;
            }
        }
    }

}

Upvotes: 0

Views: 3931

Answers (1)

Steve Wellens
Steve Wellens

Reputation: 20620

You are not actually deleting the record in the try section.

OnItemDeleting triggers BEFORE an attempt is made to delete the record.

You need to handle the OnItemDeleted event.

The documentation here shows you how to handle an exception: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.listview.itemdeleted%28v=vs.110%29.aspx

Upvotes: 1

Related Questions