Click here to Skip to main content
14,919,896 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am in big trouble please help me...
In my application I have a Datagridview with 862 rows and I am using MDB as my database. I need to update my database values according to my Datagridview for that I used below code. But its taking too much time to complete. Please help me to speed up the process..
string _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=" + DSource + ";" +
           "Jet OLEDB:Engine Type=5";
pg.Maximum = DrawingList.Rows.Count;
pg.Value = 0;
OleDbCommand cmd = new OleDbCommand();
OleDbConnection con = new OleDbConnection(_ConnectionString);
cmd.Connection = con;
foreach (DataGridViewRow row in DrawingList.Rows)
    cmd.CommandText = "update " + LbJobId.Text + " set  title = ? , created_on = ? , added_on = ? , edited_by = ? , edited_on = ? , checked_by = ? , checked_on = ? , back_dftd_by = ? , back_dftd_on = ? , cc_by = ? , cc_on = ? , cc_crrctn_by = ? , cc_crrctn_on = ? , qc_by = ? , qc_on = ? , qc_crrctn_by = ? , qc_crrctn_on = ? , remarks = ? , last_updated_by = ? , last_updated_on = ?   where drawing= ? ;";
    cmd.Parameters.AddWithValue("@title", row.Cells[3].Value.ToString());
    cmd.Parameters.AddWithValue("@created_on", row.Cells[4].Value.ToString());
    cmd.Parameters.AddWithValue("@added_on", row.Cells[5].Value.ToString());
    cmd.Parameters.AddWithValue("@edited_by", row.Cells[6].Value.ToString());
    cmd.Parameters.AddWithValue("@edited_on", row.Cells[7].Value.ToString());
    cmd.Parameters.AddWithValue("@checked_by", row.Cells[8].Value.ToString());
    cmd.Parameters.AddWithValue("@checked_on", row.Cells[9].Value.ToString());
    cmd.Parameters.AddWithValue("@back_dftd_by", row.Cells[10].Value.ToString());
    cmd.Parameters.AddWithValue("@back_dftd_on", row.Cells[11].Value.ToString());
    cmd.Parameters.AddWithValue("@cc_by", row.Cells[12].Value.ToString());
    cmd.Parameters.AddWithValue("@cc_on", row.Cells[13].Value.ToString());
    cmd.Parameters.AddWithValue("@cc_crrctn_by", row.Cells[14].Value.ToString());
    cmd.Parameters.AddWithValue("@cc_crrctn_on", row.Cells[15].Value.ToString());
    cmd.Parameters.AddWithValue("@qc_by", row.Cells[16].Value.ToString());
    cmd.Parameters.AddWithValue("@qc_on", row.Cells[17].Value.ToString());
    cmd.Parameters.AddWithValue("@qc_crrctn_by", row.Cells[18].Value.ToString());
    cmd.Parameters.AddWithValue("@qc_crrctn_on", row.Cells[19].Value.ToString());
    cmd.Parameters.AddWithValue("@remarks", row.Cells[20].Value.ToString());
    cmd.Parameters.AddWithValue("@last_updated_by", row.Cells[21].Value.ToString());
    cmd.Parameters.AddWithValue("@last_updated_on", row.Cells[22].Value.ToString());
    cmd.Parameters.AddWithValue("@drawing", row.Cells[2].Value.ToString());


Thanks in Advance.
Maciej Los 24-Oct-14 3:29am
WOW! Do you want to tell us that you're trying to update each record? What happens if your database will grow to 1M records?
DO NOT do that this way! Update single record after edition or just bulk copy datagridview data into temporary table and update destination table using UPDATE + SELECT commands ;)
Maciej Los 24-Oct-14 4:00am
Another way is to update only 'dirty' records.
Manu Prasad 24-Oct-14 5:27am
I just added two lines but the change is amazing...
cmd.CommandType = CommandType.Text;
Maciej Los 24-Oct-14 5:54am
Use 'Reply' widget to post a comment to my comment.

1 solution

As Maciej Los said this is not the right way to update records but I don't have any other choices. And I am sure that my database maximum size don't exceed 1500 records. So I used just two lines of codes that's solved my problem.!!!
  cmd.CommandType = CommandType.Text;
Maciej Los 24-Oct-14 5:53am
There is a set of choices. We need more details, if you want our help, for example: methods you used to update single record.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900