CODEWITHSUNDEEP
c#sql-serverdatagridview
Carlo Cruz
Carlo Cruz

Reputation: 41

Updating multiple rows in datagridview all the same

i can only update 1 row but when two or multiple row when i update it all the values are the same i don't know what is the error i put the sqlcommand inside the loop

       string date = dt_date.Value.ToString("yyyy-MM-dd");
        SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["POSdb"].ConnectionString);

        string strQry = "UPDATE CounterChecking " +
        "SET part_no = @c0,po_no = @po_no,invoice = @invoice,part_name = @c1," +
        "date = @date,color = @c2,qty_ordered = @c3,qty_for_delivery = @c4,qty_recieved = @c5," +
        "variance = @c6,unit_price = @c7, total = @c8, remarks = @c9 " +
        "WHERE po_no= '" + cb_po_search.Text + "'";

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {

            if (!row.IsNewRow)
            {
                sc.Open();
                    SqlCommand scmd = new SqlCommand(strQry, sc);
                    scmd.Parameters.AddWithValue("@c0", row.Cells[0].Value);
                    scmd.Parameters.Add("@po_no", SqlDbType.VarChar).Value = tb_ponumber2.Text;
                    scmd.Parameters.Add("@invoice", SqlDbType.VarChar).Value = tb_invoice2.Text;
                    scmd.Parameters.AddWithValue("@c1", row.Cells[1].Value);
                    scmd.Parameters.Add("@date", SqlDbType.Date).Value = date;
                    scmd.Parameters.AddWithValue("@c2", row.Cells[2].Value);
                    scmd.Parameters.AddWithValue("@c3", row.Cells[3].Value);
                    scmd.Parameters.AddWithValue("@c4", row.Cells[4].Value);
                    scmd.Parameters.AddWithValue("@c5", row.Cells[5].Value);
                    scmd.Parameters.AddWithValue("@c6", row.Cells[6].Value);
                    scmd.Parameters.AddWithValue("@c7", row.Cells[7].Value);
                    scmd.Parameters.AddWithValue("@c8", row.Cells[8].Value);
                    scmd.Parameters.AddWithValue("@c9", row.Cells[9].Value);
                    scmd.ExecuteNonQuery();
                    scmd.Dispose();
                    sc.Close();
                    MessageBox.Show("Successfully Updated!");}
            sc.Close();
        }

no error but it gives me all the same data every row

Upvotes: 0

Views: 2132

Answers (2)

Jacob Lambert
Jacob Lambert

Reputation: 7679

You need to recreate the command for every iteration. If you move SqlCommand scmd = new SqlCommand(strQry, sc); inside your do loop, you should be fine. But a better option would be to wrap it in a using clause like so and use a foreach to go through the dataGridView1.Rows collection:

        foreach(var row in dataGridView1.Rows)
        {
            using (SqlCommand scmd = new SqlCommand(strQry, sc))
            {
                scmd.Parameters.AddWithValue("@c0", row.Cells[0].Value);
                scmd.Parameters.Add("@po_no", SqlDbType.VarChar).Value = tb_ponumber2.Text;
                scmd.Parameters.Add("@invoice", SqlDbType.VarChar).Value = tb_invoice2.Text;
                scmd.Parameters.AddWithValue("@c1", row.Cells[1].Value);
                scmd.Parameters.Add("@date", SqlDbType.Date).Value = date;
                scmd.Parameters.AddWithValue("@c2", row.Cells[2].Value);
                scmd.Parameters.AddWithValue("@c3", row.Cells[3].Value);
                scmd.Parameters.AddWithValue("@c4", row.Cells[4].Value);
                scmd.Parameters.AddWithValue("@c5", row.Cells[5].Value);
                scmd.Parameters.AddWithValue("@c6", row.Cells[6].Value);
                scmd.Parameters.AddWithValue("@c7", row.Cells[7].Value);
                scmd.Parameters.AddWithValue("@c8", row.Cells[8].Value);
                scmd.Parameters.AddWithValue("@c9", row.Cells[9].Value);
                scmd.ExecuteNonQuery();
                MessageBox.Show("Successfully Updated!");
            }
        }

The benefit of the using clause is that it will automatically call the Dispose() method on the object prior to exiting the clause.

Upvotes: 0

MANOJ GOPI
MANOJ GOPI

Reputation: 1279

Initialize the SqlCommand inside the loop and dispose the object once the command is executed. Try to use try catch finally block inside finally dispose the command object.

SqlCommand scmd;
do
    {
      scmd = new SqlCommand(strQry, sc);
      scmd.Parameters.AddWithValue("@c0", dataGridView1.Rows[counter].Cells[0].Value);
      scmd.Parameters.Add("@po_no", SqlDbType.VarChar).Value = tb_ponumber2.Text;
      scmd.Parameters.Add("@invoice", SqlDbType.VarChar).Value = tb_invoice2.Text;
      scmd.Parameters.AddWithValue("@c1", dataGridView1.Rows[counter].Cells[1].Value);
      scmd.Parameters.Add("@date", SqlDbType.Date).Value = date;
      scmd.Parameters.AddWithValue("@c2", dataGridView1.Rows[counter].Cells[2].Value);
      scmd.Parameters.AddWithValue("@c3", dataGridView1.Rows[counter].Cells[3].Value);
      scmd.Parameters.AddWithValue("@c4", dataGridView1.Rows[counter].Cells[4].Value);
      scmd.Parameters.AddWithValue("@c5", dataGridView1.Rows[counter].Cells[5].Value);
      scmd.Parameters.AddWithValue("@c6", dataGridView1.Rows[counter].Cells[6].Value);
      scmd.Parameters.AddWithValue("@c7", dataGridView1.Rows[counter].Cells[7].Value);
      scmd.Parameters.AddWithValue("@c8", dataGridView1.Rows[counter].Cells[8].Value);
      scmd.Parameters.AddWithValue("@c9", dataGridView1.Rows[counter].Cells[9].Value);
      counter++;
      scmd.ExecuteNonQuery();
      scmd.Dispose();
      MessageBox.Show("Successfully Updated!");
   } while (counter < dataGridView1.Rows.Count);

Upvotes: 1

Related Questions