Two Step Update Support?

[localhost]

Due to business rules, I cannot do a standard SQL-Update. I actually
need to delete and then insert. How can I handle this, when the
OleDbDataadapter only supports a single UpdateCommand?

The data sources are Access, Visual FoxPro 8.0, and SQL Server (thus
my needs for OleDb).

Thanks.

 

[David Browne]

Due to business rules, I cannot do a standard SQL-Update. I actually
need to delete and then insert. How can I handle this, when the
OleDbDataadapter only supports a single UpdateCommand?

The data sources are Access, Visual FoxPro 8.0, and SQL Server (thus
my needs for OleDb).

Thanks.

A couple of options:

You can change your UpdateCommand to a batch which does a delete followed by
an update. This is easy in SqlServer just change your UpdateCommand's
commandText to

delete from T where id = @id
insert into T (...) values (...)

But how to do this for the Access and FoxPro, I don't know.

Or you can hook the RowUpdating event, change your UpdateCommand to the
plain insert statement, and in RowUpdating run the DeleteCommand for the
affected row before the UpdateCommand runs.

David

 

[Grzegorz Danowski]

(...)

You can change your UpdateCommand to a batch which does a delete followed
by an update. This is easy in SqlServer just change your UpdateCommand's
commandText to

delete from T where id = @id
insert into T (...) values (...)

I suppose that semicolon is needed, for example:
string sql = "Delete From T Where Id = 1; Insert Into T....";

But how to do this for the Access and FoxPro, I don't know.

I think, that only option is to execute two oledbcommands.
I hope it helps.
Grzegorz