Solved

Linq parent child insert causes ForeignKey error

Posted on 2011-09-16
5
1,034 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
  • 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now