Inserting into child table getting foreign key error using EF and dbcontext

Question

Hello I'm fairly new to ASP MVC so I'm a little stuck resolving this issue.

The table structure has a job as the parent and then task assigned within that job (jobdetails) as the child. The jobsdetail table has a foreignkey (jobsid) to the jobs table.

When I try to save a record, I get

Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.JobDetails_dbo.Jobs_jobsid". The conflict occurred in database "NavTracking", table "dbo.Jobs", column 'Id'. The statement has been terminated."

I suspect the issue is on the : _context.JobDetail.Add(JobDetail); line because its trying to insert a jobsid but not sure.

Here is my class:

public class JobDetails
{
    public int Id { get; set; }

    [ForeignKey("jobsid")]
    public virtual Jobs Job { get; set; }

    public int jobsid { get; set; }

    public JobType jobtype { get; set; }

    public int jobtypeid { get; set; }

    public Status status { get; set; }

    public int? statusID { get; set; }

    public DateTime? ReqDate { get; set; }

    public int? EtcStatus { get; set; }

    public Analysts Analyst { get; set; }

    public int? analystID { get; set; }

    public Priority priority { get; set; }

    public int? priorityID { get; set; }

    public bool IsTaskComplete { get; set; }

    public string SpecialNotes { get; set; }
}

My Controller to save the record:

    [HttpPost]
    public ActionResult SaveTask(JobTaskViewModel JobDetVM)
    {
        var jobex = _context.Job.SingleOrDefault(j => j.Id == 
        if (JobDetVM.Jobdetail.Id == 0)
        {
            JobDetVM.Jobdetail.jobsid=jobex.Id;
            _context.JobDetail.Add(JobDetVM.Jobdetail);
        }
        else
        {
            var jobdetailsInDB = _context.JobDetail.Single(t => t.Id == JobDetail.Id);
            jobdetailsInDB.jobsid = JobDetail.jobsid;
            jobdetailsInDB.jobtypeid = JobDetail.jobtypeid;
            jobdetailsInDB.statusID = JobDetail.statusID;
            jobdetailsInDB.ReqDate = JobDetail.ReqDate;
            jobdetailsInDB.EtcStatus = JobDetail.EtcStatus;
            jobdetailsInDB.analystID = JobDetail.analystID;
            jobdetailsInDB.priorityID = JobDetail.priorityID;
            jobdetailsInDB.IsTaskComplete = JobDetail.IsTaskComplete;
            jobdetailsInDB.SpecialNotes = JobDetail.SpecialNotes;
        }
        _context.SaveChanges();

        return RedirectToAction("Index", "Job");
    }

and the View to post the data:

@using (Html.BeginForm("SaveTask", "Job"))
{
    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.jobtypeid)
        @Html.DropDownListFor(m => m.Jobdetail.jobtypeid, new SelectList(Model.Jobtype, "Id", "JobTypeVal"), "Select Job Type", new { @class = "form-control" })
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.ReqDate)
        @Html.TextBoxFor(m => m.Jobdetail.ReqDate, new { @class = "form-control" })
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.EtcStatus)
        @Html.TextBoxFor(m => m.Jobdetail.EtcStatus, new { @class = "form-control" })
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.analystID)
        @Html.DropDownListFor(m => m.Jobdetail.analystID, new SelectList(Model.Analyst, "Id", "AnalystName"), "Select Analyst", new { @class = "form-control" })
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.priorityID)
        @Html.DropDownListFor(m => m.Jobdetail.priorityID, new SelectList(Model.Priority, "Id", "PriorityVal"), "Select Priority", new { @class = "form-control" })
    </div>

    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.statusID)
        @Html.DropDownListFor(m => m.Jobdetail.statusID, new SelectList(Model.Status, "Id", "StatusVal"), "Select Status", new { @class = "form-control" })
    </div>

    <div class="form-group">
        @Html.LabelFor(m => m.Jobdetail.SpecialNotes)
        @Html.TextBoxFor(m => m.Jobdetail.SpecialNotes, new { @class = "form-control" })
    </div>


    @Html.HiddenFor(m => m.Jobdetail.Id)
    @Html.HiddenFor(m => m.Jobdetail.jobsid)
    <button type="submit" class="btn btn-primary">Save</button>

}

My View Model:

   public class JobTaskViewModel
    {
        public JobDetails Jobdetail { get; set; }

        public Jobs Job { get; set; }

        public int jobsID { get; set; }

        public IEnumerable<Status> Status { get; set; }

        public IEnumerable<Analysts> Analyst { get; set; }

        public IEnumerable<JobType> Jobtype { get; set; }

        public IEnumerable<Priority> Priority { get; set; }



    }

You could reduce the amount of code you show and add another relevant detail: the content of `JobDetVM.Jobdetail`: its `jobid` is where the shoe pinches. — Gert Arnold, Nov 11 '17 at 10:13

Erik Philips · Answer 1 · 2017-11-10T22:53:27.793

Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.JobDetails_dbo.Jobs_jobsid". The conflict occurred in database "NavTracking", table "dbo.Jobs", column 'Id'. The statement has been terminated."

There error/exception is pretty clear. You have a database named NavTacking with a constraint named FK_dbo.JobDetails_dbo.Jobs_jobsid that the record fails to adhere to. You are inserting a record into dob.JobDetails. The insert fails because of the the column (I think) jobsid has a value specified that does not exist in the table dbo.Jobs in the column Id.

For example:

var jobDetails = new JobDetails
{
  jobsid = 12;
};

Jobs table:

Columns
Id    Name    Value
-------------------------
1     Test    Blah
2     Foo     Long Foo
3     Bar     Long Bar

The above jobDetails cannot be saved to the DB because there are no jobs with an Id of 12.

Steve Greene · Answer 2 · 2017-11-10T20:43:30.343

First, I would recommend not directly editing entity models in your views. I would use the ViewModel pattern described here and here.

The problem with your current code is you are not fetching JobDetails.Job so it is not being tracked. You could either attach it and set it's state or simply fetch the job and use it:

[HttpPost]
public ActionResult SaveTask(JobDetails JobDetail)
{
    var job = _context.Jobs.Single(j => j.Id == JobDetail.jobsid);
    if (JobDetail.Id == 0)
        JobDetail.Job = job;
        _context.JobDetail.Add(JobDetail);
    else
    {
        var jobdetailsInDB = _context.JobDetail
               .Include(jd => jd.Job)
               .Single(t => t.Id == JobDetail.Id);            
        jobdetailsInDB.jobtypeid = JobDetail.jobtypeid;
        jobdetailsInDB.statusID = JobDetail.statusID;
        jobdetailsInDB.ReqDate = JobDetail.ReqDate;
        jobdetailsInDB.EtcStatus = JobDetail.EtcStatus;
        jobdetailsInDB.analystID = JobDetail.analystID;
        jobdetailsInDB.priorityID = JobDetail.priorityID;
        jobdetailsInDB.IsTaskComplete = JobDetail.IsTaskComplete;
        jobdetailsInDB.SpecialNotes = JobDetail.SpecialNotes;
    }
    _context.SaveChanges();

    return RedirectToAction("Index", "Job");
}

I changed to a VM pattern but still getting a foreign key issue. Do I need to pass anything from the form to this method? The method is using the same VM. It just seems like the jobid is not passing the method — Cypherwolf, Nov 10 '17 at 22:31
So you fetch your JobDetail in the controller GET and map it to your ViewModel. Then you show your View with the HiddenFor on the jobid. In your POST, if the model is valid, you either create the jobsdetail or update it. So debug it through those steps and make sure the jobid is there right before you go to the view, it is in the hidden input on the view and then it is POSTed back. Show your GET and POST methods as they stand. — Steve Greene, Nov 10 '17 at 22:52

score 0 · Answer 3 · answered Nov 16 '17 at 14:23

0

Ok I found the answer. I needed to pass the Job_id in the View back to the controller so my submit looks like this:

@Html.HiddenFor(m => m.Job.Id)
<button type="submit" value="Job.Id" class="btn btn-primary">Save</button>

Thanks for the help

answered Nov 16 '17 at 14:23

Cypherwolf

25
7

Inserting into child table getting foreign key error using EF and dbcontext

3 Answers3