How to update multiple rows in one column-CodePudding

I have a column named status, and every row has a status of 1, i want to update it to 2. I can't seem to do multiple updates

Here's what I've tried

if (MessageBox.Show("Do you want to commit this?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
    int i = 0;

    foreach (DataGridViewRow row in dgvDisplay.Rows)
    {
        cn.Open();

        SqlCommand cmd = new SqlCommand(@" UPDATE CCSDD_StoreDirectDelivery1 SET Status = 2 WHERE DR#='"   dgvDisplay.SelectedRows[i].Cells[1].Value.ToString()   "'", cn);

        cmd.ExecuteNonQuery();
        cn.Close();

        i  ;
    }        

    MessageBox.Show("Your status has been updated!");
    //cn.Close();
}

When I run it, it gave an error on the line

SqlCommand cmd = new SqlCommand(@" update CCSDD_StoreDirectDelivery1 set Status = 2 WHERE DR#='"   dgvDisplay.SelectedRows[i].Cells[1].Value.ToString()   "'", cn);

with a message of:

Index was out of range. Must be non-negative and less than the size of the collection

CodePudding user response：

Your index is out of range, because you are doing it foreach row, while you are only checking the selected rows. Do foreach (DataGridViewRow row in dgvDisplay.SelectedRows instead. Also use parameter to prevent SQL injections, so:

    foreach (DataGridViewRow row in dgvDisplay.SelectedRows)
    {
         cn.Open();

         SqlCommand cmd = new SqlCommand(@" UPDATE CCSDD_StoreDirectDelivery1 SET Status = 2 WHERE DR#=@cellValue", cn);

         cmd.Parameters.AddWithValue("@cellValue", row.Cells[1].Value.ToString());
         cmd.ExecuteNonQuery();
         cn.Close();

     }

CodePudding user response：

I would recommend just executing one of the Sql commands :

Update All data in the Status Column with no conditions :

string cmd = "Update CCSDD_StoreDirectDelivery1 set Status = 2";

Update data with a condition :
string cmd = "Update CCSDD_StoreDirectDelivery1 set Status = 2 where Status = 1";