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

  • Thread starter Thread starter nvx
  • Start date Start date
N

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
 
nvx,

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

Kerry Moorman
 
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

nvx said:
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
Click to expand...
Click to expand...
 
Back
Top