Question Problem with Updating related tables

Caraldur

Member
Joined
Oct 23, 2009
Messages
19
Programming Experience
Beginner
Hello everyone,

I have watch the Beth Massi video called saving related tables so that I could update two tables with a parent child relationship.

I followed her video but I have run into a problem that I can resolve on my own so I need some help from you guys.

I am using a MySQL database and I have two tables and orders table and a orderitems table. I created a Datasource for the two tables called OrdersDataSet. I created a relationship between the orders table and the orderitems table using the OrdersDataset.xsd file. The PK and FK is OrderID and set to cascade updates and deletes.

The form I have has the Orders table added to the form as details and the orderItems tables was added to the form as a datagrid.

Here is my Form Load code
VB.NET:
    Private Sub userNewOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
       Me.CustomersTableAdapter.Fill(Me.CustomerLookup.customers) 'populating combobox
        Me.OrdersTableAdapter.Fill(Me.OrdersDataSet.orders) 'populate orders fields
        Me.OrderitemsTableAdapter.Fill(Me.OrdersDataSet.orderitems) 'populating orderitems datagrid
        mdiMain.userNewOrderFormOpen = True 'set variable when form is open

    End Sub

Here is the code for the save button
VB.NET:
    Private Sub OrdersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrdersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.OrdersBindingSource.EndEdit()
        'Me.OrderitemsBindingSource.EndEdit()
        'Me.OrdersTableAdapter.Update(Me.OrdersDataSet.orders)

        If Me.save() Then
            MsgBox("Changes saved")
        End If
    End Sub

Here is the code for the save function
VB.NET:
    Function save() As Boolean
        Dim saved As Boolean = False
        Try
            If OrdersDataSet.HasChanges Then
                Dim orderUpdates() As DataRow = Me.OrdersDataSet.orders.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
                Dim ordersdetail() As DataRow = Me.OrdersDataSet.orderitems.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                Me.OrdersTableAdapter.Update(orderUpdates)
                Me.OrderitemsTableAdapter.Adapter.Update(ordersdetail)

                Dim orderDelete() As DataRow = Me.OrdersDataSet.orders.Select("", "", DataViewRowState.Deleted)
                Dim orderitemsDelete() As DataRow = Me.OrdersDataSet.orderitems.Select("", "", DataViewRowState.Deleted)

                Me.OrdersTableAdapter.Update(orderitemsDelete)
                Me.OrderitemsTableAdapter.Adapter.Update(orderDelete)
            End If

            Return saved
        Catch ex As Exception
            MsgBox("Error during save: " & ex.Message)
        End Try
    End Function
According the the Masi video the line of code that should be used to update the orderitems table is Me.OrderitemsTableAdapter.Update(ordersdetail) but I get a blue squiggle line saying that the Update is not part of the OrderItemsTableAdapater so I had to add the .adapter.update to get it to work but now I am getting an error that says Update requires a valid insert command when passed Datarow collection with new rows.

I then tried to use Me.OrdersTableAdapter.Update(orderitemsUpdates) but this line throws an error saying that I can not have the column purchaseorder null. The problem is that there is no purchase order column in the orderitems table but it is located in the orders table.


Here is the code to endedit when a row is added to the datagrid

VB.NET:
   Private Sub OrderitemsDataGridView_Enter1(ByVal sender As Object, ByVal e As System.EventArgs) Handles OrderitemsDataGridView.Enter
        Me.OrdersBindingSource.EndEdit()
    End Sub

I hope that my post is not too confusing and I hope someone here can help me with this problem.

thanks in advance for the help.

-Fred
 
Back
Top