Saving Data from Related Data Tables (Hierarchical Update)

[Meddix]

Hi Everyone,

I am new to C#, kind of new to programming altogether. It is something I am really enjoy learning but I am a little stuck with hierarchical updates.

I am using Visual Studio 2008 and SQL Express.

I have been working on a simple application for my girlfriends business. I have a database with 2 tables, Customers & Vacancies. I use the IDE to create the data-bound controls, Customers being "Details" and Vacancies being "DataGridView".

On the Customer table I have a CustomerID column. This is a Primary Key and has Identity configured so each record is unique and the number is incremented.

I created a Foreign Key Constraint (one to many) from Customer - CustomerID to Vacancies - CustomerID. I did this so each record in the Vacanacie table would update when the Customer is changed on the form.

I may have got some words wrong there, but hopefully you know what I am getting at =)

When I run the program, i receive the following error in debug mode when I try and add a user and vacancy at the same time.

"The UPDATE statement conflicted with the FOREIGN KEY constraint"

I found an article on MSDN (http://msdn.microsoft.com/en-us/library/bb384432.aspx) that sounds like just what I am trying to achieve.

I went through the walkthrough step by step, when I run the program in debug and try to add a customer and an order together, I receive the following error

"The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Orders_Employees". The conflict occurred in database "90C91394EA995F36B4DCCD90A88CCCD8_008\PROJECTS\HIE RARCHICALUPDATEWALKTHROUGH\HIERARCHICALUPDATEWALKT HROUGH\BIN\DEBUG\NORTHWND.MDF", table "dbo.Employees", column 'EmployeeID'."

This is the same error as I receive in my application.

Has anyone else had problems with this walkthrough? If I can solve this, then I am confident I can solve the problem with my application.

I hope all this makes sense, this is a huge learning curve for me.

Many thanks

Dave

[jkmyoung]

It says that the error stems from the "FOREIGN KEY constraint "FK_Orders_Employees", table "dbo.Employees", column 'EmployeeID'."


What does this foreign key represent?
Or have you just renamed everything in your example?

[Meddix]

Hi jkmyoung,

Thanks for replying.

The error "FOREIGN KEY constraint "FK_Orders_Employees", table "dbo.Employees", column 'EmployeeID'." I received when I did the example on the MSDN site.

I am receiving the same error in my application, which is the CustomerID one. Maybe I should have left that out, probably made a bit more confusing for other readers.

Because I am receiving the same error in the MSDN walkthrough, I thought it would be easier to give that as an example for people to look at and even do and see if they receive the same problem as me or even resolve it!

If I can find a resolution to the MSDN walkthrough, then I should be able to use the same resolution to fix my own app.

Hope that makes a little more sense =)

Many thanks

[jkmyoung]

ah, ok. Can you post your udpate statement, and the error you are getting?

[Lukasz Rudziewicz]

Hi there,

In your scenario, the VS 2008 autogenerated code won't work unless you do further configuration. Please follow these steps:

1. Right-click on your data set listed in DataSources and go to Edit DataSet with Designer,

2. Once the designer opens, select the parent table adapter, right-click on it and go to Configure,

3. Table adapter configuration wizard opens, click on the Advanced Options button and make sure that the 'Refresh the data table' option is checked. By default it is checked, yet make sure anyway. Close the wizard and go back to the dataset designer. Now double-click the table relation,

4. Relation dialogbox opens, here select 'Both Relation and Foreign Key Constraint' and below choose Cascade for both Update Rule and Delete Rule. Click OK button,

5. Go to the place in your code where the table adapters are filled (where the Fill method is called). If your code is autogenerated, these methods are called in Form_Load handler. Make sure that the order of calls is correct. The correct order is to call Fill for parent table adapter and then for the child table adapter. It might be the other way round, so correct it manually.

Run and test your code. It should work as expected now.

Lukasz