.net dataadapter update executes on wrong table

Question

I have a typed dataset with 2 datatables, TableX and TableY. Created in designer. No TableAdapters.

When I fill the TableX I run an SQL and fetch data from another table, TableZ. The table fills up with records and the TableX.tableName is "TableX".

But when I use datadapter and commandbuilder to generate updatecommand for TableX it tries to update TableZ instead. How can I change this?

Oracle.DataAccess.Client.OracleDataAdapter da = new OracleDataAdapter("select * from " + table.TableName, this.Connection);
Oracle.DataAccess.Client.OracleCommandBuilder cb = new OracleCommandBuilder(da);
return cb.DataAdapter.Update(table);

The update generates an SQL statement which starts with "update TableZ...". It should have been "Update TableX...".

I guess this is because the method which fills the datatable runs an sql statement which gets data from another table "select ....... from TableZ", and in the datatable schema it reads:

<?xml version="1.0" standalone="true"?>
<dsConsignmentReport xmlns="http://tempuri.org/dsConsignmentReport.xsd">
    <xs:schema xmlns="http://tempuri.org/dsConsignmentReport.xsd" elementFormDefault="qualified" ...."> 
        <xs:element msdata:EnforceConstraints="False" msdata:Locale="nb-NO" ...> 
        <xs:complexType> 
        <xs:choice maxOccurs="unbounded" minOccurs="0"> -<xs:element name="TABLEX" msprop:BaseTable.0="TABLEZ">

which says that the "baseTable" value is "TableZ". How can I get around this problem? I cannot change my SQL statement for filling the table. Is there anything I can do to get the DataAdapter to execute against the right table? (TableX)

Answer 1

I found out a solution. I built the CUD commands in dataadapter BEFORE calling the

return cb.DataAdapter.Update(table);

Then the CUD commands will generate right.