Hi Guys,
I’ve got 2 tables where each has a primary key which is also the identifier (Both fields called “id” in each table)
In the “picture” table as one of the fields I’ve got the photoalbumid (The id of the “photoalbum” table). This field is just an int field.
I’m getting the following error:
“Cannot insert explicit value for identity column in table ‘Picture’ when IDENTITY_INSERT is set to OFF”
Can anyone give me some advise?
It’s such a basic thing I would hope that there’s just something small in the creation of the database that I’ve missed out.
At the risk of sounding stupid, I don’t know how I can find the exact query within visual web developer.
I’m using the insert function of the listview control with a linq datasource, so if someone can guide me, I’ll happily provide you with the exact query.
The tables layout is as follows:
PhotoAlbum table:
Id int (primary key identity)
Name nvarchar(100)
Picture table:
Id int (primary key identity)
Description nvarchar(300)
ToolTip nvarchar(50)
ImageUrl nvarchar(200)
PhotoAlbumId int
Basically I’ve got 1 page where a user enters a name for a photoalbum which then puts the photoalbumid from the photoalbum table in the query string.
The next page allows the user to fill out the details for the pictures table (except the id and photoalbumid).
The photoalbumid is pulled from the query string and is supposed to be populating the picture table.
I can see the photoalbum table getting populated, but the picture table is empty, the error I’m getting doesn’t explain which field is the problem.
wwb_99 I’m not sure if I’m interpretting your code correctly or not, but it seems as if you think that the addressId is the foreignkey in the users table.
I’ve modified my code to the following:
Dim myGoogleContext As New GoogleMapsExperimentDataContext
Dim usr As New user With
{.username = "johnny"
}
' Add the new object to the users collection.
myGoogleContext.users.InsertOnSubmit(usr)
Dim addr As New address With
{.test = "mynewaddress"
}
' Add the new object to the address collection.
myGoogleContext.addresses.InsertOnSubmit(addr)
' Submit the change to the database.
myGoogleContext.SubmitChanges()
However I’m getting the following error:
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_addresses_users”. The conflict occurred in database “googlemapstest”, table “dbo.users”, column ‘userid’.
Does this mean that I’ve set up my database incorrectly, or is it that I’m not referencing the userid in the userid field of the addresses table?
I’ve tried putting in .userid=usr.userid as below, but that doesn’t fix the error:
Dim addr As New address With
{userid=usr.userid,
.test = “mynewaddress”
}
I’m pretty sure that it’s the way I’m trying to grab the userid to insert into the addresses table, that’s where I’m a little stuck as the obvious way I’ve described above doesn’t seem to work :(.
Well, LINQ to SQL is a much different beast than straight SQL–its pretty squarely in the ORM world while being disturbingly close to the database at the same time. Or, you might want to just start with the Entity Framework v4.
Anyhow, Linq2Sql should have generated a reference to the User object for your Address object. You should set that property to equal the user. Something like:
[Examples in C# because I forgot VB by now]
Address a = new Address { AddressLine1 = "1234 Some St.", City = "Watertown", State ="NY", Zip = "00534" };
User u = new User { Name = "Fred", Address = a }
using (MyDataContext dc = new MyDataContext(connStr))
{
dc.Users.InsertOnSubmit(u);
dc.SubmitChanges();
}
One more protip–don’t catch exceptions like that. If the DataContext failed to submit, submitting it again in an unknown [or ‘Exceptional’] state isn’t going to help. I actually generally advise not catching exceptions at all, better to let them bubble
Okay, new scenario.
I’m much closer to getting this right now.
I’m working on a different database but I’m still following the main line of enquiry.
I have 2 tables “users” and “addresses”.
the user table just has a userid and a username, where as the address table has an addressId and the userId as a foriegn key from the users table (as well as a field I’ve just called test for now).
Basically, I have discovered that the database wont just grab the userid from the users table and populate the addresses table with it as the userId, I gather I have to do that myself?
So, I’ve got as far as inserting the username, but am struggling to retrieve the userId in order to populate the addresses table with it, in a single query.
My LINQ to SQL is as follows:
Dim myGoogleContext As New GoogleMapsExperimentDataContext
Dim usr As New user With
{.username = "jim"
}
' Add the new object to the users collection.
myGoogleContext.users.InsertOnSubmit(usr)
Dim addr As New address With
{
.test = "myaddress"
}
' Add the new object to the address collection.
myGoogleContext.addresses.InsertOnSubmit(addr)
' Submit the change to the database.
Try
myGoogleContext.SubmitChanges()
Catch ex As Exception
myGoogleContext.SubmitChanges()
End Try
I’m trying to put in the userId just above “.test = “myaddress”” but I can’t figure out how.
Once I’ve got that sorted, I should be able to get way ahead on my own, so if anyone can help me I’d greatly appreciate it (Sooo close).
