Issues with foreign keys when adding to database in MVC

Question

I have a function (addItem) which is suppposed to add a new row to my database. But when the function runs, it gives me this error:

{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK1\". The conflict occurred in database \"C:\\USERS\\ALAL0006\\DOWNLOADS\\SOA PROJEKT\\SOA PROJEKT\\SOA PROJEKT\\APP_DATA\\DATABASE1.MDF\", table \"dbo.Category\", column 'Id'.\r\nThe statement has been terminated."}

Controller:

public ActionResult Create()
    {
        IEnumerable<SelectListItem> categories = tbl.Category.Select(c => new SelectListItem
        {
            Value = SqlFunctions.StringConvert((double)c.Id).Trim(),
            Text = c.Namn
        });
        //ViewBag.Category = new SelectList(tbl.Category, "Id", "Namn");
        ViewBag.Id = categories;
        return View();
    }
    [HttpPost]
    public ActionResult Create(Vara newItm)
    {
        if (ModelState.IsValid)
        {
            srvc.addItem(newItm.Namn, newItm.Pris, newItm.CategoryID);
            return RedirectToAction("Create");
        }
        else
        {
            return View();
        }
    }

Service:

public void addItem(string name, int price, int ctgID)
    {
        Database1Entities1 tbl = new Database1Entities1();
        Vara newItm = new Vara() {

            Namn = name,
            Pris = price,
            CategoryID = ctgID,

        };
        tbl.Vara.Add(newItm);
        try
        {
            tbl.SaveChanges();
        }

cshtml file:

        <div class="form-group">
        @Html.LabelFor(model => model.Namn, new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.Namn)
            @Html.ValidationMessageFor(model => model.Namn)
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Pris, new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.Pris)
            @Html.ValidationMessageFor(model => model.Pris)
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.CategoryID, "CategoryID", new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.DropDownList("Id", "Välj kategori")
            @Html.ValidationMessageFor(model => model.CategoryID)
        </div>
    </div>

Category DB:

CREATE TABLE [dbo].[Category] (
[Id]   INT           IDENTITY (1, 1) NOT NULL,
[Namn] VARCHAR (MAX) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));

Item DB:

CREATE TABLE [dbo].[Vara] (
[Id]         INT           IDENTITY (1, 1) NOT NULL,
[Namn]       VARCHAR (MAX) NOT NULL,
[Pris]       INT           NOT NULL,
[CategoryID] INT           NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK1] FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[Category] ([Id]));

I am unsure of what I am supposed to do to fix this. Any ideas?

@Zaki It does exist, has the name "Id" in the Category table and it is the primary key. — Alan A., Apr 29 '16 at 11:58

score 0 · Answer 1 · answered Apr 29 '16 at 12:10

You have empty model.CategoryID in

@Html.ValidationMessageFor(model => model.CategoryID)

because you stored list of categories to ViewBag.Id and showed View with empty model:

 ViewBag.Id = categories;
 return View();

Your post is generated with ctgID = 0 (default value of integer). In save step to database via EntityFramework your insert probably failed, because your Category table hasn't row with Id = 0. (default first row Id in MSSQL is 1)

score 0 · Accepted Answer · edited May 23 '17 at 12:31

Your problem is that in the POST method, the value of newItm.CategoryID is 0 (the default for int) because you never generate a form control for property CategoryID (the <select> element you generating is for property Id).

You need to change the code in your view to (always use the strongly typed xxxFor() methods)

@Html.DropDownListFor(m => m.CategoryID, (IEnumerable<SelectListItem>)ViewBag.Id, "Välj kategori")

In addition, you need to modify your POST method to reassign the value of ViewBag.Id if ModelState is invalid and you return the view, otherwise an exception will be thrown

[HttpPost]
public ActionResult Create(Vara newItm)
{
    if (ModelState.IsValid)
    {
        ....
    }
    ViewBag.Id = ... // same code as used in the GET method.
    return View(newItm);
}

However a better approach is to use view models (refer What is ViewModel in MVC?).

public class VaraVM
{
    public int? ID { get; set; }
    ....
    [Display(Name = "Category")]
    [Required(ErrorMessage = "Please select a category")]
    public int SelectedCategory { get; set; }
    public IEnumerable<SelectistItem> CategoryList { get; set; }   
}

and in the view

@Html.DropDownListFor(m => m.SelectedCategory , Model.CategoryList, "Välj kategori")

and in the controller

public ActionResult Create()
{
    VaraVM model = new VaraVM();
    ConfigureViewModel(model);
    return View(model);
}

[HttpPost]
public ActionResult Create(VaraVM model)
{
    if (!ModelState.IsValid)
    {
        ConfigureViewModel(model);
        return View(model);  
    }
    // map the view model to a new instance of the data model
    // save and redirect
}

private void ConfigureViewModel(VaraVM model)
{
    model.CategoryList = .... // your query
}

score -1 · Answer 3 · answered Apr 29 '16 at 12:01

-1

You send Vara object CategoryID, because CategoryID Foreign key in Category table, Category table have been you send CategoryID

answered Apr 29 '16 at 12:01

Alper Şaldırak

1,034
8
10

2

It's very hard to understand what you are saying and not enough detail. – Clint B Apr 29 '16 at 12:11

Issues with foreign keys when adding to database in MVC

3 Answers3