Using TableAdapters to Insert Related Data into an MS Access Database

See more:

VB

ADO.NET

DataGridView

, +

Hi Everyone!

I am creating a simple application to track Customers with their Orders using Visual Basic.NET and MS Access 2007.
I set up the updates to the database like shown in Beth Massi's video: msdn.microsoft.com/.../bb725826 (Updating Related Tables).
I dragged the Customer table as Details view and the Orders as a DataGridView control from the DataSources Window.

I would like to add more orders for a customer.
I tried to save the new rows created with the DataGridView control with this code:

Private Function Save() As Boolean

       Dim saved As Boolean = False

       If Me.MyDataSet1.HasChanges Then

           Try

               Dim customerUpdates() As DataRow = Me.MyDataSet1.Customer.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

               Dim orderUpdates() As DataRow = Me.MyDataSet1.Order.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

               Me.CustomerTableAdapter1.Update(customerUpdates)

               Me.OrderTableAdapter1.Update(orderUpdates)

               Dim customerDeletes() As DataRow = Me.MyDataSet1.Customer.Select("", "", DataViewRowState.Deleted)

               Dim orderDeletes() As DataRow = Me.MyDataSet1.Order.Select("", "", DataViewRowState.Deleted)

               Me.OrderTableAdapter1.Update(orderDeletes)

               Me.customerTableAdapter1.Update(customerDeletes)

               saved = True

           Catch ex As Exception

               MsgBox(ex.ToString)

           End Try

       End If

       Return saved

   End Function

----------------------------------
This is the code for the Save button:

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

       Try

           Me.Validate()

           'Call EndEdit on all BindingSources!

           Me.CustomerBindingSource.EndEdit()

           Me.OrderBindingSource.EndEdit()

           Me.TableAdapterManager.UpdateAll(Me.MyDataSet1)

           If Me.Save Then

               MsgBox("Your records were saved.")

           End If

       Catch ex As Exception

           MsgBox(ex.ToString)

       End Try

End Sub

When I try to add rows for the first customer in the ListBox and click the Save button, the rows are being saved back to the database where I can open and verify it. But when I try to pick another customer from the ListBox, I get a strange error which says:
--------------------------------------------------------------------------------
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
-------------------------------------------------------------------------------
I have no idea why it is giving me this error and how to deal with it. I've searched many places but could not find how to deal with this error.

The second problem I have is when I try to choose another customer from the ListBox and it continues to give me an error:

"Cannot clear table Customer because ForeignKeyConstraint CustomerTableOrderTable enforces constraints and there are child rows in OrderTable.'"

on this line --->>>Table.Clear()

in the code below:

If ClearBeforeFill Then

              Table.Clear()

          End If

          Return Adapter.Fill(Table)

While at other times, it gives me a completely different error: "Object reference not set to an instance of an object."

on this line --->>> Dim CustomerID As String = CustomerListBox.SelectedValue.ToString

in the code below:

Dim CustomerID As String = CustomerListBox.SelectedValue.ToString

      CustomerTableAdapter.FillByCustomerID(MyDataSet1.CustomerTable, CustomerID)

      OrderTableAdapter.FillByCustomerID(MyDataSet1.OrderTable, CustomerID)

I even tried to fix this by turning off the Constraints to false on the DataSet designer. But nothing seems to be working. Could this mean that when I try to select a different Customer from the ListBox, there is a problem with the DataTable being filled in the DataSet ??

Please help me resolve these two errors as I am nearing the completion of my application and I'm really stuck in the middle.

Appreciate all your help, thanks a lot!