C# SQL DataGridView change field before updating database

Question

I have a WindowsForms app with a DataGridView bound to a SQL database. All columns except one ("ts_last_edit") are editable. I want to update the "ts_last_edit" column only programmatically with the current DateTime every time someone changes some of the other columns AND saves the changes. I tried this:

...
    this.MyTableAdapter.Adapter.RowUpdating += Adapter_RowUpdating;
...
private void Adapter_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
  e.Row["ts_last_edit"]=DateTime.Now;    
}

But it did not work because e.Row is a read-only property apparently.

I tried changing the UPDATE SQL command and it works:

private void Adapter_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
// pseudo code
string newSqlCommand= "STUFF CURRENT DATE AND TIME IN COLUMN ts_last_edit";
e.Command.CommandText = newSqlCommand;
}

This works OK, but I was wondering if there is a better way to do it. Thank you

But it did not work because e.Row is a read-only property apparently. What exact exception was thrown? — mjwills
– mjwills, Commented Jul 6, 2017 at 11:09
@Krishna I want to store the DateTime when row is saved to database, not when a row loses focus — user1523271
– user1523271, Commented Jul 6, 2017 at 11:36
At what point you save it to database ? not on row leave ? do you have a button ? if so do it there — Krishna
– Krishna, Commented Jul 6, 2017 at 11:37

Krishna · Accepted Answer · 2017-07-06 13:10:59Z

2

you need to merge changes back to dataset after updating the time

private void brandBookBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
  this.Validate();
  var changes = dataSet1.GetChanges();
  if(changes!=null && changes.Tables["BrandBook"].Rows.Count>0)
  {
    foreach(row in changes.Tables["BrandBook"].Rows)
    {
      row["ts_last_edit"] = DateTime.Now;
    }
  }
  dataSet1.Merge(changes, false,System.Data.MissingSchemaAction.Add);
  this.brandBookBindingSource.EndEdit();
  this.tableAdapterManager.UpdateAll(this.dataSet1);
}

answered Jul 6, 2017 at 13:10

Krishna

1,9831 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# SQL DataGridView change field before updating database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related