OleDbDataAdapter Update command updates unmodified rows also

[Sek]

Hi Folks,

I am using OleDb in ADO.NET to update a mdb file.

I open the db, read the table into a dataset. Map the dataset to a
dbgrid.
Make changes in dbgrid and try to save the changes to the same mdb
file.

I just change one row in the dbgrid and then do an
OleDbDataAdapter.Update command using the dataset that was mapped to
the dbgrid.

Instead of updating only the modified row, all the rows in the mdb are
filled with the only modified row in the dbgrid.

Following code does the reading of DB:

********************************************************************************************************

// Open db connection
odbConn = new OleDbConnection(connecStr+dbFileName);
odbAdap = new OleDbDataAdapter("SELECT * FROM "+ tableName , odbConn);
odbAdap.RowUpdating +=
new OleDbRowUpdatingEventHandler (odbAdap_RowUpdating);
OleDbCommandBuilder cmdBldr = new OleDbCommandBuilder(odbAdap);

// Read db table into dataset
ds = new DataSet();
odbAdap.Fill(ds,tableName);

// Set the primary key in dataset
DataColumn[] dCols = new DataColumn[1];
dCols[0] = ds.Tables[tableName].Columns["ID"];
ds.Tables[tableName].PrimaryKey = dCols;


// Construct the Update command for adapter
OleDbCommand cmdUpdt = new OleDbCommand();
StringBuilder cmdText = new StringBuilder();
cmdText.Append(@"UPDATE "+tableName+" SET ");
foreach(DataColumn col in ds.Tables[tableName].Columns)
{
cmdText.Append(col.ColumnName +" = @"+ col.ColumnName+",");
}
cmdText[cmdText.Length-1] = ' ';
cmdUpdt.Connection = this.odbConn;
cmdUpdt.CommandText = cmdText.ToString();

foreach(DataColumn col in ds.Tables[tableName].Columns)
{
OleDbParameter prmZone = new OleDbParameter("@"+col.ColumnName,
OleDbType.VarWChar);
prmZone.SourceColumn = col.ColumnName;
cmdUpdt.Parameters.Add(prmZone);
}
odbAdap.UpdateCommand = cmdUpdt;

// Set the dataset to dbgrid
dataGrid1.DataSource = ds;
dataGrid1.DataMember = tableName;

********************************************************************************************************

Following code does the updation:

odbAdap.Update(ds.GetChanges(),tableName);

When i check the RowUpdating event handler, the method is called only
once meaning onlyl one row is modified. But, howcome all the rows in my
dataset is filled with the same row.


Any pointers ?

TIA,
Sek

 

[Miha Markic [MVP C#]]

Adding WHERE statement helps sometimes ;-)
BTW, why did you declare OleDbCommandBuilder?

 

[Sek]

Hi Miha,

I am trying to read all the rows for all columns...thats y didn't use
WHERE.

Declared OleDbCommandBuilder to automatically fill the UpdateCommand
property of DataAdapter. Also, i have done manual formation of UPDATE
sql command. When i had only OleDbCommandBuilder, Update method throws
error stating "Dynamic generation of SQL command for Update not
supported"...something similar to that.

Let me know, if i am missing anything.

Thanks a ton.
Sek