Update three tables

Question

Hi.
I have a db with three tables. On the form i drop the first table , then the second (related) , then the third (related to the second).
I use 1 dataset and three datagridviews. The problem is that i can't update the three tables when i use the update button. I use VB 2008

thanks

Answer 1

             Check this Link:
                                          http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/0d5e3307-40e8-422c-a83a-697ca12b5209

---

WIth Regards, Alizee Hayek.

Answer 2

You cannot update the database with those tables by using some generic created code, you should use code which you made yourself.

You should do a kind of sequence.

First you get the changes from the datatable (using the rowstate) from the deleted lowest level detail rows (and that in your case down up)
then you take the deleted rows from one level below that
then you take the deleted top rows
then you can do the new top rows
then you can do the middle table new rows
then you can do the lowest level new rows.
after that you can do the updates, because they stay in the database the sequence of that is not important.

This sequence is not strict as long as you delete forever first the depending detail level row first and the depending new top level first.

Be aware that it (should) be right in your dataset so don't be afraid for situations which are not there like a deleted row from a not yet added top table row.

---

Success
Cor

Answer 3

Hi and sorry for the delay.
I used the MSDN help with the hierarchical update and i manged to update the first two tables but not the third.
 I used this block of code :


Private Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.CustomersBindingSource.EndEdit()
        Me.OrdersBindingSource.EndEdit()
        Me.Order_DetailsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)

    End Sub
 
   Private Sub OrdersBindingSource_AddingNew(ByVal sender As System.Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrdersBindingSource.AddingNew
        Me.CustomersBindingSource.EndEdit()
    End Sub

    Private Sub Order_DetailsBindingSource_AddingNew(ByVal sender As System.Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles Order_DetailsBindingSource.AddingNew
        Me.OrdersBindingSource.EndEdit()

    End Sub

It Should be ok with the third table but it's not. Is here something i could do about it?


thanks

Answer 4

if your maneged to upadate the two table and not the third one, its means that your third table is not part of your dataset. You need to do it separetly from your two tables. and use the dataset that is linked to your third table.

---

Don't judge me, just Upgrade me. Thanks!

Answer 5

Hi.
When i double-click the northwind dataset i can see all three tables and their relations, Customer to order and order to orderdetail.
I changed the relation from -relation only-  to  -relation and foreign key constraint- . Nothing happend. Then i changed the rules first to nothing and then to cascade with no luck .I can't even update any existing data.

thanks in advance

Answer 6

Hi koskappas,

When you drag items from the data sources window, code to save data is added automatically for the first table dropped onto a form.  Any additional tables added to a form require the manual addition of code required to save data.
Here is a walkthrough you can look into
http://msdn.microsoft.com/en-us/library/4esb49b4.aspx
And here is an example about update multiple tables in datagridview

http://social.msdn.microsoft.com/Forums/en-US/vbide/thread/473240a4-af30-41cf-ae30-08f052a9eb0d  

Hope this helps

Regards
Jeff Shan

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hallo koskappas,

In the Northwind database sample the relations are already set, they are transported if you do it right to the dataset.

Doing something about relation in a dataset that should be updated does not help you.

I am not aware in how far in the newest versions the wizards with updates do it accoording to the mapping like I already wrote.

---

Success
Cor

Answer 8

There is a great video on the TableAdapterManager at
http://windowsclient.net/learn/video.aspx?v=14630 

Visual Basic programmers will learn how to properly control updates, inserts and deletes in multi-user applications on forms that edit master-detail related data using the TableAdapterManager in Visual Studio 2008.

Presented by Beth Massi

Pat NH USA

Answer 9

Hi Cor Lightert

I changed the UPDATEALL command with 3 UPDATE commands, one for each table, but nothing happend.
Here's the code i used :




Private Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.CustomersBindingSource.EndEdit()
        Me.OrdersBindingSource.EndEdit()
        Order_DetailsTableAdapter.Update(Me.NorthwindDataSet.Order_Details)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        'TODO: This line of code loads data into the 'NorthwindDataSet.Customers' table. You can move, or remove it, as needed.
        Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
        'TODO: This line of code loads data into the 'NorthwindDataSet.Orders' table. You can move, or remove it, as needed.
        Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders)
        'TODO: This line of code loads data into the 'NorthwindDataSet.Order_Details' table. You can move, or remove it, as needed.
        Me.Order_DetailsTableAdapter.Fill(Me.NorthwindDataSet.Order_Details)



    End Sub

    Private Sub OrdersBindingSource_AddingNew(ByVal sender As System.Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrdersBindingSource.AddingNew
        Me.CustomersBindingSource.EndEdit()
        CustomersTableAdapter.Update(NorthwindDataSet.Customers)
    End Sub

    Private Sub Order_DetailsBindingSource_AddingNew(ByVal sender As System.Object, ByVal e As System.ComponentModel.AddingNewEventArgs)
        Me.OrdersBindingSource.EndEdit()
        OrdersTableAdapter.Update(NorthwindDataSet.Orders)
    End Sub



Then i tried with the  3 updates together ,one after the other.

Am i doing something wrong here ?

thanks

Answer 10

Hi Cor Lightert

I changed the UPDATEALL command with 3 UPDATE commands, one for each table, but nothing happend.
Here's the code i used :

As Jeff mentioned above, you will most likey need to create your own Commands for INSERT, UPDATE, DELETE.  What you posted does not show how you accomplish this.

---

jfc

Answer 11

Hi jamesfreddyc

The code i posted above is part from the code i found in the Microsoft MSDN site about hierarchical updates.
the link is :   http://msdn.microsoft.com/en-us/library/bb384432.aspx

The code is for two tables but it says that a third table can be added.

thanks

Answer 12

I have very little experience with the Designer, so I'll probably drop out of this thread.  But I did notice in the article that:

1.  Verify that heirarchal updates are enabled in the DataSet
2.  If new rows are being added to the child table, then you will have to commit new rows of to the dataset for the parent table for the foreign key constraints to enable those child rows to be added to the dataset.

I have a gut feeling that 2. above is messing it up for you.  Step thru your code and make certain that the order/steps are actually working as expected.

---

jfc

Answer 13

Hi jamesfreddyc


If the article says a third table can be added, there's must be a way.

Otherwise i have to put the first two tables in one form and then the second and the third table into another to insert all necessary data, but that's not
very convinient .

The realations in the dataset are the same as the database downloaded from site of microsoft

Answer 14

Hi jamesfreddyc


If the article says a third table can be added, there's must be a way.

Otherwise i have to put the first two tables in one form and then the second and the third table into another to insert all necessary data, but that's not
very convinient .

The realations in the dataset are the same as the database downloaded from site of microsoft

I sure there is.  Again, as Jeff mentioned, you are probably going to have to leave the comfort of the Designer and code some things yourself.  I didn't read the info in the links he posted, but it's probably a good start.  Also, did you verify that if new rows are added to the 3rd DataTable, then you will first have to commit new rows to it's parent table in order to maintain the pk and fk constraints?

---

jfc

Answer 15

Thanks jamesfreddyc

I see what i can do about it

Answer 16

Kokapas, if you do it like this, then you have the trouble that you have the chance that you Delete orders, where later is tried to remove order detailrows.


Have a look at this page to prevent that

http://msdn.microsoft.com/en-us/library/5dxfaha8.aspx

Be aware that when you are ready with this procedure you have to do an acceptchanges on the dataset.

---

Success
Cor