none
How to update 2 tables with TableAdapter.Update ? RRS feed

  • Question

  •  

    I have a TableAdapter and a Table with queries that combine multiple tables into one for convenience

     

    but in this table, you can modify say 1 or 2 fields from the Orders Table and 1 or 2 from the Customer's Table

     

    I'm not using SQL, it's OLEDB with a VisualFoxPro Provider.

     

    when I call a TableAdapter.Update, it accepts changes. for one table as the update commands allow you to specify. however I don't see a way to add a second update query so they both execute on the same data.

     

    I know you can add update queries to the TableAdapter, but I'd like to know if anyone's tried those and what is good practice when dealing with this kind of scenario or some kind of way that you might have tried and had this worked easily and properly.

     

    My idea is a little crude, but I thought this:

     

    Dim OrderChanges = DataTable.GetChanges.merge ?  -> clone these somehow, so when the update happens it wont loose the changed rows

     

    TableAdapter.UpdateTable1(OrderChanges)

    TableAdapter.Update(Customers) <- update the original data, so all changes are applied to my binded datatable

     

     

    Notes:

    FoxPro 2.6 Flat Tables over OLEDB only supports sql queries, not advanced things like views etc..  stored procedures can only be called like functions within a query.

    Monday, September 8, 2008 5:15 PM

Answers

  • To summarize,

    You have a single DataTable that combines the results of several database tables.

    You want to now update the database tables.

     

    You can write your own adapter.UpdateCommand that executes multiple statements, one for each table.

     

    Or if your update command needs to be data aware, you could write an adapter.OnRowUpdating event hander that would look the DataRow and then change the RowUpdatingEventArgs.Command to the appropriate command (you'll also have to copy the parameter values from the DataRow yourself).

     

    Monday, September 22, 2008 6:07 PM
    Moderator