Trying to do a hierarchical update results in an error "A foreign key value cannot be inserted"

Question

I'm a bit of a noob with DAO and SQL Server and I'm running into a problem when I'm trying to insert values into two tables that have a relation. The table Photos has a gpsId field which has a foreign key relation with the id field of the GPSLocations table. I want to create a new Photos entry linked to a new GPSLocation, so the code looks something like this:

gpsRow = dataset.GPSLocations.AddGPSLocationsRow("0.0N", "3.2W");
dataset.Photos.AddPhotosRow(@"c:\path\file.jpg", gpsRow);
tableAdapterManager.UpdateAll(dataset); 

However this results in the following error:

A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = photoToGps ]

I'm using SQL Server CE. Is my understanding correct that the TableAdapterManager should be handling this hierarchical update? I just dragged these tables onto the XSD view and relied on its automatic creation of the wrapper classes. Do I need to change anything about the relation (eg to make it a Foreign Key constraint)? I've noticed that under some circumstances the gps id is positive and sometimes negative, is that relevant?

EDIT: I've also ensured that the update property is set to CASCADE, which results in the same error. Hierarchical updates are set to true and there is a foreign key constraint between the two tables in the designer.

Answer 1

I've managed to track down the source of this problem, which boils down to a limitation of SQL Server CE compared with the full SQL Server. It turns out the major hint that something wasn't right was because the ids were negative. The ids are negative in the DataSet before the row is inserted into the database, at which point it gets resolved to a positive index. The fact that it wasn't becoming a positive index happened because the TableAdapterManager normally does a batch statement of INSERT followed by a SELECT to update the id. However, SQL Server CE doesn't support batch statements, so this requires extra code to be written so that we simulate the SELECT step by responding to the RowUpdated event. This MSDN article explains the steps.