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!
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);
}