OleDbDataAdapter Update command updates unmodified rows also

microsoft.public.dotnet.framework.adonet

4 Nov 2005 11:37 AM

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

4 Nov 2005 1:13 PM

Miha Markic [MVP C#]

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

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"Sek" <sek***@gmail.com> wrote in message
news:1131104268.829703.102360@f14g2000cwb.googlegroups.com...
> 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
>

4 Nov 2005 4:26 PM

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