C# Update Database for every DataGridView Row

Question

I want to update a Table in Database for every row in the DataGridView by using a Update button. Here's The following Update Button Code :

private void btUpdate_Click(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection(con);
    cn.Open();
    SqlCommand cmdUpdate = new SqlCommand("UPDATE OINV SET U_IDU_HFP = @HFP, U_IDU_FAKTURPAJAK = @FP, U_IDU_FPDATE = @FPDATE WHERE DocEntry = @DocEntry;", cn);
    foreach (DataGridViewRow item in dgvInputPKP.Rows)
    {
        cmdUpdate.Parameters.AddWithValue("@HFP", item.Cells[10].Value);
        cmdUpdate.Parameters.AddWithValue("@FP", item.Cells[11].Value);
        cmdUpdate.Parameters.AddWithValue("@FPDATE", item.Cells[9].Value);
        cmdUpdate.Parameters.AddWithValue("@DocEntry", item.Cells[1].Value);
        cmdUpdate.ExecuteNonQuery();
    }
    MessageBox.Show("Record Updated Successfully");
    cn.Close();
}

When I press the Update Button only the 1st row from DataGridViews was Updated Succesfully in the Database, but not with the other rows. It also return some error with following detail:

The variable name '@HFP' has already been declared. Variable names must be unique within a query batch or stored procedure.

I was try to searching and googling but never find the right one.

Please help me to fix this issue. Thanks.

How do you bind the DataGridView to your data? Are you using an SqlDataAdapter? If yes there is a simple way to update records modified, added or deleted in the datatable binded to the DataGridView. — Steve
– Steve, Commented Feb 1, 2016 at 8:23

Steve · Accepted Answer · 2016-02-01 08:33:11Z

You need to clear the Command.Parameters collection at each loop

foreach (DataGridViewRow item in dgvInputPKP.Rows)
{
    cmdUpdate.Parameters.Clear();
    ....
    cmdUpdate.ExecuteNonQuery();
}

However there are a couple of things that could be done here to optimize and make your code more safe and remove weakness.

using(SqlConnection cn = new SqlConnection(con))
using(SqlCommand cmdUpdate = new SqlCommand("UPDATE OINV SET U_IDU_HFP = @HFP, U_IDU_FAKTURPAJAK = @FP, U_IDU_FPDATE = @FPDATE WHERE DocEntry = @DocEntry;", cn))
{
    cn.Open();
    using(SqlTransaction tr = cn.BeginTrasaction())
    {
        cmdUpdate.Transaction = tr;
        cmdUpdate.Parameters.Add("@HFP", SqlDbType.NVarChar);
        cmdUpdate.Parameters.Add("@FP", SqlDbType.NVarChar);
        cmdUpdate.Parameters.Add("@FPDATE", SqlDbType.NVarChar);
        cmdUpdate.Parameters.Add("@DocEntry", SqlDbType.NVarChar);
        foreach (DataGridViewRow item in dgvInputPKP.Rows)
        {
            cmdUpdate.Parameters["@HFP"].Value = item.Cells[10].Value;
            cmdUpdate.Parameters["@FP"].Value = item.Cells[11].Value;
            cmdUpdate.Parameters["@FPDATE"].Value = item.Cells[9].Value;
            cmdUpdate.Parameters["@DocEntry"].Value = item.Cells[1].Value;
            cmdUpdate.ExecuteNonQuery();
       }
       tr.Commit();
   }
}
MessageBox.Show("Record Updated Successfully");

The parameter collection could be declared before entering the loop while inside the loop you update only the value. Notice also that the command and the connection are disposable object and should be declared and initialized inside a using block. Also to make your code more robust, I have added a transaction that ensures that all of your records are written to the database or none of them will be written in case of failure. The SqlTransaction.Commit at the end of the loop ensures this.

Finally, I don't know the datatype of the fields that receive the parameters value. You need to change the SqlDbType in the creation of the parameters according to the datatype of your columns in the datatable.

Soner Gönül · Accepted Answer · 2016-02-01 08:09:37Z

1

Since you use AddWithValue, this method tries to add over and over your parameters and their values in every iteration.

That's why your first iteration works, but in your second iteration your command already have those parameters definition and you still try to add them one more time. And you get this error message.

Just use SqlParameterCollection.Clear method top of your foreach statemnet like;

foreach (DataGridViewRow item in dgvInputPKP.Rows)
{
    cmdUpdate.Parameters.Clear();
    cmdUpdate.Parameters.AddWithValue("@HFP", item.Cells[10].Value);
    cmdUpdate.Parameters.AddWithValue("@FP", item.Cells[11].Value);
    cmdUpdate.Parameters.AddWithValue("@FPDATE", item.Cells[9].Value);
    cmdUpdate.Parameters.AddWithValue("@DocEntry", item.Cells[1].Value);
    cmdUpdate.ExecuteNonQuery();
}

By the way, don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overload to specify your parameter type and it's size.

Also use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods manually.

answered Feb 1, 2016 at 8:09

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

1 Comment

Mac Over a year ago

Thank You very much. It was running awesome as expected.

Nemanja Todorovic · Accepted Answer · 2016-02-01 08:09:19Z

0

Put entire SQL Command in foreach.

answered Feb 1, 2016 at 8:09

Nemanja Todorovic

2,8802 gold badges21 silver badges38 bronze badges

1 Comment

Takarii Over a year ago

that is unnecessary. The command SQL doesn't need to be issued every time. If the SQL query is the same, it just needs to be told to execute with the new parameters

Collectives™ on Stack Overflow

C# Update Database for every DataGridView Row

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related