AddNew on empty dataset with relations sets wrong child key.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dataset with two related tables. When I add a new parent/child pair
when there are rows in the dataset the Parent Key and Child Key are set
appropriately in the database. However when I do the same thing but there
are no rows in the dataset, the Parent Key gets set to a new valid Key but
the child row key doesn't get set to the new value. This results in a
Foreign Key constraint error being returned.

If I update the database with just the parent first, I can then add children
to it.

Any solutions or ways around this?

Thanks,
Carl
 
You can set the EnforceConstraints property on the DataSet to false.
Then the child rows can be added before the parent, if that is your
bent. The only trick to avoiding future problems is to set
EnforceConstraints back to true when all of the updates are finished.
This will cause all of the constraints to be evaluated and any
exceptions thrown at that point.

Bruce Johnson [.NET MVP]
http://www.objectsharp.com/blogs/bruce
 
Bruce,

Thank you for the response. The problem isn't one of order it has to do
with the DataRelation's (or some other object's) ability to set a temporary
Key when a Parent row is created, use that temporary key as the Foreign Key
in the Child row, replace the Parent Key with a permanent key when added to
the database (SQL Server in this case), and finally use the permanent Key as
the permanent Foreign Key in the child.

This process works fine for me as long as there is at least one row in the
parent dataset even if the child dataset is empty. It fails to work when the
parent dataset is empty. The Parent's Key is set to the new permanent Key
and the row stores to the database fine. The Child row fails with a Foreign
Key constraint error because its temporary Key is not updated to the new,
permanent Parent Key.

Carl.
 
Hi All,

In rereading my description I realized my terminology wasn't quite accruate.
Both tables are in a single dataset linked via appropriate datarelations.
Hierarchy looks like this:

CustomerTable
CustomerPrescriptionRelation
PrescriptionTable
PrescriptionPrescriptionDetailRelation
PrescriptionDetailTable

It's the PrescriptionPrescriptionDetailRelation that doesn't seem to process
the permanent Key from PrescriptionTable when the Prescription table is empty
at the time of adding the Prescription row and PrescriptionDetail row.

Carl
 
Carl,

Given you clarification, let me take another stab at it. First of all,
the propagation of primary keys from parent to child is dependent on a
couple of factors.

First of all, the relation needs to be defined to cascade changes from
parent to child. This is what allows a change to the parent row's key
to cause the child row's key to be set.

Second, and the one that I suspect is causing you grief, is that the
Insert statement on the DataAdapter that your using needs to include a
SELECT statement. This allows the contents of the particular row to be
refreshed with the key that was generated by the INSERT statement.
Which, because of the previously mentioned DataRelation setting, will
then get pushed down to the child row.

Hope that does it for you.

Bruce Johnson [.NET MVP]
http://www.objectsharp.com/blogs/bruce
 
Hi Bruce,

I just tried the cascade at the typed dataset level but it had no affect.
Is this where it needs to be done or do I need to set cascade update at the
SQL Server table definition level?

Thanks for the additional information. Could you add a little more detail
on the Select statement for the Insert? Which insert needs the select
statement? The parent or the child? Shat is the select statement selecting?
Could you give a simple example?

Also, do I need to do an AcceptChanges at the parent level before processing
the child?

Thanks,
Carl
 
Ok, here we go with the next layer of the onion.

I've setup the sqlcommands per the recommendations and am now selecting back
the @@Identity column after the Insert. The Parent row gets added but I get
an exception that the related child FK is read-only.

(Good news is that at least my code is trying to do what is needed <g>)

Any suggestions?

Thanks,
Carl
 
That surprises me, since the update to the child FKs should be done
through the cascade when the parent PK is updated.

Just to make sure, the reselect is done as part of the InsertCommand on
the Parent's DataAdapter, right?

INSERT INTO Parent (F1, F2) VALUES (@F1, @F2);
SELECT Id, F1, F2 FROM Parent WHERE (Id =
SCOPE_IDENTITY())

Bruce Johnson [.NET MVP]
http://www.objectsharp.com/blogs/bruce
 
Hi Bruce,

Me too, it seems like the child row is locked or something.

Yes, I'm doing the select on the parent's insert command. I'm using
@@Identity at this point but plan on changing it to SCOPE_IDENTITY() once I
get the basic functionality working. I also have cascade set on for the
relationship (delete, Update, and Accept/Reject) on the typed dataset - but
not in the database.

Here is the .update code I am using:
If Not inDS.GetChanges(DataRowState.Added) Is Nothing Then
'don't allow customer additions here, reject any that come
in If Not inDS.Customer.GetChanges(DataRowState.Added) Is
Nothing Then

inDS.Customer.GetChanges(DataRowState.Added).RejectChanges()
End If

Me.sdaCustomerPrescription.Update(inDS.GetChanges(DataRowState.Added),
"Prescription")

Me.sdaCustomerPrescriptionDtl.Update(inDS.GetChanges(DataRowState.Added),
"PrescriptionDetail")
End If

I've pasted my select command and insert command code below which is based
on the results of the Dataadapter Configuration Wizard.

Note that my lowest level table, PrescriptionDetail has a two column Primary
Key: PrescriptionFK and PrescriptionDetailType. I can change that to have
an identity column as the primary key and then set a uniqueness constraint on
the two columns if you think that is source of the problem.

Thanks,
Carl

Code:

For Customer:
With CustomerSDA.SelectCommand
.CommandText = "SELECT CustomerKey, LeftPD, RightPD,
TotalNearPD, TotalDistPD FROM Customer WHERE" & _
" (CustomerKey = @InputCustomerKey)"
.Parameters.Add("@InputCustomerKey", System.Data.SqlDbType.Int,
4).Value = strCustomerKey
.Connection = SqlConnection1
End With

For Prescription:
With PrescritptionSDA.SelectCommand
.CommandText = _
"SELECT PrescriptionKey, CustomerFK, IssuedDate,
ExpirationDate, PrescriberFK, Special_Instr " & _
"FROM Prescription " & _
"WHERE (CustomerFK = @InputCustomerKey)"
.Parameters.Add("@InputCustomerKey", System.Data.SqlDbType.Int,
4).Value = strCustomerKey
.Connection = SqlConnection1
End With
'
'SqlInsertCommand1
'
With PrescritptionSDA.InsertCommand
.CommandText = "INSERT INTO Prescription(CustomerFK, IssuedDate,
ExpirationDate, PrescriberFK, Sp" & _
"ecial_Instr) VALUES (@CustomerFK, @IssuedDate, @ExpirationDate,
@PrescriberFK, @" & _
"Special_Instr); SELECT PrescriptionKey, CustomerFK, IssuedDate,
ExpirationDate, " & _
"PrescriberFK, Special_Instr FROM Prescription WHERE
(PrescriptionKey = @@IDENTITY)"
.Connection = SqlConnection1
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CustomerFK", System.Data.SqlDbType.Int,
4, "CustomerFK"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@IssuedDate",
System.Data.SqlDbType.DateTime, 4, "IssuedDate"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ExpirationDate",
System.Data.SqlDbType.DateTime, 4, "ExpirationDate"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PrescriberFK",
System.Data.SqlDbType.Int, 4, "PrescriberFK"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Special_Instr",
System.Data.SqlDbType.NVarChar, 250, "Special_Instr"))
End With

For PrescriptionDetail (Note that the PK here is the PrescriptionFK plus the
PrescriptionDetailType):
With PrescriptionDetailSDA.SelectCommand
.CommandText = "SELECT PrescriptionFK, PrescriptionDetailType,
RDSpherical, RNSpherical, " & _
"RCylindrical, RAxis, RPrism1, RBase1, RPrism2, RBase2,
LDSpherical, LNSpherical, " & _
"LCylindrical, LAxis, LPrism1, LBase1, LPrism2, LBase2 " & _
"FROM PrescriptionDetail " & _
"WHERE (PrescriptionFK IN " & _
"(SELECT PrescriptionKey FROM Prescription WHERE
CustomerFK = @InputCustomerKey))"
.Parameters.Add("@InputCustomerKey", System.Data.SqlDbType.Int,
4).Value = strCustomerKey
.Connection = SqlConnection1
End With
'
'SqlInsertCommand1
'
With PrescriptionDetailSDA.InsertCommand
.CommandText = "INSERT INTO PrescriptionDetail(PrescriptionFK,
PrescriptionDetailType, RDSpherica" & _
"l, RNSpherical, RCylindrical, RAxis, RPrism1, RBase1, RPrism2,
RBase2, LDSpheric" & _
"al, LNSpherical, LCylindrical, LAxis, LPrism1, LBase1, LPrism2,
LBase2) VALUES (" & _
"@PrescriptionFK, @PrescriptionDetailType, @RDSpherical,
@RNSpherical, @RCylindri" & _
"cal, @RAxis, @RPrism1, @RBase1, @RPrism2, @RBase2,
@LDSpherical, @LNSpherical, @" & _
"LCylindrical, @LAxis, @LPrism1, @LBase1, @LPrism2, @LBase2);
SELECT Prescription" & _
"FK, PrescriptionDetailType, RDSpherical, RNSpherical,
RCylindrical, RAxis, RPris" & _
"m1, RBase1, RPrism2, RBase2, LDSpherical, LNSpherical,
LCylindrical, LAxis, LPri" & _
"sm1, LBase1, LPrism2, LBase2 FROM PrescriptionDetail WHERE
(PrescriptionDetailTy" & _
"pe = @PrescriptionDetailType) AND (PrescriptionFK =
@PrescriptionFK)"
.Connection = SqlConnection1
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PrescriptionFK",
System.Data.SqlDbType.Int, 4, "PrescriptionFK"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PrescriptionDetailType",
System.Data.SqlDbType.NVarChar, 15, "PrescriptionDetailType"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RDSpherical",
System.Data.SqlDbType.NVarChar, 10, "RDSpherical"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RNSpherical",
System.Data.SqlDbType.NVarChar, 10, "RNSpherical"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RCylindrical",
System.Data.SqlDbType.NVarChar, 10, "RCylindrical"))
.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RAxis",
System.Data.SqlDbType.NVarChar, 10, "RAxis"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RPrism1",
System.Data.SqlDbType.NVarChar, 2, "RPrism1"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RBase1", System.Data.SqlDbType.NVarChar,
2, "RBase1"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RPrism2",
System.Data.SqlDbType.NVarChar, 2, "RPrism2"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RBase2", System.Data.SqlDbType.NVarChar,
2, "RBase2"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LDSpherical",
System.Data.SqlDbType.NVarChar, 10, "LDSpherical"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LNSpherical",
System.Data.SqlDbType.NVarChar, 10, "LNSpherical"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LCylindrical",
System.Data.SqlDbType.NVarChar, 10, "LCylindrical"))
.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LAxis",
System.Data.SqlDbType.NVarChar, 10, "LAxis"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LPrism1",
System.Data.SqlDbType.NVarChar, 2, "LPrism1"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LBase1", System.Data.SqlDbType.NVarChar,
2, "LBase1"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LPrism2",
System.Data.SqlDbType.NVarChar, 2, "LPrism2"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LBase2", System.Data.SqlDbType.NVarChar,
2, "LBase2"))
End With



Bruce Johnson said:
That surprises me, since the update to the child FKs should be done
through the cascade when the parent PK is updated.

Just to make sure, the reselect is done as part of the InsertCommand on
the Parent's DataAdapter, right?

INSERT INTO Parent (F1, F2) VALUES (@F1, @F2);
SELECT Id, F1, F2 FROM Parent WHERE (Id =
SCOPE_IDENTITY())

Bruce Johnson [.NET MVP]
http://www.objectsharp.com/blogs/bruce
Click to expand...
 
Hi Bruce,

I'm at my wits end on this problem. this is what i've done:
1. Added the Select Statement to the InsertCommand as recommended.
Result was that the child's FK was reporting that it was read-only.
2. Changed it to read-only = false
Result - FK constraint error again.
3. Added a separate PK field to the child instead of using the child's FK
and record type as the PK; added a unique constraint on the FK and record
type fields.
Result - FK constraint error still.
4. Checked UpdatedRowSource property - it is set to Both.
5. Set Autoincrement Seed and Step to -1 on both parent and child.
6. Set MissingSchemaAction.AddWithKey.
7. Set parent PK column to read-only = false.

Still getting the FK constraint error. Now, as I monitor the Parent PK
value the dataset does not seem to be updating with the new values although I
can see the new parent row in the database with the correct PK value.

Here is the InsertCommand text I am using. Maybe you can see something that
I can't.

SqlDataAdapter1.InsertCommand = New SqlCommand
With SqlDataAdapter1.InsertCommand
.CommandText = _
"INSERT INTO Prescription(CustomerFK, IssuedDate,
ExpirationDate, PrescriberFK, " & _
"Special_Instr) " & _
"VALUES (@CustomerFK, @IssuedDate, @ExpirationDate,
@PrescriberFK, @Special_Instr); " & _
"SELECT PrescriptionKey, CustomerFK, IssuedDate,
ExpirationDate, " & _
"PrescriberFK, Special_Instr FROM Prescription " & _
"WHERE (PrescriptionKey = SCOPE_IDENTITY())"
.Connection = SqlConnection1
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CustomerFK", System.Data.SqlDbType.Int,
4, "CustomerFK"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@IssuedDate",
System.Data.SqlDbType.DateTime, 4, "IssuedDate"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ExpirationDate",
System.Data.SqlDbType.DateTime, 4, "ExpirationDate"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PrescriberFK",
System.Data.SqlDbType.Int, 4, "PrescriberFK"))
.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Special_Instr",
System.Data.SqlDbType.NVarChar, 250, "Special_Instr"))
End With

As I said, I don't know where to go from here. I feel like I've checked out
every path available at this point.

Hopefully you can see/find the problem,
Carl

Bruce Johnson said:
That surprises me, since the update to the child FKs should be done
through the cascade when the parent PK is updated.

Just to make sure, the reselect is done as part of the InsertCommand on
the Parent's DataAdapter, right?

INSERT INTO Parent (F1, F2) VALUES (@F1, @F2);
SELECT Id, F1, F2 FROM Parent WHERE (Id =
SCOPE_IDENTITY())

Bruce Johnson [.NET MVP]
http://www.objectsharp.com/blogs/bruce
Click to expand...
 
Hi Bruce,

I've nailed it! Always darkest before the dawn as they say.

The problem was with the way I was selecting my rows for the adapter update.
I was using:
Me.sdaCustomerPrescription.Update(inDS.GetChange (DataRowState.Added),
"Prescription")

Which accroding to the docmentation GetChange "makes a copy of the dataset".
I narrowed it donw to here by looking at the data coming back withthe
RowUpdated event and found that it had the correct value. When I changed the
update to:
Me.sdaCustomerPrescription.Update(inDS.Prescription.Select(Nothing,
Nothing, DataViewRowState.Added))

the select statement merely filtered the rows and posted the updated values
back to the original dataset as needed.

Carl
 
Back
Top