使用C#操作SQL Server执行SQL Delete时遇问题求助

Hey there, let's work through this Delete issue together! Even if you’ve confirmed the ID is correct, there are several common pitfalls that could be triggering that exception. Here’s how to diagnose and fix it step by step:

First things first—don’t ignore the exception’s full context. The error message, inner exception, and SQL error code will tell you exactly what’s going wrong. Add a try-catch block to log these details:

try
{
    // Your existing Delete execution code here
}
catch (SqlException sqlEx)
{
    Console.WriteLine($"SQL Error Message: {sqlEx.Message}");
    Console.WriteLine($"SQL Error Code: {sqlEx.Number}");
    if (sqlEx.InnerException != null)
    {
        Console.WriteLine($"Inner Exception: {sqlEx.InnerException.Message}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"General Error: {ex.Message}");
}

This will reveal if it’s a permission issue, foreign key constraint conflict, syntax error, or something else.

Make sure you’re using parameterized queries (never string concatenation—this avoids SQL injection and syntax bugs). Here’s a correct example:

using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
    conn.Open();
    string deleteSql = "DELETE FROM YourTargetTable WHERE Id = @RecordId";
    using (SqlCommand deleteCmd = new SqlCommand(deleteSql, conn))
    {
        // Explicitly set parameter type to match your database column
        deleteCmd.Parameters.Add("@RecordId", SqlDbType.Int).Value = yourConfirmedId;
        int rowsDeleted = deleteCmd.ExecuteNonQuery();
        Console.WriteLine($"Rows deleted: {rowsDeleted}");
    }
}

Also verify:

• Table and column names are spelled correctly (SQL Server is case-insensitive by default, but some configurations aren’t)
• You’re not missing quotes for string-based IDs (though parameterization eliminates this risk)

Even with a valid ID, the record might be locked by a foreign key relationship. For example, if your Users table is linked to an Orders table via UserId, you can’t delete a user who still has active orders unless your foreign key is set to ON DELETE CASCADE.

Run this query to check for related constraints:

SELECT 
    fk.name AS ForeignKey,
    OBJECT_NAME(fk.parent_object_id) AS ParentTable,
    OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable
FROM 
    sys.foreign_keys fk
WHERE 
    OBJECT_NAME(fk.parent_object_id) = 'YourTargetTable';

Ensure the SQL Server login your app uses has DELETE permissions on the target table. You can check this with:

USE YourDatabase;
EXEC sp_helprotect @username = 'YourLoginName', @objname = 'YourTargetTable';

If permissions are missing, grant them with:

GRANT DELETE ON YourTargetTable TO YourLoginName;

It’s possible the record was deleted by another process/user between when you verified the ID and when your code runs. Add a quick check before deleting:

string checkSql = "SELECT COUNT(*) FROM YourTargetTable WHERE Id = @RecordId";
using (SqlCommand checkCmd = new SqlCommand(checkSql, conn))
{
    checkCmd.Parameters.Add("@RecordId", SqlDbType.Int).Value = yourConfirmedId;
    int recordCount = (int)checkCmd.ExecuteScalar();
    if (recordCount == 0)
    {
        Console.WriteLine("Warning: The record no longer exists in the database.");
        return;
    }
}

Make sure your C# variable type matches the database’s Id column type. For example:

• If the column is uniqueidentifier (GUID), don’t pass a string that isn’t a valid GUID
• If it’s bigint, don’t use an int variable that could overflow
  Explicitly defining the SqlDbType in your parameter (like in step 2) avoids these mismatches.

内容的提问来源于stack exchange，提问作者maximdj