Need clarification about how to apply a custom update to data adapter source

Question

I have created a record-view form that contains a few bound elements via a BindingSource and a BindingNavigator. The viewing of the data fields is operating correctly. Note that the variables da and ds are global in this form.

private void frmItem_Load(object sender, EventArgs e) {    
  string scon = System.Configuration.ConfigurationManager.ConnectionStrings["myitems"].ToString();
  da = new SqlDataAdapter("Select * From myitems where id > 0 ", scon);
  ds = new DataSet();
  da.Fill(ds);
  bindingSource1.DataSource = ds.Tables[0];            
  bindingNavigator1.BindingSource = this.bindingSource1;
  this.txtId.DataBindings.Add(new Binding("Text", bindingSource1, "id", true));
  this.txtItem.DataBindings.Add(new Binding("Text", bindingSource1, "item", true));
  this.txtUpdatedwhen.DataBindings.Add(new Binding("Text", bindingSource1, "updatedwhen", true));
}

I am showing this record-view form from a data grid view of items by using a row header mouse dbl-click event. The requested row from the dgv is correctly being selected and its row data is correctly being shown in the record-view form.

private void dgvItems_RowHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e) {
  frmItem gfrmItem = new frmItem();
  string sID = this.dgvItems.CurrentRow.Cells[0].Value.ToString();
  gfrmItem.FilterByID(sID);
  gfrmItem.Show();
}

I've added a save button to the navigator so that I can make individual record save. What I'm attempting to do is programatically apply a date/time stamp update before the record is saved from the button click.

private void btnSave_Click(object sender, EventArgs e)
{
  this.txtUpdatedwhen.Text = DateTime.Now.ToString();
  da.Update(ds); 
}

Although the date/time value is changed per the code and shows in the form, the update is not applying the date/time change.

I thought that the textbox value was being bound to the underlying dataset and would accept changes as if I had entered it manually ... but this is not occurring. I had read some other posts that using the data adapter update is the right way to go about this as apposed to doing something like performing a direct sql update.

I'm stumped with how to resolve this. Any pointers would be greatly appreciated.

Answer 1

After letting this sit a while and coming back to it today, I found a resolution.

There was a common misunderstanding at work that I saw in other posts.
That was that the dataadapter does not automatically populate its commands, even if you pass an active connection into the creation step.

So my resolution was to create a global SqlCommandBuilder variable along with the other ones I was using

SqlDataAdapter da;
SqlConnection sc;
SqlCommandBuilder sb;
DataSet ds;

then create the builder object at form load and initialize the update command into a string variable ... which isn't used there after, but the dataadapter commands are now populated.

string scon = System.Configuration.ConfigurationManager.ConnectionStrings["networkadmin"].ToString();
sc = new SqlConnection(scon);
sc.Open();
string sSelect = "Select * From datatable where id > 0 Order By fld1;";
}
this.da = new SqlDataAdapter(sSelect, sc);

sb = new SqlCommandBuilder(da);

// This initiates the commands, though the target var is not used again.
string uCmd = sb.GetUpdateCommand().ToString();

this.ds = new DataSet();
this.da.Fill(this.ds);

Then the update step does work as expected:

this.txtUpdatedwhen.Text = DateTime.Now.ToString();
DataRowView current = (DataRowView)bindingSource1.Current;
current["updatedwhen"] = this.txtUpdatedwhen.Text;            
bindingSource1.EndEdit();
this.da.Update(ds);        

I hope this helps someone.