Delete data from two tables

Question

Hello Team,

I have two tables and am trying to delete from the two tables but I keep getting this response which says fail to delete data.

public ActionResult DeleteStaff(int StaffId, int NextOfKingId)
{
try
{
var DeleteNextOfKing = objBasicShoolDBEntities.tblNextOfKings.Where(a => a.NextOfKingId == NextOfKingId).FirstOrDefault<tblNextOfKing>();
if (DeleteNextOfKing != null)
{
objBasicShoolDBEntities.tblNextOfKings.Remove(DeleteNextOfKing);
}
var Staff = objBasicShoolDBEntities.tblStaffs.Where(a => a.StaffId == StaffId && a.NextOfKingId == NextOfKingId).FirstOrDefault<tblStaff>();
objBasicShoolDBEntities.tblStaffs.Remove(Staff);
objBasicShoolDBEntities.SaveChanges();
return Json(true, JsonRequestBehavior.AllowGet);
}
catch (Exception)
{
return Json(false, JsonRequestBehavior.AllowGet);
}
}

function DeleteStaff(StaffId, NextOfKingId) {
swal({
title: "Do you want to delete this staff data?",
showCancelButton: true,
confirmButtonColor: "#DD6B55",
confirmButtonText: 'Yes Delete',
cancelButtonText: 'No pls cancel',
confirmButtonClass: 'btn btn-success',
cancelButtonClass: 'btn btn-danger',
type: "warning",
closeOnConfirm: false,
closeOnCancel: false
}).then(
function (isConfirm) {
if (isConfirm.value) {
$.get("/Home/deleteStaff", { StaffId: StaffId, NextOfKingId: NextOfKingId }, function (res) {
if (res) {
dataTable.ajax.reload();
swal('Confirm', 'Staff deleted successfully', 'success');
}

// dataTable.ajax.reload();
}).fail(function () {
swal('Error', 'Failed to delete staff!', 'erro');
});
}
else {
swal('Cancelled', '', 'error');
}

});
}

Sarthak Varshney · Answer

Ensure Database Integrity : Ensure that your database constraints and foreign key relationships are correctly set up, and there are no issues with cascading deletes. Verify Picture Path Handling : Ensure that the Picture Path field is not causing issues during deletion, such as file locks or database constraints. Enhanced Logging : Add more detailed logging to understand where the deletion might be failing. Let's enhance the code with more detailed logging and ensure both DeleteNextOfKing and Staff objects are properly handled: Revised C# Code with Detailed Logging public ActionResult DeleteStaff(int StaffId, int NextOfKingId) { using (var transaction = objBasicShoolDBEntities.Database.BeginTransaction()) { try { // Find and remove NextOfKing var DeleteNextOfKing = objBasicShoolDBEntities.tblNextOfKings .FirstOrDefault(a => a.NextOfKingId == NextOfKingId); if (DeleteNextOfKing != null) { objBasicShoolDBEntities.tblNextOfKings.Remove(DeleteNextOfKing); } else { // Log or handle case where NextOfKing is not found Console.WriteLine("NextOfKing with ID " + NextOfKingId + " not found."); } // Find and remove Staff var Staff = objBasicShoolDBEntities.tblStaffs .FirstOrDefault(a => a.StaffId == StaffId && a.NextOfKingId == NextOfKingId); if (Staff != null) { objBasicShoolDBEntities.tblStaffs.Remove(Staff); } else { // Log or handle case where Staff is not found Console.WriteLine("Staff with ID " + StaffId + " and NextOfKingId " + NextOfKingId + " not found."); } objBasicShoolDBEntities.SaveChanges(); transaction.Commit(); return Json(true, JsonRequestBehavior.AllowGet); } catch (Exception ex) { // Rollback the transaction transaction.Rollback(); // Log the exception with detailed information Console.WriteLine("Exception occurred: " + ex.Message); Console.WriteLine("Stack Trace: " + ex.StackTrace); return Json(false, JsonRequestBehavior.AllowGet); } } } Verifying Picture Path Handling If the Picture Path might be causing issues (e.g., file locks, permission issues), make sure it's not being referenced or locked elsewhere in your application. JavaScript Function Update Ensure your JavaScript function correctly displays errors and catches responses: function DeleteStaff(StaffId, NextOfKingId) { swal({ title: "Do you want to delete this staff data?", showCancelButton: true, confirmButtonColor: "#DD6B55", confirmButtonText: 'Yes Delete', cancelButtonText: 'No pls cancel', confirmButtonClass: 'btn btn-success', cancelButtonClass: 'btn btn-danger', type: "warning", closeOnConfirm: false, closeOnCancel: false }).then( function (isConfirm) { if (isConfirm.value) { $.get("/Home/DeleteStaff", { StaffId: StaffId, NextOfKingId: NextOfKingId }, function (res) { if (res) { dataTable.ajax.reload(); swal('Confirm', 'Staff deleted successfully', 'success'); } else { swal('Error', 'Failed to delete staff!', 'error'); } }).fail(function (jqXHR, textStatus, errorThrown) { console.error("AJAX request failed: " + textStatus + ", " + errorThrown); swal('Error', 'Failed to delete staff! ' + errorThrown, 'error'); }); } else { swal('Cancelled', '', 'error'); } }); } Additional Troubleshooting Steps Database Constraints : Check for any foreign key constraints that might be preventing deletion. Database Logs : Check the database logs to see if there are any errors logged when the deletion attempt is made. Isolation of Issue : Temporarily simplify the deletion logic to isolate which part might be causing the problem. For example, try deleting just from one table first to see if the issue persists. public ActionResult DeleteStaff(int StaffId) { using (var transaction = objBasicShoolDBEntities.Database.BeginTransaction()) { try { // Find and remove Staff var Staff = objBasicShoolDBEntities.tblStaffs .FirstOrDefault(a => a.StaffId == StaffId); if (Staff != null) { objBasicShoolDBEntities.tblStaffs.Remove(Staff); } else { Console.WriteLine("Staff with ID " + StaffId + " not found."); } objBasicShoolDBEntities.SaveChanges(); transaction.Commit(); return Json(true, JsonRequestBehavior.AllowGet); } catch (Exception ex) { transaction.Rollback(); Console.WriteLine("Exception occurred: " + ex.Message); Console.WriteLine("Stack Trace: " + ex.StackTrace); return Json(false, JsonRequestBehavior.AllowGet); } } }

Emmmanuel FIADUFE · Answer

Hello Sarthak, Please as you can seen in the table here there is not null column here, I applied your format but is still not working, though I have a picture path saved in the table but i can't tell if that is the cause Detailed Exception Handling is not catching any error

Sarthak Varshney · Answer

To troubleshoot the issue with deleting records from your tables, let's examine a few key aspects of your code and make some suggestions for improvements. Check for Null Values : Ensure you are properly handling the case where the record to be deleted might not exist. Save Changes After Each Deletion : Consider saving changes after each deletion to pinpoint where the failure occurs. Detailed Exception Handling : Instead of a generic Exception catch, use specific exception types to get more information on what might be going wrong. Logging : Add logging statements to understand the flow and catch any issues. Here's an improved version of your method with these considerations: public ActionResult DeleteStaff(int StaffId, int NextOfKingId) { try { // Find the NextOfKing record var DeleteNextOfKing = objBasicShoolDBEntities.tblNextOfKings .FirstOrDefault(a => a.NextOfKingId == NextOfKingId); if (DeleteNextOfKing != null) { objBasicShoolDBEntities.tblNextOfKings.Remove(DeleteNextOfKing); objBasicShoolDBEntities.SaveChanges(); // Save changes after each deletion } else { // Log if not found // Example: logger.LogInformation("NextOfKing record not found."); } // Find the Staff record var Staff = objBasicShoolDBEntities.tblStaffs .FirstOrDefault(a => a.StaffId == StaffId && a.NextOfKingId == NextOfKingId); if (Staff != null) { objBasicShoolDBEntities.tblStaffs.Remove(Staff); objBasicShoolDBEntities.SaveChanges(); // Save changes after each deletion } else { // Log if not found // Example: logger.LogInformation("Staff record not found."); } return Json(true, JsonRequestBehavior.AllowGet); } catch (DbUpdateException dbEx) { // Handle database update specific exceptions // Example: logger.LogError(dbEx, "Database update exception occurred."); return Json(false, JsonRequestBehavior.AllowGet); } catch (Exception ex) { // Handle all other exceptions // Example: logger.LogError(ex, "An exception occurred."); return Json(false, JsonRequestBehavior.AllowGet); } } Frontend JavaScript Ensure the endpoint URL is correct ( /Home/deleteStaff should be /Home/DeleteStaff to match the action name): function DeleteStaff(StaffId, NextOfKingId) { swal({ title: "Do you want to delete this staff data?", showCancelButton: true, confirmButtonColor: "#DD6B55", confirmButtonText: 'Yes Delete', cancelButtonText: 'No pls cancel', confirmButtonClass: 'btn btn-success', cancelButtonClass: 'btn btn-danger', type: "warning", closeOnConfirm: false, closeOnCancel: false }).then(function (isConfirm) { if (isConfirm.value) { $.get("/Home/DeleteStaff", { StaffId: StaffId, NextOfKingId: NextOfKingId }, function (res) { if (res) { dataTable.ajax.reload(); swal('Confirm', 'Staff deleted successfully', 'success'); } else { swal('Error', 'Failed to delete staff!', 'error'); } }).fail(function () { swal('Error', 'Failed to delete staff!', 'error'); }); } else { swal('Cancelled', '', 'error'); } }); } Steps for Debugging Log Detailed Messages : Use logging to capture detailed information about the execution flow and any exceptions that occur. Check Database Constraints : Ensure there are no foreign key constraints or other database rules that prevent deletion. Verify Records Exist : Before attempting to delete, confirm that the records actually exist in the database. Use Debugger : Run the application in debug mode and step through the deletion process to see exactly where it fails. Check AJAX Call URL : Ensure the URL in the AJAX call matches the route configured in your application. By implementing these steps and the updated code, you should be able to identify and fix the issue preventing the deletion of records.

Emmmanuel FIADUFE · Answer

Well noted and thank you

Sarthak Varshney · Answer

It looks like the NextOfKingId column in the tblStaffs table allows null values, and this might not be directly related to the issue of failing to delete data from both tables. When referencing the NextOfKingId in your function, allowing nulls shouldn't prevent deletion unless there are specific constraints or dependencies in your database schema that are not being handled. Since you were able to successfully delete records with the updated query, it appears that the issue may lie with how the original function was structured or how parameters were being passed. To ensure that deletions work correctly when involving the NextOfKingId , let's revisit your original function and make sure it's robust enough to handle various scenarios: Check for Foreign Key Constraints: Ensure that there are no foreign key constraints that might prevent deletion. Use Transactions: Continue using transactions to ensure atomicity, so if any part of the deletion process fails, it rolls back. Detailed Logging: Add more detailed logging to capture errors effectively. Here's an improved version of your function with these considerations: public ActionResult DeleteStaff(int StaffId, int? NextOfKingId) { using (var transaction = objBasicShoolDBEntities.Database.BeginTransaction()) { try { // Find and remove NextOfKing record if NextOfKingId is provided if (NextOfKingId.HasValue) { var DeleteNextOfKing = objBasicShoolDBEntities.tblNextOfKings .FirstOrDefault(a => a.NextOfKingId == NextOfKingId.Value); if (DeleteNextOfKing != null) { objBasicShoolDBEntities.tblNextOfKings.Remove(DeleteNextOfKing); } else { Console.WriteLine("NextOfKing with ID " + NextOfKingId.Value + " not found."); } } // Find and remove Staff record var Staff = objBasicShoolDBEntities.tblStaffs .FirstOrDefault(a => a.StaffId == StaffId); if (Staff != null) { objBasicShoolDBEntities.tblStaffs.Remove(Staff); } else { Console.WriteLine("Staff with ID " + StaffId + " not found."); } objBasicShoolDBEntities.SaveChanges(); transaction.Commit(); return Json(true, JsonRequestBehavior.AllowGet); } catch (Exception ex) { transaction.Rollback(); Console.WriteLine("Exception occurred: " + ex.Message); Console.WriteLine("Stack Trace: " + ex.StackTrace); return Json(false, JsonRequestBehavior.AllowGet); } } } In this version: The NextOfKingId parameter is nullable ( int? ). If NextOfKingId is provided, it attempts to find and delete the NextOfKing record. It then finds and deletes the Staff record. It uses transactions to ensure that both deletions either succeed together or fail together. It includes detailed logging for better debugging. Ensure that the AJAX call sends both StaffId and NextOfKingId correctly. If NextOfKingId is not always available, ensure the backend can handle null values appropriately. Lastly, if the deletion involves files (e.g., picture paths), ensure that any file operations are handled properly, possibly by deleting associated files from the storage if necessary.

Emmmanuel FIADUFE · Answer

Hello Sarthak, Thank you team, It is working now, Sarthak this query work form me well and thank you, but my question is I have NextOfKingId as secondary key in the staff table and why is not working when I reference it in the function, or is it because in the database I made it Allow Nulls public ActionResult DeleteStaff(int StaffId) { using (var transaction = objBasicShoolDBEntities.Database.BeginTransaction()) { try { // Find and remove Staff var Staff = objBasicShoolDBEntities.tblStaffs .FirstOrDefault(a => a.StaffId == StaffId); if (Staff != null) { objBasicShoolDBEntities.tblStaffs.Remove(Staff); } else { Console.WriteLine("Staff with ID " + StaffId + " not found."); } objBasicShoolDBEntities.SaveChanges(); transaction.Commit(); return Json(true, JsonRequestBehavior.AllowGet); } catch (Exception ex) { transaction.Rollback(); Console.WriteLine("Exception occurred: " + ex.Message); Console.WriteLine("Stack Trace: " + ex.StackTrace); return Json(false, JsonRequestBehavior.AllowGet); } } }