?
Solved

Linq parent child insert causes ForeignKey error

Posted on 2011-09-16
5
Medium Priority
?
1,092 Views
Last Modified: 2012-05-12
Hi i'm new to linq to sql classes and i'm having a problem with inserting a parent and child together using the code below

 Using db As New FMSDataContext
                        Dim NS As New Site
                        NS.Title = "newsite"
                        NS.Created = Now()
                        NS.CreatedBy = "me"
                        db.Sites.InsertOnSubmit(NS)


                        Dim Address As New Address
                        Address.Address1 = "test"
                        Address.Town = "testtown"
                        Address.PostCode = "teste"
                        Address.Created = Now()
                        Address.CreatedBy = "me"

                        NS.Addresses.Add(Address)
                        db.Addresses.InsertOnSubmit(Address)


                        db.SubmitChanges()

                    End Using

i can submit the changes for each table seperatly and they are written tot he database, i can't get the releations to tie together using this method (i could get the ne id and then use this and have 2 submit changes but i've read you don't need to do this).

the problem is that the above code cause the error below
System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException: Operation is not valid due to the current state of the object

i belive that this is do with the id field already being written.

Thanks in advances
0
Comment
Question by:bolt81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36548000
You should provide the Id Field in Address.FK_NewSiteID  then just do the following:

db.Addresses.InsertOnSubmit(Address);
db.SubmitChanges()

Your Code should look like:

                Using db As New FMSDataContext
                        Dim NS As New Site
                        NS.Title = "newsite"
                        NS.Created = Now()
                        NS.CreatedBy = "me"
                        db.Sites.InsertOnSubmit(NS)


                        Dim Address As New Address
                        Address.Address1 = "test"
                        Address.Town = "testtown"
                        Address.PostCode = "teste"
                        Address.Created = Now()
                        Address.CreatedBy = "me"
                       
                        Address.SietID = NS.ID

                        db.Addresses.InsertOnSubmit(Address)
                        db.SubmitChanges()

                    End Using

this will tie the relations together
0
 
LVL 1

Author Comment

by:bolt81
ID: 36548014
Hi the releationships the other way round "site" is the parent and "Address" is the child

i've edited the code to look like this
Using db As New FMSDataContext
                       
                        Dim Address As New Address
                        Address.Address1 = "test"
                        Address.Town = "testtown"
                        Address.PostCode = "teste"
                        Address.Created = Now()
                        Address.CreatedBy = "me"

                       db.Addresses.InsertOnSubmit(Address)

                        Dim NS As New Site
                        NS.Title = "newsite"
                        NS.Created = Now()
                        NS.CreatedBy = "me"

                        NS.AddressID = Address.AddressID
                        db.Sites.InsertOnSubmit(NS)

                        db.SubmitChanges()

                    End Using

this creates a record in each table with no errors but the new address id ins't put into the site record
0
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36548072
Ok after  the last submitChanges() you have to re new your DataContext because of a refresh Issue,

do this after end using :

db As New FMSDataContext

and the new address object will be in its parent (site) record Like :

MyObjAddress.Sites
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36570486
I usually use two submit changes. And you would use the SiteID in Address if the Site is the parent.
0
 
LVL 2

Accepted Solution

by:
Lord_Death earned 2000 total points
ID: 36578641
If You have something like :

Site : SiteID , SiteName ,...
Address : AddressID,Address1,FK_SiteID

then Site  would be parent of Address , and i Would use the parent id cause you have to insert the parent at first  (Foreign Key Constraint)  and then after submitting changes you have the value of your primary key,

now when you want to add child for your parents in this case Addresses  you use the parent property in this case Site .

a simple Example would be :

 Using db As New FMSDataContext
                       
                        Dim ObjCountry As New Country
                        ObjCountry.Name = "United States of America"
                        ObjCountry.Code = "US"
                       
                        db.Countries.InsertOnSubmit(ObjCountry)
                        db.SubmitChanges()
                        'Here I assume CountryID is as AutoIdentity Field , so after
                        'SubmitChanges() I have a  value for CountryID

                        Dim ObjCity As New City
                        ObjCity.CityName = "New York"
                        ObjCity.CityCode = "NY"
                        ObjCity.CountryID = ObjCountry.CountryID

                        db.Cities.InsertOnSubmit(ObjCity)

                        Dim ObjCity2 As New City
                        ObjCity2.CityName = "Los Angeles"
                        ObjCity2.CityCode = "LA"
                        ObjCity2.CountryID = ObjCountry.CountryID

                        db.Cities.InsertOnSubmit(ObjCity2)


                        db.SubmitChanges()

        End Using
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question