CODEWITHSUNDEEP
c#datagridviewdatatablesqldataadapter
Paul
Paul

Reputation: 26680

Strange behaviour of DataTable with DataGridView

Please explain me what is happening. I have created a WinForms .NET application which has DataGridView on a form and should update database when DataGridView inline editing is used.

Form has SqlDataAdapter _da with four SqlCommands bound to it. DataGridView is bound directly to DataTable _names.

Such a CellValueChanged handler:

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    _da.Update(_names);
}

does not update database state although _names DataTable is updated. All the rows of _names have RowState == DataRowState.Unchanged

Ok, I modified the handler:

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    DataRow row = _names.Rows[e.RowIndex];
    row.BeginEdit();
    row.EndEdit();
    _da.Update(_names);
}

this variant really writes modified cell to database, but when I attempt to insert new row into grid, I get an error about an absence of row with index e.RowIndex

So, I decided to improve the handler further:

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    if (_names.Rows.Count<e.RowIndex)
    {
        DataRow row = _names.Rows[e.RowIndex];
        row.BeginEdit();
        row.EndEdit();
    }
    else
    {
        DataRow row = _names.NewRow();
        row["NameText"] = dataGridView1["NameText", e.RowIndex].Value;
        _names.Rows.Add(row);
    }
    _da.Update(_names);
}

Now the really strange things happen when I insert new row to grid: the grid remains what it was until _names.Rows.Add(row); After this line THREE rows are inserted into table - two rows with the same value and one with Null value.

The slightly modified code:

DataRow row = _names.NewRow();
row["NameText"] = "--------------"
_names.Rows.Add(row);

inserts three rows with three different values: one as entered into the grid, the second with "--------------" value and third - with Null value.

I really got stuck in guessing what is happening.

Upvotes: 4

Views: 1514

Answers (3)

JimSnyder
JimSnyder

Reputation: 157

NECRO ANSWER: The easiest way is to use another dataset for just the changes:

private void UpdateDB()
{
    using (SqlConnection conn = new SqlConnection(SQLString.ConnStr))   // All new connection
    {
        try
        {
            dataAdapter.SelectCommand = new SqlCommand(SQLString.QryStr, conn);
            cmd.DataAdapter = dataAdapter;

            changeSet = dataSet.GetChanges();
            if (changeSet != null)
            {
                dataAdapter.Update(changeSet, "tstRegion");
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }
    }
}

Note that the original dataset's changes are all that is in the dataset of changes.

Upvotes: 0

David Hall
David Hall

Reputation: 33183

The simplest way to get the behaviour (I think) you want is to introduce a binding source - set the data table to be that binding source's data source, and then set the binding source to the grid.

Cell value changed then becomes:

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    bs.EndEdit();
    _da.Update(_names);
}

Upvotes: 1

Sam Anwar
Sam Anwar

Reputation: 679

I have a solution for you but let me explain what is happening. By the time the CellValueChanged event is triggered, the DataGridView has recognized that you made a change to the cell and it actually already pushed the value into the table, but the table is still in edit mode so the RowState of modified rows are still unchanged. Also, inserted rows aren't even visible in the table yet. To force the table to reflect these changes, call the EndCurrentEdit() method of the BindingContext of the form:

this.BindingContext[_names].EndCurrentEdit();

Upvotes: 2

Related Questions