[RESOLVED] Changing data in related tables

[gtrahern]

I am creating a simple data driven application built on .net 3.5 using VS 2010 Express that is only for educational purposes. My data is stored in an Access 2007 db named test. I have one table named Customers and another table named Orders. The Customers table has PK CustomerID and this field is the FK in the Orders table. In the access db, the two tables have a one to many relationship. In the relationship definition, I clicked “Enforce Referential Integrity”, “Cascade Update Related Fields”, and “Cascade Delete Related Records”. I allowed visual studio to create my dataset for me, selected both tables, and Hierarchical Update and EnforceConstraints are both set to true. I’m also using visual studio’s drag and drop capability to add the data controls to my form. I dragged the customers table to my form using details, then I dragged the orders table to create a datagridview. In the dataset designer, I opened up the FK_Customers_Orders and chose both relation and foreign key constraint with the update rule set to cascade and the delete rule also set to cascade. My ordersbindingsource is set to datasource customersbindingsource with datamember fk_customers_orders. My form code is below. Here is the problem that I need help with. If I run the form and click the add button on the navigator, type in customer information, then go to the orders datagridview and type in order information and click the save button, I receive the following error:

You cannot add or change a record because a related record is required in table ‘customers’.

Public Class Form1

    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.TableAdapterManager.UpdateAll(Me.TestDataSet)
    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 'TestDataSet.Customers' table. You can move, or remove it, as needed.
        Me.CustomersTableAdapter.Fill(Me.TestDataSet.Customers)
        'TODO: This line of code loads data into the 'TestDataSet.Orders' table. You can move, or remove it, as needed.
        Me.OrdersTableAdapter.Fill(Me.TestDataSet.Orders)
    End Sub

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

[sparbag]

Hello,

Having never programmed in VB.NET and looking at your rather verbose list of actions (VB programmer you are), I have the suspicion that this is a database issue.

I believe you are having this error because you are creating a new customer, without writing the record to the database, and then creating an order for them, then attempting to write this record to the database.
The resultant message is stating that their is no customer for the order.

If in your code you can evidence where you have written the customer record to the DB prior to the writing of the order to the DB, that will assist in tracing the issue.

Kind regards

Steve

PS I will always advise against cascade update and cascade delete due to knowing what they do. If you do not know then untick them as options in your database.

[gtrahern]

I don't think I need to write the code myself to make sure that the customer record is inserted first, I believe that the tableadaptermanager (which I didn't mention in the first post) takes care of that for me. I learned of this method from watching the video in this link: http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

[jmcilhinney]

The problem really comes down to the fact that the Jet and ACE OLE DB providers don't support multiple SQL statements in a single command. When you save the parent data, the database generates permanent IDs that may or may not match those generated by the DataTable to be used temporarily. With SQL Server, you would write your SQL code to retrieve that ID and refresh your DataTable with it. That would be pushed down to the child table automatically and then those records would save with the correct foreign key value.

If the parent IDs don't match and you don't refresh the parent DataTable, the foreign key values in the child DataTable are invalid. That will prevent them saving or, even worse, save them against the wrong parent.

Because you can't refresh the IDs automatically, you have no choice but to save the parent data, retrieve the parent data again and then update the foreign keys yourself somehow. It's a pain, and it's why you shouldn't use Access when you need to save related data this way.

[gtrahern]

jmcilhinney, I was afraid of that but thank you for taking the time to answer.

[arcanine]

...It's a pain, and it's why you shouldn't use Access when you need to save related data this way.

I've spent approximately three full days (72 hours) looking for an answer like that! I kept wondering why my FKs kept breaking, and deep down I was afraid it was because I'm stuck using Access (legacy project).

Well, I know this is an old thread, but you mentioned that the new parent ID would need to be retrieved and then all Child records matching the old parent ID would need to be updated with the new. Can you elaborate on that thought, because this is exactly what I need.