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()
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!