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,

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

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

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