Row removed from a dataset table is not removed from a database tableduring update

[nvx]

Hi,
I have a problem with updating a database table bound to a DataGridView. After I fill the DataSet with DataAdapter.Fill(),
generate INSERT, DELETE and UPDATE commands using CommandBuilder, remove some rows from the DataSet.Tables["tablename"].Rows
collection, call BindingSource.EndEdit() and DataAdapter.Update(), these removed rows are still present in the database file. In
addition, DataSet.HasChanges() returns false! Sample code is included below.

In case I add some rows or change existing ones, everything is fine and changes are submitted to the DB file.

Sample code:

OleDbConnection conn = new OleDbConnection();
DataSet dataSet = new DataSet();
OleDbDataAdapter dAdapter = new OleDbDataAdapter();
string strTablenameSelect = "SELECT col1, col2, col3, col4, col5, col6, col7 FROM tablename";
DataTable tablename = new DataTable();
BindingSource bindingSource = new BindingSource();
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder();

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullpath;
dAdapter.SelectCommand = new OleDbCommand(strTablenameSelect, conn);
dataSet.Tables.Add(tablename);
conn.Open();
dAdapter.Fill(dataSet, "tablename");
conn.Close();

bindingSource.DataSource = dataSet.Tables["tablename"];
dataGridView.DataSource = bindingSource;

// DataPropertyName of columns in the dataGridView set here
dataGridView.Columns["col1DGVColumn"].DataPropertyName = "col1";
// etc.

DataColumn[] primaryKey = new DataColumn[1];
primaryKey[0] = dataSet.Tables["tablename"].Columns["col1"];
dataSet.Tables["tablename"].PrimaryKey = primaryKey;

commandBuilder.DataAdapter = dAdapter;
dAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
dAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();

// let's remove a row with a string "Example" in the primary key column...
dataSet.Tables["tablename"].Rows.Remove(dataSet.Tables["tablename"].Rows.Find("Example"));
// row is removed, disappears also from the dataGridView

conn.Open();
bindingSource.EndEdit();
// after executing the line below changed lines are changed, new lines are inserted, but removed lines are NOT removed
dAdapter.Update(dataSet, "tablename");
conn.Close();


Any help would be appreciated, I'm quite new to data binding...

With regards
nvx

 

[Guest]

nvx,

You need to use Delete to delete the row from the data table, not Remove.

Kerry Moorman

 

[nvx]

Hi Kerry,
thank you very much for your advice. It is working correctly now.

Have a nice day...

nvx


Kerry Moorman napsal(a):

nvx,

You need to use Delete to delete the row from the data table, not Remove.

Kerry Moorman

Hi,
I have a problem with updating a database table bound to a DataGridView. After I fill the DataSet with DataAdapter.Fill(),
generate INSERT, DELETE and UPDATE commands using CommandBuilder, remove some rows from the DataSet.Tables["tablename"].Rows
collection, call BindingSource.EndEdit() and DataAdapter.Update(), these removed rows are still present in the database file. In
addition, DataSet.HasChanges() returns false! Sample code is included below.

In case I add some rows or change existing ones, everything is fine and changes are submitted to the DB file.

Sample code:

OleDbConnection conn = new OleDbConnection();
DataSet dataSet = new DataSet();
OleDbDataAdapter dAdapter = new OleDbDataAdapter();
string strTablenameSelect = "SELECT col1, col2, col3, col4, col5, col6, col7 FROM tablename";
DataTable tablename = new DataTable();
BindingSource bindingSource = new BindingSource();
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder();

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullpath;
dAdapter.SelectCommand = new OleDbCommand(strTablenameSelect, conn);
dataSet.Tables.Add(tablename);
conn.Open();
dAdapter.Fill(dataSet, "tablename");
conn.Close();

bindingSource.DataSource = dataSet.Tables["tablename"];
dataGridView.DataSource = bindingSource;

// DataPropertyName of columns in the dataGridView set here
dataGridView.Columns["col1DGVColumn"].DataPropertyName = "col1";
// etc.

DataColumn[] primaryKey = new DataColumn[1];
primaryKey[0] = dataSet.Tables["tablename"].Columns["col1"];
dataSet.Tables["tablename"].PrimaryKey = primaryKey;

commandBuilder.DataAdapter = dAdapter;
dAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
dAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();

// let's remove a row with a string "Example" in the primary key column...
dataSet.Tables["tablename"].Rows.Remove(dataSet.Tables["tablename"].Rows.Find("Example"));
// row is removed, disappears also from the dataGridView

conn.Open();
bindingSource.EndEdit();
// after executing the line below changed lines are changed, new lines are inserted, but removed lines are NOT removed
dAdapter.Update(dataSet, "tablename");
conn.Close();


Any help would be appreciated, I'm quite new to data binding...

With regards
nvx