SqlDataAdapter.Update not writing to table until second update

Question

I have a program developed in C# 2010 Express with a form consisting of a DataGridView with individual cell editing fields below it. When a row is selected (clicked at its left end), the values in the row are copied into the editing fields. Changes are made, and a Memorize Transaction button is clicked. The new value appears in the DataGridView. If the form is then closed and reopened (disposed and reloaded), the original value is shown, not the new value. Clearly the database record was not updated.

If the process above is repeated, except that before closing the form the record is selected again and the Memorize Transaction button is clicked again, the value IS stored in the database. When the form is closed and then reopened, the new value appears.

Obviously the desired behavior is for the value to be stored in the database immediately on the first click of the Memorize Transaction button.

The following declaration appears at the top of the form class:

    DataTable _dt;
    SqlDataAdapter _adapter;

The method load_DataGridView() looks like the following. Note that the adapter's Update, Delete and Insert queries are built with calls to their respective GetXXXCommand() methods of the SqlCommandBuilder. Breakpoints during debugging indicate that these have been correctly populated.

    private void load_dataGridView()
    {
        DataTable dt = new DataTable();
        _dt = dt;
        SqlConnection dbconn = new SqlConnection(dbq.connectionString);
        dbconn.Open();
        string qstr = @"SELECT transaction_id
    , 0 as pay
    , m.has_splits
    , m.Num
    , m.Type
    , m.last_date
    , m.next_date
    , m.Payee
    , m.Memo
    , m.Tax_Category
    , m.Amount
    , m.Clr
    , m.Initials
    , m.sales_tax_paid
    , m.from_institution_id as FromInstitutionID
    , m.from_institution as FromInstitution
    , m.from_account_id as FromAccountID
    , m.from_account as FromAccount
    , m.from_envelope_id as FromEnvelopeID
    , m.from_envelope as FromEnvelope
    , m.to_institution_id as ToInstitutionID
    , m.to_institution as ToInstitution
    , m.to_account_id as ToAccountID
    , m.to_account as ToAccount
    , m.to_envelope_id as ToEnvelopeID
    , m.to_envelope as ToEnvelope
    , m.frequency
    FROM memorized m
    ";
        SqlDataAdapter adapter = new SqlDataAdapter(qstr,dbconn);
        SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
        adapter.UpdateCommand = new SqlCommandBuilder(adapter).GetUpdateCommand();
        adapter.DeleteCommand = new SqlCommandBuilder(adapter).GetDeleteCommand();
        adapter.InsertCommand = new SqlCommandBuilder(adapter).GetInsertCommand();
        _adapter = adapter;
        _adapter.Fill(_dt);
        build_dgv_columns(Properties.Settings.Default.currentInstitutionId,
        Properties.Settings.Default.currentAccountId);
        memorizedDataGridView.DataSource = _dt;
    }

The code that is invoked when the Memorize Transaction button is clicked is as follows:

    private void bnMemorizeTx_Click(object sender, EventArgs e)
    {
        if (_newTx)
        {
            _currentTxRow = _dt.Rows.Count;
            _dt.Rows.Add();
            _newTx = false;
        }
        else
        {
            _currentTxRow = memorizedDataGridView.SelectedRows[0].Index;
        }
        populateRowFromFields(memorizedDataGridView.Rows[_currentTxRow]);
        memorizedDataGridView.Refresh();
        memorizedDataGridView.ClearSelection();
        //grid shows new value with no rows selected at this point, row is "dirty"
        try
        {
            _adapter.Update(_dt);
        }
        catch (SqlException e1)
        {
            MessageBox.Show(e1.Message + e1.InnerException, "Error Thrown",MessageBoxButtons.OK);
        }
        reset_transaction_fields();
    }

No exception is ever thrown. I suspect that the action of pulling data out of the DataGridView into the editing fields and then writing it back again somehow monkeys with the _adapter.Update(_dt) command's ability to detect that a row has been changed, so when it's called, it doesn't update anything. Then on the second try, when the row has been selected afresh (showing the new value) but no editing changes are made, it does work. Somehow, even though the new value appears in the grid, it's like the change hasn't been committed and the row has to be reselected for that to happen.

The populateRowFromFields method follows below, writing the values in the editing fields back into the cell values in the row by using column name references for the cell indexes:

            private void populateRowFromFields(DataGridViewRow dgvr)
    {
        dgvr.Cells["Num"].Value = tbRefNum.Text;
        dgvr.Cells["Type"].Value = cbType.GetItemText(cbType.SelectedItem);
        dgvr.Cells["last_date"].Value = dtpLastPaid.Value.ToShortDateString();
        dgvr.Cells["next_date"].Value = dtpNextPay.Value.ToShortDateString();
        dgvr.Cells["Payee"].Value = tbPayee.Text;
        dgvr.Cells["Amount"].Value = tbAmount.Text;
        dgvr.Cells["FromInstitutionID"].Value = cbFromInst.GetItemText(cbFromInst.SelectedValue); // fromInst
        dgvr.Cells["FromInstitution"].Value = cbFromInst.GetItemText(cbFromInst.SelectedItem); // fromAcct
        dgvr.Cells["FromAccountID"].Value = cbFromAcct.GetItemText(cbFromAcct.SelectedValue); // fromEnv
        dgvr.Cells["FromAccount"].Value = cbFromAcct.GetItemText(cbFromAcct.SelectedItem); // fromInst
        dgvr.Cells["FromEnvelopeID"].Value = cbFromEnv.GetItemText(cbFromEnv.SelectedValue); // fromAcct
        dgvr.Cells["FromEnvelope"].Value = cbFromEnv.GetItemText(cbFromEnv.SelectedItem); // fromEnv
        if ((cbType.GetItemText(cbType.SelectedItem) == "+iTx") || (cbType.GetItemText(cbType.SelectedItem) == "-iTx") || (cbType.GetItemText(cbType.SelectedItem) == "+eTx") || (cbType.GetItemText(cbType.SelectedItem) == "-eTx")) 
        {
            dgvr.Cells["ToInstitutionID"].Value = cbToInst.GetItemText(cbToInst.SelectedValue); // toInst
            dgvr.Cells["ToInstitution"].Value = cbToInst.GetItemText(cbToInst.SelectedItem); // toInst
            dgvr.Cells["ToAccountID"].Value = cbToAcct.GetItemText(cbToAcct.SelectedValue); // toAcct
            dgvr.Cells["ToAccount"].Value = cbToAcct.GetItemText(cbToAcct.SelectedItem); // toAcct
            dgvr.Cells["ToEnvelopeID"].Value = cbToEnv.GetItemText(cbToEnv.SelectedValue); // toEnv
            dgvr.Cells["ToEnvelope"].Value = cbToEnv.GetItemText(cbToEnv.SelectedItem); // toEnv
            hideTxToFields();
        }
        dgvr.Cells["Memo"].Value = tbMemo.Text;
        dgvr.Cells["Tax_Category"].Value = cbTaxCategory.GetItemText(cbTaxCategory.SelectedItem); // taxCategory
        dgvr.Cells["sales_tax_paid"].Value = cbSalesTaxPaid.GetItemText(cbSalesTaxPaid.SelectedItem); // salesTaxPaid
        dgvr.Cells["Clr"].Value = "U";
        dgvr.Cells["Initials"].Value = cbBy.GetItemText(cbBy.SelectedItem); // by
        dgvr.Cells["frequency"].Value = cbFrequency.GetItemText(cbFrequency.SelectedItem); // frequency
    }

Help! This is driving me nuts!

I would suggest that you're going about this the wrong way. You should populate a `DataTable`, bind that to a `BindingSource` and then bind that to your `DataGridView` AND your editing fields. When the user selects a row in the grid, its fields will be displayed automatically in the editing fields; no code required. Any changes made in the editing fields will be automatically pushed back to the grid when the update is committed. That will happen automatically when you navigate to to another record but you can also call `EndEdit` on the `BindingSource` to force it before saving. — jmcilhinney, Aug 02 '14 at 01:21
Makes sense -- I'm all for "no code required". But I'm not sure what the code looks like to bind the editing fields (all 24 of them) to the BindingSource. Could you give a short example? TIA — Jim I., Aug 02 '14 at 01:44
Also, am I not populating a DataTable when I say _adapter.Fill(_dt); and memorizedDataGridView.DataSource = _dt;? (you have to scroll down in the code window to see these lines) — Jim I., Aug 02 '14 at 01:52
Yes, that `Fill` call is how you populate a `DataTable` but I wanted to include all the required steps in my description. I'll post a code example as an answer. — jmcilhinney, Aug 02 '14 at 02:58
Thanks, j, give me a day to try this out and I'll post results and mark as solution then. Much clearer. — Jim I., Aug 02 '14 at 03:31

jmcilhinney · Accepted Answer · 2014-08-02T05:15:24.650

The simplest option is to use data-binding for the grid and the editing controls, via a BindingSource, e.g.

private SqlConnection connection;
private SqlDataAdapter adapter;
private SqlCommandBuilder builder;
private DataTable table;

private void Form1_Load(object sender, EventArgs e)
{
    connection = new SqlConnection("connection string here");
    adapter = new SqlDataAdapter("SELECT * FROM Person", connection);
    builder = new SqlCommandBuilder(adapter);
    table = new DataTable();

    adapter.Fill(table);
    bindingSource1.DataSource = table;
    dataGridView1.DataSource = bindingSource1;
    givenNameTextBox.DataBindings.Add("Text", bindingSource1, "GivenName");
    familyNameTextBox.DataBindings.Add("Text", bindingSource1, "FamilyName");
}

private void addButton_Click(object sender, EventArgs e)
{
    bindingSource1.AddNew();
}

private void deleteButton_Click(object sender, EventArgs e)
{
    bindingSource1.RemoveCurrent();
}

private void saveButton_Click(object sender, EventArgs e)
{
    bindingSource1.EndEdit();
    adapter.Update(table);
}

Thanks again, j, once I had made the necessary conversions to C# (not a big deal, thankfully VB and C# are pretty close in this case, but could be confusing for newbies), it worked great. I'm having this bronzed for future reference! :-) — Jim I., Aug 02 '14 at 04:43
Terribly sorry about posting VB code. I've been mostly answering VB questions and forgot that I wasn't this time. Even though you've already done it for yourself, I'll replace the code in case anyone else comes to this question with a similar problem. — jmcilhinney, Aug 02 '14 at 05:06

SqlDataAdapter.Update not writing to table until second update

1 Answers1