Custom writing OleDbCommand commands.

Question

Hello!

I have (another) question about writing OleDbCommand's. I have a complex select command (includes several left joins) and OleDbCommandBuilder is unable to generate update commands for me.

I can't seem to grasp the magic behind writing update commands. I know the structure of the SQL update statement, however I am a bit puzzled about how this works with OleDbDataAdapter. I know that when I call adapter.Update() it will update only the necessary rows of DataTable, however I don't know how to write a Update command so it will be able to know what field in what table needs updating.

I would really appreciate if someone could explain the process to me and point me in the right direction.

Edit: Since no one is answering, I will post some more details regarding my question:

I have a select statement in this form:

select field1, field2, fieldn from table1 left join table2 on condition left join table3 on condition where condition1 and condition2

one is puzzling me is how should I write an update statement since I can not write it like this:

update table1, table2 set field1 = newvalue where condition1 and condition2

So I don't understand how to do this, since update statement only accepts one table argument. Is it even possible to call OleDbDataAdapter.Update method on this kind of DataTables?

Answer 1

Are you trying to do this ?

update table1 set table1.field1 = table2.field2
from table1 left join table2 on condition1
where condition2 and condition3

I'm quite sure you can't update more than one table at a time. So you would have to issue one update statement for each of the tables. If you're trying to reduce the number of roundtrips to the server you may separate the statements with ";", or juste call a stored procedure that would do the n updates

Still check out INSTEAD OF triggers, that maybe what you're looking for

Answer 2

and for IN expr please