UPDATE statement conflict with FOREIGN KEY constraint in ASP.NET MVC4

Question

I am actually working on an ASP.NET MVC4 application where we used the entity framework and the Model-View-View-Model approach and 7-layered architecture. We have one page where we need to insert or update "Product" information. The inserted or updated Product data will be saved inside "Product" table. My database name is "DbOnix". The basic structure of Product Table is given below:

 Column Name               Data Type      Allow Nulls
ProductID PK                  int           
ProductName                varchar(255)       NO
ProductCategoryID FK          int             
Sequence                      int             YES
ActiveStatus                  int             YES
SlNo                          int             NO

The ProductCategoryID column in Product table has a foreign key relationship with the ProductCategory table. The basic structure of ProductCategory table:

    Column Name                Data Type          Allow Nulls
 ProductCategoryID PK             int                          
 ProductCategoryName            varchar(150)          NO

Whenever I'm trying to insert or update data in the Product table, the following exception is thrown:

The UPDATE statement conflicted with the FOREIGN KEY constraint   "FK_Product_ProductCategory". The conflict occurred in database "DbOnix", table "dbo.ProductCategory", column 'ProductCategoryID'.The statement has been terminated.

My Controller Code:

    public HttpStatusCodeResult UpdateProductInformation(int id, ProductDTO ProductDTO)
    {
        _productManager.UpdateProductInformation(id, ProductDTO);
        return new HttpStatusCodeResult(HttpStatusCode.OK);
    }

My Manager Class Code:

    public void UpdateProductInformation(int id, ProductDTO productDTO)
    {
        //if productDTO data is not valid
        if (productDTO == null)
            throw new  ArgumentException(Messages.warning_CannotAddProfileWithNullInformation);

        //Create a new product entity
        var currentProduct = _ProductRepository.Get(id);

        var updatedProduct = new Product();
        updatedProduct.ProductID = id;
        updatedProduct.ProductName = productDTO.ProductName;
        updatedProduct.ProductCategoryID = productDTO.ProductCategoryID;
        updatedProduct.Sequence = productDTO.Sequence;
        updatedProduct.ActiveStatus = productDTO.ActiveStatus;
        updatedProduct.SlNo = productDTO.SlNo;

        //Update Product
        updatedProduct = this.UpdateProduct(currentProduct, updatedProduct);

    }

My Core (Property) Class code:

public partial class Product : Entity, IValidatableObject
{


    public Product()
    {

    }


    [Key]
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int ProductCategoryID { get; set; }
    public int Sequence { get; set; }
    public int ActiveStatus { get; set; }
    public int SlNo { get; set; }
}

and my DTO Class code:

public class ProductDTO
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int ProductCategoryID { get; set; }
    public int Sequence { get; set; }
    public int ActiveStatus { get; set; }
    public int SlNo { get; set; }
}

Note that my Database Server is MS SQL Server 2008 R2.

EDIT-1: I forgot to include my Javascript code:

$(function () {



var Product = function (Product) {
    var self = this;
    self.ProductID = ko.observable(Product ? Product.ProductID : 0).extend({ required: true });
    self.ProductName = ko.observable(Product ? Product.ProductName : '').extend({ required: true });
    self.ActiveStatus = ko.observable(Product ? Product.ActiveStatus : 0);

};

var ProductCollection = function () {
    var self = this;

    //if ProfileId is 0, It means Create new Profile
    if (ProductID == 0) {
        self.Product = ko.observable(new Product());
    }
    else {
        $.ajax({
            url: urlProduct + '/GetProductById/' + ProductID,
            async: false,
            dataType: 'json',
            success: function (json) {
                self.Product = ko.observable(new Product(json));
            }
        });
    }

    self.ProductErrors = ko.validation.group(self.Product());





    self.saveProduct = function () {
        var isValid = true;

        if (self.ProductErrors().length != 0) {
            self.ProductErrors.showAllMessages();
            isValid = false;
        }



        if (isValid) {

            self.Product().ActiveStatus = document.getElementById("stat").value;

            $.ajax({
                type: (ProductID > 0 ? 'PUT' : 'POST'),
                cache: false,
                dataType: 'json',
                url: urlProduct + (ProductID > 0 ? '/UpdateProductInformation?id=' + ProductID : '/SaveProductInformation'),
                data: JSON.stringify(ko.toJS(self.Product())),
                contentType: 'application/json; charset=utf-8',
                async: false,
                success: function (data) {
                    alert("Product saved successfully.");
                    window.location.href = '/Product';
                },
                error: function (err) {
                    var err = JSON.parse(err.responseText);
                    var errors = "";
                    for (var key in err) {
                        if (err.hasOwnProperty(key)) {
                            errors += key.replace("Product.", "") + " : " + err[key];
                        }
                    }
                    $("<div></div>").html(errors).dialog({ modal: true, title: JSON.parse(err.responseText).Message, buttons: { "Ok": function () { $(this).dialog("close"); } } }).show();
                },
                complete: function () {
                }
            });
        }
    };
};


var ProductsViewModel = function () {
    var self = this;
    var url = "/Product/GetAllProduct";
    var refresh = function () {
        $.getJSON(url, {}, function (data) {
            self.Products(data);
        });
    };

    // Public data properties
    self.Products = ko.observableArray([]);

    // Public operations
    self.createProduct = function () {
        window.location.href = '/Product/ProductCreateEdit/0';
    };

    self.editProduct = function (product) {
        //alert(product.ProductID);
        window.location.href = '/Product/ProductCreateEdit/' + product.ProductID;
    };

};

    ko.applyBindings(new ProductsViewModel(), document.getElementById("productlist"));
    ko.applyBindings(new ProductCollection(), document.getElementById("product_edit"));
});

Note that I used KnockoutJS v2.3.0

Answer 1

In your code ..

updatedProduct.ProductCategoryID = productDTO.ProductCategoryID;

there may be you are assigning a value (ProductCategoryID) which is not exists in the ProductCategory table. So please check whether you get the correct ProductCategories(check productDTO) from the database. The issue may be the value of your ProductCategoryID is 0. Thats why it says UPDATE statement conflicted with the FOREIGN KEY constraint