Transform insert into update with oledbdatadapater (with Access database)

[wl]

Hi,

Assume I would like to do the following:
- use an OleDBDataAdapter and OleDBCommandBuilder to "fill" and "update" a
dataset

but: the initial SELECT to fill the dataset with contains only a limited
number of records.
Thus when a record is INSERTED in the disconnected dataset, it would be
possible I need to do an UPDATE
to the database (can't use stored procedures because using Access).

I would think I can use the RowUpdating event of the dataadapter to see
whether an insert or update is needed and adjust the
command accordingly. But this does not work. The "update" method fails.

I tried to to something like this (simplified code):

private void RowUpdating (object sender, OleDbRowUpdatingEventArgs e)
{
e.Command = dap.InsertCommand; //Always do an insert event thought the
operation might have been update
}

Thanks in advance,

Wim

 

[Michael Hampel]

The approach I would adopt is this:

Create a data adaptor
Create an insert/update command
assign the command to the appropriate dataadaptor command ie
insertcommand, updatecommand

create and add a new row to the dataset table rows collection. This
will set to row status to new.
Call the data adaptor update method which will persist the new/changed
rows to the database.

Finally call the dataset accept changes method which sets the row
indicators to unchanged.

The data adaptor update method will check each tables row property and
call the appropriate command based on its status. You must not call
acceptchanges till after the update because this will reset all the
row status indicators.

I don't see why you need to use the RowUpdating event as this I think
would be called at the wrong place.

Michael