Issues with datagridview and datadpter.update

[jcp1234]

Hi All,

I have a datagridview that is populated from an SQL table the user can then modify it and I want to be able to post back nay updates whether they be updates or inserts back to the db but it doesn't seem to work properly, code below:

using (SqlConnection conn = new SqlConnection(connex))
{

using (SqlDataAdapter da = new SqlDataAdapter("SELECT date, ref, field1, field2, field3, notefield, idfield FROM table1 ", conn))
// WHERE date >= '" + dtStart.Value.ToString("yyyyMMdd") + "' AND date <= '" + dtEnd.Value.ToString("yyyyMMdd") + "'", conn))
{
using (SqlCommandBuilder builder = new SqlCommandBuilder(da))
{

DataTable dt = new DataTable();



//da.DeleteCommand = builder.GetDeleteCommand(true);
da.UpdateCommand = builder.GetUpdateCommand(true);

da.InsertCommand = builder.GetInsertCommand(true);
dt = dgPurchase.DataSource as DataTable;
/*if(String.IsNullOrEmpty(dgPurchase.CurrentRow.Cells["keyid"].Value?.ToString()))
dt.Columns.Remove("id");*/

da.Update(dt);

}
}
}

This adds multiple extra duplicate records when I update a single value in one row but without the updated value.

so if field 1 =1 prior to save and then i edit it to be 1.5 and click save then multiple records with the same ref,date and value are added but with new ids.

Is there a way to make this wokr or do I need to resort to using a for loop to loop through each row in the table?

Continue reading...