Manual Update rather than UpdateBatch when underlying data substantially changed

• Previous message: Thomas Holme: "Re: ADO OLEDB connection to MSSQL: pooling not working"
• Next in thread: Brian Bushay TeamB: "Re: Manual Update rather than UpdateBatch when underlying data substantially changed"
• Reply: Brian Bushay TeamB: "Re: Manual Update rather than UpdateBatch when underlying data substantially changed"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Mon, 21 Feb 2005 10:56:06 -0000

I have a clientside cursor, batchoptimistic locked dataset with updates
pending but the internal sequence of SQL updates that calling UpdateBatch
would implement will encounter write conflicts and key-violations etc. and
fail as the underlying data has substantially changed. However I simply want
to replace the data at the server with that cached local data regardless.
How do I do that?

To explain more fully:

I am using batchoptimistic locking and clientside cursors to make edits to
data in a master details relationship. The mdb file has RI defined to
cascade both deletes and updates from master table to details table.
Sometimes Master records are deleted and the remaining record IDs renumbered
(no gaps), which means that some details records are deleted the partial key
of others is updated. This may seem odd, but I have reasons to want it like
that.

Of course the local cached datasets do not benefit from the RI rules, hence
locally (while user making changes but not saved them to server) the
deletion and renumbering has to be done explicitly. This is not a problem,
but I have found that when finally doing the batch updates the RI causes
write conflicts. Not a surprise really, when master.updatebatch called the
RI cascades changes to the details table, then when details.updatebatch is
called with all the details deleted or partial key values changed it finds
the underlying data has changed.

So when deletion and renumbering has happened rather than call
details.updatebatch and try and untangle the conflicts with the cascaded
changes, I thought one solution would be to do the updates manually. That is
delete all the affected details at the server and re-populate it with the
records held cached in the local dataset. How can I most easily do this? Do
I loop through the dataset creating a SQL insert statement to execute, or is
there some clever ADO method I can use?

I hope someone can understand what I am getting at.

Dave

• Previous message: Thomas Holme: "Re: ADO OLEDB connection to MSSQL: pooling not working"
• Next in thread: Brian Bushay TeamB: "Re: Manual Update rather than UpdateBatch when underlying data substantially changed"
• Reply: Brian Bushay TeamB: "Re: Manual Update rather than UpdateBatch when underlying data substantially changed"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]