OleDbDataAdaptor Updates only first changed row from datatable

  • Thread starter Thread starter Duncan Spence
  • Start date Start date
D

Duncan Spence

Anyone got any clues on this?

I am using an oledbdataadapter update command to store changes to a
dataset table back to the database, the database is access, I'm using
a stored query to perform the update and it works when run manually.

The table will normally have updates to 4 rows, the first one found
works, the others don't appear in the database.

I've checked with OleDbRowUpdatingEventHandler event handlers and each
of the 4 rows is caught and the parameters look fine (right data and
order for the query).

I've also checked with a OleDbRowUpdatedEventHandler and the 1 row has
been updated each time - this is what I would expect.

Finally, I've tried it with transactions on and off, same result.

Anybody got any ideas?

Thanks,

Duncan
 
Hi Duncan,

Can you show us UpdateCommand definition?
How is the stored query defined?
 
Hi Miha, thanks for taking an interest,

Here's the query in access, defined through the query screen with the
name sp_DeckUpdateByGameCardId:-

UPDATE tblDecks SET tblDecks.CurrentPlayerId =
[CurrentPlayerIdRequired], tblDecks.intOrder = [intOrderRequired],
tblDecks.LocationId = [LocationIdRequired]
WHERE (((tblDecks.GameId)=[GameIdRequired]) AND
((tblDecks.CardId)=[CardIdRequired]));

The combination of GameId and CardId identifies an individual row.

The update is done through a couple of classes, from the top

public void Update( DataSet dtsDeck, string strTableName )
{
//updates the details of the deck back to the database
//ACCEPTS: dataset - containing the data table
// string - name of the table to be updated
//RETURNS: nothing
//PREREQS: calls a stored procedure named sp_DeckUpdateByGameCard
through a querybuilder

//create the update command and set the parameters
OleDbCommand cmdUpdate = new OleDbCommand( "exec
sp_DeckUpdateByGameCard" );
cmdUpdate.Parameters.Add( new OleDbParameter(
"CurrentPlayerIdRequired", OleDbType.Integer, 4, "CurrentPlayerId" )
);
cmdUpdate.Parameters.Add( new OleDbParameter( "intOrderRequired",
OleDbType.Integer, 4, "intOrder" ) );
cmdUpdate.Parameters.Add( new OleDbParameter( "LocationIdRequired",
OleDbType.Integer, 4, "LocationId" ) );
cmdUpdate.Parameters.Add( new OleDbParameter( "GameIdRequired",
OleDbType.Integer, 4, "GameId" ) );
cmdUpdate.Parameters.Add( new OleDbParameter( "CardIdRequired",
OleDbType.Integer, 4, "CardId" ) );

OleDbDataAdapter dtaAdatpter = new OleDbDataAdapter();
dtaAdatpter.UpdateCommand = cmdUpdate;

//call the update function in the base class to take care of
transactions etc.
UpdateFromDataSet( dtsDeck, strTableName, dtaAdatpter );

} //Update

to the bottom

protected void UpdateFromDataSet(
DataSet dtsDataSet,
string strTableName,
OleDbDataAdapter dtaAdapter )
{
//updates the database using the supplied data adapter and dataset
//ACCEPTS: dataset - containing the data to be saved
// string - name of the table containing the
updates
// oledbdataadapter - containing the update commands
//RETURNS: nothing
//PREREQS: none

dtaAdapter.UpdateCommand.Connection = conConnection;

//if there is an ongoing transaction, add it to the command
if( mintTransactionDepth > 0 )
dtaAdapter.UpdateCommand.Transaction = mtrnCurrentTransaction;

dtaAdapter.RowUpdating += new OleDbRowUpdatingEventHandler(
RowAboutToUpdate );
dtaAdapter.RowUpdated += new OleDbRowUpdatedEventHandler( RowUpdated
);

dtaAdapter.Update( dtsDataSet, strTableName );

} //UpdateFromDataSet

The event handlers are simply place holders to allow me to query the
arguments using watches.

I only set up an update command because I can guarantee no Insert or
Delete operations.

Hope this helps,

Duncan
 
Hi Duncan,

I think that there is a problem with Access' queries.
Apparently they accept your parameters only for the first time and ignore
them subsequently.

As the workaround I see only the option that you port your sql query
statament into command object.
 
Back
Top