修改DataTable行后更新SQL表报错，需修复UpdateCommand缺失问题

First, let's break down why you're hitting that error Update requires a valid UpdateCommand when passed DataRow collection with modified rows:

Your SqlDataAdapter only has a SelectCommand set up by default. When you modify rows (update values or delete rows) in the DataTable and call Update(), the adapter has no clue how to translate those changes back to the database—it needs explicit UpdateCommand and DeleteCommand definitions to do that.

Step 1: Auto-Generate Required Commands with SqlCommandBuilder #

The simplest fix for the UpdateCommand issue is using SqlCommandBuilder. It automatically creates Update, Delete, and Insert commands based on your existing SelectCommand—just make sure your SELECT query returns a primary key or unique identifier column for the table (this is how it knows which rows to target).

Step 2: Fix Loop Logic Pitfalls #

Your original loop has a few critical issues:

• Risk of index out-of-bounds when i is the last row (accessing i+1 will throw an error)
• Calling Update() and AcceptChanges() inside the loop is inefficient and can cause partial updates if something goes wrong
• Forward traversal while deleting rows will skip subsequent rows (since deleting a row shifts the index of all rows below it up by one)

Fixed Code Implementation #

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString))
{
    // Define your select query and initialize the adapter
    string selectQuery = "SELECT * from tbl_Copy_Journal";
    SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, con);

    // Use SqlCommandBuilder to auto-generate update/delete/insert commands
    SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);

    DataSet Journals = new DataSet();
    adapter.Fill(Journals, "tbl_Copy_Journal");
    DataTable journalTable = Journals.Tables["tbl_Copy_Journal"];

    // Traverse rows from bottom to top to avoid index issues when deleting
    for (int i = journalTable.Rows.Count - 2; i >= 0; i--)
    {
        DataRow currentRow = journalTable.Rows[i];
        string currentAction = currentRow["Action"].ToString().Trim();

        if (currentAction == "reading dispensed")
        {
            DataRow nextRow = journalTable.Rows[i + 1];
            string nextAction = nextRow["Action"].ToString().Trim();

            if (nextAction.StartsWith("Referred"))
            {
                // Merge the two action strings into the current row
                currentRow["Action"] = $"{currentAction}, {nextAction}";
                // Mark the next row for deletion
                nextRow.Delete();
            }
        }
    }

    // Apply all accumulated changes to the database in one batch
    adapter.Update(Journals, "tbl_Copy_Journal");
    // Finalize changes in the DataSet to reflect the database state
    Journals.AcceptChanges();
}

Key Improvements Explained #

• using Statement: Ensures the SqlConnection is properly disposed after use, preventing resource leaks.
• SqlCommandBuilder: Eliminates the original error by auto-creating the commands needed to sync DataTable changes to the database.
• Backward Traversal: Starting from the second-last row and moving up avoids skipping rows when we delete a row (deleting a row doesn't affect the index of rows above it).
• Batch Update: Calling Update() only once after all modifications are done is far more efficient than updating inside the loop.
• Trimmed Strings: Added .Trim() to handle accidental whitespace in the Action column values, which could break your condition checks.

Important Notes #

• Make sure tbl_Copy_Journal has a primary key column—this is mandatory for SqlCommandBuilder to generate valid update/delete commands. If your table doesn't have a primary key, you'll need to manually define the UpdateCommand and DeleteCommand for the adapter.
• Always test this logic on a copy of your data first to avoid accidental data loss.

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