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.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments