Delete data from two tables

Well noted and thank you

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:

1. Check for Foreign Key Constraints: Ensure that there are no foreign key constraints that might prevent deletion.

2. Use Transactions: Continue using transactions to ensure atomicity, so if any part of the deletion process fails, it rolls back.

3. 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.

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); } } }

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

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.

1. Check for Null Values: Ensure you are properly handling the case where the record to be deleted might not exist.

2. Save Changes After Each Deletion: Consider saving changes after each deletion to pinpoint where the failure occurs.

3. Detailed Exception Handling: Instead of a generic Exception catch, use specific exception types to get more information on what might be going wrong.

4. 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);
    }
}

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');
        }
    });
}

1. Log Detailed Messages: Use logging to capture detailed information about the execution flow and any exceptions that occur.

2. Check Database Constraints: Ensure there are no foreign key constraints or other database rules that prevent deletion.

3. Verify Records Exist: Before attempting to delete, confirm that the records actually exist in the database.

4. Use Debugger: Run the application in debug mode and step through the deletion process to see exactly where it fails.

5. 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.