CODEWITHSUNDEEP
databaseasp.net-mvc-2foreign-keys
DiscoDude
DiscoDude

Reputation: 617

MVC: The INSERT statement conflicted with the FOREIGN KEY constraint

I have two tables that are supposed to be related.

enter image description here

Tables and Coloums Specification

Primary key table
ProductCategory
ProductCategoryID

Foreign key table
SubProductCategory2 ProductCategoryID

In the controller I have the following methods when creating sub category...

public ActionResult Create()
{
  ViewBag.ProductCategory = db.ProductCategories.OrderBy(p =>  
  p.ProductCategoryID).ToList();
  ViewBag.SubProductCategory2 = db.SubProductCategory2.OrderBy(a => 
  a.ProductCategoryID).ToList();

  var PC2 = new SubProductCategory2();
  return View(PC2);
} 

public ActionResult Create(SubProductCategory2 Createsubcat2, 
FormCollection values)
{
  if (ModelState.IsValid)
  {
    db.AddToSubProductCategory2(Createsubcat2);
    db.SaveChanges(); 
      //error pointing here and the full error message I am getting is...
     /*error: System.Data.SqlClient.SqlException: 
     * The INSERT statement conflicted with the FOREIGN KEY constraint 
     * "FK_SubProductCategory2_ProductCategory". The conflict occurred in  
     * database "MyHouseDB", table "dbo.ProductCategory", column 
     * 'ProductCategoryID'. The statement has been terminated.*/    
     return RedirectToAction("/");
   }

   ViewBag.ProductCategory = db.ProductCategories.OrderBy(p => 
   p.ProductCategoryID).ToList();
   ViewBag.SubProductCategory2 = db.SubProductCategory2.OrderBy(a => 
   a.ProductCategoryID).ToList();

   return View(Createsubcat2);
  }
 ViewBag.ProductCategory = db.ProductCategories.OrderBy(p => 
    p.ProductCategoryID).ToList();
    ViewBag.SubProductCategory2 = db.SubProductCategory2.OrderBy(a => 
    a.ProductCategoryID).ToList();
    return View(Createsubcat2);

in the views I have the following code...

<div class="editor-label">
  @Html.LabelForModel()
</div>
<div class="editor-field">
   @Html.DropDownList("CategoryName", new   
   SelectList((System.Collections.IEnumerable)ViewData["ProductCategory"], 
   "ProductCategoryID", "CategoryName"))
   @Html.ValidationMessageFor(model => model.ProductCategory.CategoryName)

Could some tell me how to solve the The INSERT statement conflicted with the FOREIGN KEY constraint error message. Correct me if I'm wrong, have I created the relationship between two tables incorrectly or the problem else where? Thanks in advance.

Upvotes: 0

Views: 8414

Answers (2)

DiscoDude
DiscoDude

Reputation: 617

I have found the problem. It was my sql database design not MVC coding side. I removed CategoryName column from SubCategory table. I could have felt the CategoryName as long as Allow Null was set to true. There was no point in doing that has PrimaryKey had been setup correctly for the two tables.

Upvotes: 0

iamkrillin
iamkrillin

Reputation: 6876

This error happens when the following conditions are true 1) The value that you selected for "ProductCategoryID" is not present in the "ProductCategory" table OR 2) The product category table is empty.

Does you have values in the product category table?

What value are you choosing for ProductCategoryID?

Upvotes: 1

Related Questions