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
Here is the code for the save button
Here is the code for the save 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
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
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
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