updating dataset using join and bindingsource?

Question

Hi,

I have created a dataset and in the designer I have created the relations and foreign keys that exist in the database. Basically, I have a product that has a relationship to a table of prices. The keyfield they share is IdProduct in the Prices table. In the Fill/Get of the product I return the Price field.

I also have a DataGrid that uses a BindingSource which uses this table. Everything displays correctly and when I double click on a row within the datagrid I then open up a tabbed form that contains a detailed view of the record selected.
The user at this point is able to make changes to the record and they are properly propogated back to the BindingSource. The problem is that the TableAdapter does not contain the appopriate update, therefore I am not able to call the TableAdapter.Update method with the dataset as I would had I created a tableadapter not using a join.

How am I best to handle this situation.

At the same time I cannot get any modified row:

       dTiendasDs.ProductosDataTable modified = (dTiendasDs.ProductosDataTable)
dTiendasDs.Productos.GetChanges(DataRowState.Modified);

modified is always null

Thanks,

Answer 1

Personally I always do a foreach on all rows..
Then per row I check the Row.RowState property.

BEWARE !! ALWAYS CALL Row.EndEdit() before asking the rowstate because else tough values have changed, the rowstate is not modified.. (maybe that was your original problem).

If unchanged -> nothing
If deleted -> delete statement
if modified -> update statement
if added -> insert statement

You can dynamically generate your SQL based on the columns in the table.
You can even check if values must be updated by comparing the current and original values.

object o1 = pRow[fCol, DataRowVersion.Current];
object o2 = pRow[fCol, DataRowVersion.Original];

Hope this is enough to get you going..

Answer 2

I haven't found a solution that pleases me utterly.

I don't know how to make the update method in one, so that my two tables that are "joined" (inner join) in the dataset, update simultaneously.

I had the problem that even if the controls were databound to productosBindingSource, this bindingSource did not update, so my final solution was to use the row of the dataset directly, and then update the tables of products and prices separately. It is annoying, I hoped to have a unique update.

My code finally is the following.

this.productosBindingSource.EndEdit();

        dTiendasDs.Productos[0].idZona =

Convert.ToInt32(zonasCombobox.SelectedValue);

        dTiendasDs.Productos[0].Precio =

Convert.ToDouble(this.precioTextBox.Text);

        dTiendasDs.Productos[0].EndEdit();


        if (dTiendasDs.HasChanges())
        {
            this.productosTableAdapter.UpdateData(dTiendasDs.Productos[0]["nombre"].ToString(),
                Convert.ToInt32(dTiendasDs.Productos[0]["idZona"]),Convert.ToInt32(dTiendasDs.Productos[0]["idProducto"]));

            this.preciosTableAdapter.UpdateData(
                Convert.ToInt32(dTiendasDs.Productos[0]["idProducto"]),
                Convert.ToDouble(dTiendasDs.Productos[0]["precio"]),IdTienda);
        }

        this.dTiendasDs.AcceptChanges();