How can I update multiple rows in a table with SQL query?

Question

I am new to SQL and C#.

As shown in the screenshot, I want to update the 3 rows (order #3) in Order Details table by inserting only Qty, Description and Price values from DataGridView2. I am using the combination of Order_Number and DateTime to make the order details unique and easy to find in the table.

I used the following code but it updates the 3 rows of order #3 based on row 0 in DataGridView2:

private void Update_OrderDetails_Click(object sender, EventArgs e)  
    {
        SqlConnection cn = new SqlConnection("Data Source=PCNmm-TOSH;Initial Catalog=mydb;Integrated Security=True");
        cn.Open();

             SqlCommand cm = new SqlCommand("UPDATE Customer_Order_Details SET Qty = @Qty, Description = @Description, Price =  @Price WHERE Order_Number = @OrderNumber and DateTime = @DateTime ");


            cm.Parameters.Add("@OrderNumber", SqlDbType.Int);
            cm.Parameters["@OrderNumber"].Value = 3;

            cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
            cm.Parameters["@DateTime"].Value = "2015 - 12 - 17 15:04:57.043";



            cm.Parameters.Add("@Qty", SqlDbType.Int);
            cm.Parameters["@Qty"].Value = dataGridView2.Rows[0].Cells[2].Value;

            cm.Parameters.Add("@Description", SqlDbType.Text);
            cm.Parameters["@Description"].Value = dataGridView2.Rows[0].Cells[3].Value;

            cm.Parameters.Add("@Price", SqlDbType.Money);
            cm.Parameters["@Price"].Value = dataGridView2.Rows[0].Cells[4].Value;


            cm.Connection = cn;
            cm.ExecuteNonQuery();
        }
    }

But if I use for statement for (i = 0; i <= dataGridView2.RowCount; i++) and use dataGridView2.Rows[i].Cells[Cell_Number].Value in the parameters it gives me error "The parameterized query '(@OrderNumber int,@DateTime datetime,@Qty int,@Description text,' expects the parameter '@Qty', which was not supplied"

what I want is:

Row 1 in Order details table = DataGridView2 Row 0

Row 2 in Order details table = DataGridView2 Row 1

Row 3 in Order details table = DataGridView2 Row 2

But the problem is: I do not know how to index those 3 rows in the OrderDetail table in Database. Any idea how can I update the 3 rows (order 3) in database Order Details table with the 3 rows in DataGridView2? Thank you

Answer 1

private void Update_OrderDetails_Click(object sender, EventArgs e)
{
    string sql = @"insert Customer_Order_Details 
        (Order_Number, DateTime, Qty, Description, Price)
        values
        (@OrderNumber, @DateTime@Qty, @Qty, @Description, @Price)";

    using (SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=True"))
    using (SqlCommand cm = new SqlCommand(sql, cn))
    using (SqlCommand delete = new SqlCommand("delete from Customer_Order_Details where Order_Number = @OrderNumber"))
    {

        cm.Parameters.AddWithValue("@OrderNumber", 3);
        cm.Parameters.AddWithValue("@DateTime", new DateTime(2015, 12, 17, 15, 4, 57, 43));
        cm.Parameters.AddWithValue("@Qty", 0);
        cm.Parameters.AddWithValue("@Description", "");
        cm.Parameters.AddWithValue("@Price", 0M);

        delete.Parameters.AddWithValue("@OrderNumber", 3);

        cn.Open();
        delete.ExecuteNonQuery();

        foreach (DataGridViewRow row in dataGridView2.Rows)
        {
            cm.Parameters["@Qty"].Value = row.Cells[2].Value;
            cm.Parameters["@Description"].Value = row.Cells[3].Value;
            cm.Parameters["@Price"].Value = row.Cells[4].Value;
            cm.ExecuteNonQuery();
        }
        cn.Close();
    }
}

PS: The code is just a sample. It is a terrible idea to use order_number, datetime as the unique value (it is not unique, that is another matter. even if it were, it is a terrible idea). Code use delete + insert because you don't really have primary keys to use update with (again, a terrible table design).

Answer 2

DateTime and Order_Number fields are not enough to form an unique key, so when you call UPDATE with these parameter in the WHERE clause end up to update every record with the Ordernumber and the DateTime with the same parameter values.
You need a field (or fields) that acts as Primary Key or Unique Key to use in your where statement. (Some identity column used to reference the records and never showed to your customer)

If this is not an option then there is the possibility to delete the previous data and add them again with an INSERT query.
This is not a practice to recommend but if you don't have a primary key on this table perhaps is the only solution possible.

In any case this should be done in a transaction block to avoid data loss in case of problems

private void Update_OrderDetails_Click(object sender, EventArgs e)  
{
    string cmdText = @"INSERT INT Customer_Order_Details 
         (Order_Number, DataTime, Qty, Description, Price) 
         VALUES(@Order_Number, @DateTime, @Qty, @Description, @Price)";

    // Put every disposable objects inside a using block
    using(SqlConnection cn = new SqlConnection(....))
    {
        cn.Open();
        using(SqlTransaction tr = cn.BeginTransaction()) // Start the transaction
        using(SqlCommand cm = new SqlCommand("", cn));
        {
            // Delete the data that you are ready to reinsert
            cm.CommandText = @"DELETE FROM Customer_Order_Details 
                               WHERE Order_Number = @OrderNumber 
                               AND DateTime = @DateTime";
            cm.Parameters.Add("@OrderNumber", SqlDbType.Int).Value = 3;
            cm.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = "2015 - 12 - 17 15:04:57.043";
            cm.ExecuteNonQuery();

            // Change the commandtext, leave the parameters already there and
            // add the parameters required for insert, DO NOT PUT VALUES NOW
            cm.CommandText = cmdCommandText;
            cm.Parameters.Add("@Qty", SqlDbType.Int).Value = 0;
            cm.Parameters.Add("@Description", SqlDbType.Text).Value = "";
            cm.Parameters.Add("@Price", SqlDbType.Money).Value = 0;

            // Loop on your rows and reinsert the records
            for(int rowIndex = 0; x < 3; rowIndex++)
            {
              cm.Parameters["@Qty"].Value = dataGridView2.Rows[rowIndex].Cells[2].Value;
              cm.Parameters["@Description"].Value = dataGridView2.Rows[rowIndex].Cells[3].Value;

              cm.Parameters["@Price"].Value = dataGridView2.Rows[rowIndex].Cells[4].Value;
              cm.ExecuteNonQuery();
           }
           tr.Commit();
       }
   }
}