CODEWITHSUNDEEP
asp.net-mvcforeign-keyssql
109221793
109221793

Reputation: 16887

SQL Error: The Insert Statement Conflicted with the Foreign Key constraint

I knew this was a problem I would run into and I'm just not sure how to solve it. I have two tables with the following structures:

dbo.File
FileID            int
Title             nvarchar(50)
ISBN              nvarchar(50)
UploadDate        datetime
UserName          nvarchar(50)

dbo.Cover
CoverID           int
CoverFileContent  varbinary(max)
CoverMimeType     nvarchar(50)
CoverFileName     nvarchar(50)
FileID            int

The file table has a one to many relationship with the cover table, and FileID in the Cover table references FileID in the File table.

So I also have an MVC application. In this application, on one screen I insert the File Data, then in the following screen I upload a file, and insert this into the cover table, however I get the following error:

InnerException {"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Cover_File\". The conflict occurred in database \"SampleAppDB\", table \"dbo.File\", column 'FileID'.\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}

I know what it means, I've just gone blank as to how I actually perform this insert statement to correspond with the File table. Currently, I'm not inserting anything into the FileID foreign key in the Cover table because I'm not sure how to go about it. Here is a snippet from the Cover controller where the upload of the cover file occurs:

[HttpPost]
public ActionResult CreateCover(FormCollection formvalues)
{
    Cover cover = new Cover();

    cover.CoverMimeType = Request.Files["CoverUpload"].ContentType;
    Stream fileStream = Request.Files["CoverUpload"].InputStream;
    cover.CoverFileName = Path.GetFileName(Request.Files["CoverUpload"].FileName);
    int fileLength = Request.Files["CoverUpload"].ContentLength;
    cover.CoverFileContent = new byte[fileLength];
    fileStream.Read(cover.CoverFileContent, 0, fileLength);

    filerepository.AddCoverData(cover);

    filerepository.Save();

    return View(cover);
}

any advice on how to handle this problem would be greatly appreciated, if anyone needs me to post more info too, just ask.

As requested: filerepository.cs

private SampleAppDBEntities entities = new SampleAppDBEntities();

        public void AddCoverData(Cover cover)
        {
            entities.Covers.AddObject(cover);
        }

Upvotes: 0

Views: 2114

Answers (2)

Lorenzo
Lorenzo

Reputation: 29427

You could have the FileID passed within your FormCollection values as an hidden param and then have a Cover controller action that initialize it.

For example:

From the File detail page add a link to create a Cover

<a href="#" rel='<%= Url.Content("~/Cover/AddCover/FILEID_HERE") %>'>Add cover</a>

then you can have a get action on the Cover controller that simply initialize the cover FileID and return the AddCover view

[HttpGet]
public ActionResult AddCover(int id) {
    CoverModel cm = new CoverModel();
    cm.FileId = id;
    return View("AddCover", cm);
}

Finally within your Cover View, that should be typed to the CoverModel simply add the FileID as an hidden field so you can have it posted with the rest of your form data.

<% using (Html.BeginForm("AddCover", "Cover", FormMethod.Post, new {} ))  { %>
    <%= Html.HiddenFor(model => model.FileID) %>

    //Place your cover form fields here

<% } %>

One effect of using this pattern is that you can take advantage of using the default model binder features of MVC and have a post action like this one instead of using the FormCollection class:

[HttpPost]
public ActionResult AddCover( CoverModel model ) {
   //Validate your data and tehn add to database
}

Upvotes: 1

Darin Dimitrov
Darin Dimitrov

Reputation: 1038710

You need to post the fileId associated to this cover to the controller action:

cover.File = filerepository.GetFile(fileId);

Upvotes: 1

Related Questions