How can I prevent this exception from happening inasp.netmvc web application?
System.Data.Entity.Infrastructure.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'
Inner 1: UpdateException: An error occurred while updating the entries. See the inner exception for details.
Inner 2: SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_GoodsManifest_CMR". The conflict occurred in database "eTransport", table "CMR.CMR", column 'CMR_ID'.
The statement has been terminated.
This happens when table CMR.CMR in database is empty and user tries to insert a new row in table GoodsManifest.
Most likely the code is trying to insert a record where the foreign key does not exist in the related table. Perhaps the key value defaults to zero. Anyway, you need to fix the code to populate the foreign key before saving the record.
DJ_B
This happens when table CMR.CMR in database is empty and user tries to insert a new row in table GoodsManifest.
Maybe you need to insert a record into related table? We cannot see your code or your DB schema. If someone else created this application maybe you can get with that team and ask for assistance setting up the DB with data?
Actually it's my own work. And yes, this happens when foreign key doesn't exist in the related table. My question is how can I prevent this in the application in the earlier described case? I can set the
[Required] data annotation in my application for text boxes, but it seems it doesn't work for
DropDownList.
Also I apologize for not posting any code or DB schema. The reason is that parts of code and DB schema is not written in English.
This sounds like is a design issue. Insert a record in to the related table before inserting the foreign key record.
DJ_B
My question is how can I prevent this in the application in the earlier described case? I can set the
[Required] data annotation in my application for text boxes, but it seems it doesn't work for
DropDownList.
Is the problem you expect a model validation error? The community cannot see your code and we have no idea how your application is supposed to work.
According to your needs, you need to check
whether the corresponding foreign key is empty before submitting.Since you did not provide any code, I made an example for your reference.
You need to implement client side validation in ASP.NET MVC.In this way, when you submit the form, if one of your non-nullable fields has no value, the page will directly give an error message and will not be
submitted to the action.You need to reference the three js files "jquery-3.4.1.min.js", "jquery.validate.min.js" and "jquery.validate.unobtrusive.min.js".For
more information, you can refer to this link.
In addition, in my example I used modal, at this time, you have to do one more step: add the form to the page, and then manually register the form.If you are directly on a page, you only need to quote the three
files mentioned above.
$.validator.unobtrusive.parse("#modelEdit");
The value of dropDownList can be verified, you can refer to the example I gave to modify your code. In my example,
StandardRefId is a foreign key.
Model
public class Student
{
public int StudentID { get; set; }
public string StudentName { get; set; }
[Required(ErrorMessage = "You must select.")]public int StandardRefId { get; set; }
[ForeignKey("StandardRefId")]
public Standard Standard { get; set; }
}
public class Standard
{
public int StandardId { get; set; }
public string StandardName { get; set; }
public ICollection<Student> Students { get; set; }
}
Controeler
public ActionResult Index()
{
var standardlist=db.Standards.ToList();
ViewBag.Standard = standardlist;
return View(db.Students.ToList());
}
public ActionResult Edit(int StudentID)
{
var standardlist = db.Standards.ToList();
ViewBag.Standard = standardlist;
var currentStudent = db.Students.Where(s => s.StudentID == StudentID).FirstOrDefault();
return PartialView(currentStudent);
}
[HttpPost]
public ActionResult Edit(Student student)
{
if (ModelState.IsValid)
{
//Edit code here
}
return RedirectToAction("Index");
}
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
1 Points
2 Posts
SqlException in asp.net MVC web application
Jul 10, 2020 11:35 AM|DJ_B|LINK
How can I prevent this exception from happening in asp.net mvc web application?
System.Data.Entity.Infrastructure.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'
Inner 1: UpdateException: An error occurred while updating the entries. See the inner exception for details.
Inner 2: SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_GoodsManifest_CMR". The conflict occurred in database "eTransport", table "CMR.CMR", column 'CMR_ID'.
The statement has been terminated.
This happens when table CMR.CMR in database is empty and user tries to insert a new row in table GoodsManifest.
All-Star
53751 Points
24069 Posts
Re: SqlException in asp.net MVC web application
Jul 10, 2020 11:45 AM|mgebhard|LINK
Most likely the code is trying to insert a record where the foreign key does not exist in the related table. Perhaps the key value defaults to zero. Anyway, you need to fix the code to populate the foreign key before saving the record.
Maybe you need to insert a record into related table? We cannot see your code or your DB schema. If someone else created this application maybe you can get with that team and ask for assistance setting up the DB with data?
Member
1 Points
2 Posts
Re: SqlException in asp.net MVC web application
Jul 10, 2020 12:00 PM|DJ_B|LINK
@mgebhard
Actually it's my own work. And yes, this happens when foreign key doesn't exist in the related table. My question is how can I prevent this in the application in the earlier described case? I can set the
[Required]data annotation in my application for text boxes, but it seems it doesn't work forDropDownList.Also I apologize for not posting any code or DB schema. The reason is that parts of code and DB schema is not written in English.
All-Star
53751 Points
24069 Posts
Re: SqlException in asp.net MVC web application
Jul 10, 2020 12:12 PM|mgebhard|LINK
This sounds like is a design issue. Insert a record in to the related table before inserting the foreign key record.
Is the problem you expect a model validation error? The community cannot see your code and we have no idea how your application is supposed to work.
All-Star
48740 Points
18193 Posts
Re: SqlException in asp.net MVC web application
Jul 10, 2020 02:36 PM|PatriceSc|LINK
Hi,
Which browser? Try https://caniuse.com/#feat=mdn-api_htmlselectelement_required for browser side support but even if not supported by your browser it shoudln't pass server side model validation. Are you calling ModelState.IsValid ?
Contributor
3090 Points
879 Posts
Re: SqlException in asp.net MVC web application
Jul 15, 2020 05:57 AM|YihuiSun|LINK
Hi DJ_B,
According to your needs, you need to check whether the corresponding foreign key is empty before submitting.Since you did not provide any code, I made an example for your reference.
$.validator.unobtrusive.parse("#modelEdit");Model
public class Student { public int StudentID { get; set; } public string StudentName { get; set; } [Required(ErrorMessage = "You must select.")] public int StandardRefId { get; set; } [ForeignKey("StandardRefId")] public Standard Standard { get; set; } } public class Standard { public int StandardId { get; set; } public string StandardName { get; set; } public ICollection<Student> Students { get; set; } }Controeler
public ActionResult Index() { var standardlist=db.Standards.ToList(); ViewBag.Standard = standardlist; return View(db.Students.ToList()); } public ActionResult Edit(int StudentID) { var standardlist = db.Standards.ToList(); ViewBag.Standard = standardlist; var currentStudent = db.Students.Where(s => s.StudentID == StudentID).FirstOrDefault(); return PartialView(currentStudent); } [HttpPost] public ActionResult Edit(Student student) { if (ModelState.IsValid) { //Edit code here } return RedirectToAction("Index"); }Index
@model IEnumerable<WebApplication5.Models.Student> <table class="table"> <tr><td>StudentID</td><td>StudentName</td><td>Standard</td><td></td></tr> @foreach (var item in Model) { <tr> <td>@Html.DisplayTextFor(m => item.StudentID)</td> <td> @Html.DisplayTextFor(m => item.StudentName) </td> <td> @Html.DropDownListFor(m => item.StandardRefId, new SelectList(ViewBag.Standard, "StandardId", "StandardName", item.StandardRefId), "Please select") </td> <td><button onclick="getEdit(@item.StudentID)">Edit</button></td> </tr> } </table> <div id="modelEdit" class="modal fade" tabindex="-1" role="dialog"> <div class="modal-dialog" role="document"> <div class="modal-content"> </div> </div> </div> @section scripts{ <script src="~/Scripts/jquery.validate.min.js"></script> <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> <script> function getEdit(StudentID) { $.ajax({ url: "@Url.Action("Edit")", data: { StudentID: StudentID}, type: "GET", success: function (data) { $(".modal-content").html(data); $("#modelEdit").modal('show'); $('.modal-content').change( function () { $.validator.unobtrusive.parse("#modelEdit"); }); } }); } </script> }Edit
@model WebApplication5.Models.Student @using (Html.BeginForm("Edit", "Test17", FormMethod.Post)) { @Html.HiddenFor(m => Model.StudentID) <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button> <h4 class="modal-title">Edit</h4> </div> <div class="modal-body"> <div class="row"> <div class="col-md-4">StudentID</div> <div class="col-md-4">@Html.DisplayTextFor(m => Model.StudentID)</div> </div> <div class="row"> <div class="col-md-4">StudentName</div> <div class="col-md-4">@Html.TextBoxFor(m => Model.StudentName)</div> <div class="col-md-4">@Html.ValidationMessageFor(m => Model.StudentName)</div> </div> <div class="row"> <div class="col-md-4">Standard</div> <div class="col-md-4">@Html.DropDownListFor(m => Model.StandardRefId, new SelectList(ViewBag.Standard, "StandardId", "StandardName", Model.StandardRefId), "Please select")</div> <div class="col-md-4">@Html.ValidationMessageFor(m => Model.StandardRefId)</div> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Close</button> <button type="submit" class="btn btn-primary">Save changes</button> </div> }Here is the result.
Best Regards,
YihuiSun