Houssam,

To add on to what Kevin wrote...

When you modify data in multiple levels of a hierarchical DataSet, you have to deal with the order of pending inserts and deletions to your database. The challenge is to submit these changes in the proper order to comply with the referential integrity constraints in your database. Updates in a hierarchical DataSet should submit the changes in the following order:

1. Submit new parent records.
2. Submit new child records.
3. Submit modified parent records.
4. Submit modified child records.
5. Submit deleted child records.
6. Submit deleted parent records.

The Select method of the DataTable object is a way to locate DataRow objects that satisfy a desired RowState. The Select method returns an array of DataRow objects. And one of the overloaded DataAdapter Update methods accepts an array of DataRow objects.

Here is an example of using the Select method to isolate just the desired changes and submit them to the database in the desired order:

// Parent table
DataTable tblCustomers = ds.Tables["Customers"];
// Child table
DataTable tblOrders = ds.Tables["Orders"];

//Submit the new customers and then the new orders.
DataAdapterCustomers.Update(tblCustomers.Select("", "", DataViewRowState.Added));
DataAdapterOrders.Update(tblOrders.Select("", "", DataViewRowState.Added));

//Submit the modified customers and then the modified orders.
DataAdapterCustomers.Update(tblCustomers.Select("", "", DataViewRowState.ModifiedCurrent));
DataAdapterOrders.Update(tblOrders.Select("", "", DataViewRowState.ModifiedCurrent));

//Submit the deleted orders and then the deleted customers.
DataAdapterOrders.Update(tblOrders.Select("", "", DataViewRowState.Deleted));
DataAdapterCustomers.Update(tblCustomers.Select("", "", DataViewRowState.Deleted));

>Kevin,
>
><--
>Wouldn't the child records already have the parent code at the point where you save the DataSet?
>>
>
>Yes, but what is exactly the order of update? If its: the first record updated gets first to the database, then the child record will get there first and we will get an error. Since at this point, the parent does not yet exist, bits its already referenced by the child record.
>
>No. Here's the scenario:
>1- Create new record (1). parent code is null.
>2- Create new record (2). parent code is null.
>3- Modify record (1) and set parent code = (2).
>
>Now, how do we handle such cases during batch update?
>
>Thanks