master/detail foreign keys during insert

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

Guest

when I insert a new record into a master record - I need to update the new child record with the autonumbered ID key from the master recor

Is there a simple way to do this? I insert the record - then try to find it - then read the key and that seems silly

thx for your assistance
 
Set up a DataRelation on this field...
http://www.knowdotnet.com/articles/datarelation.html Then when you set the
parent value the child values will be updated accordingly. Typically, you'd
set the autoincrement value to -1 and define a datarelation between the
fields. When the update is submitted, the DB will assign a value to the
negative number since autoincrement fields don't allow negatives. You can
then refresh the dataset with another select statement or specify this
option in the DataAdapter Configuration wizard...it's on by default and all
it does is append an additional Select statement on the end of your inserts.

When the value is updated from the DB, the parent record will be corrected.
then if a relation is set properly, the child values will be set too...now
you can submit the updaate on the child table.

HTH,

Bill
mekim said:
when I insert a new record into a master record - I need to update the new
Click to expand...
child record with the autonumbered ID key from the master record
Is there a simple way to do this? I insert the record - then try to find
Click to expand...
it - then read the key and that seems silly -
 
I just posted this...this seems to wor

Any suggestions

Public Sub AddingParentChildRecords(
Dim dbConn As New OleDbConnection(constConnectionString
dbConn.Open(

Dim dbSet As New DataSe
Dim dbParentAdapt As OleDbDataAdapte
dbParentAdapt = New OleDbDataAdapter("SELECT * FROM TableParent", dbConn

Dim dbParentCB As New OleDbCommandBuilder(dbParentAdapt
dbParentAdapt.FillSchema(dbSet, SchemaType.Source

AddHandler dbParentAdapt.RowUpdated, AddressOf dbParentAdapt_OnRowUpdat

Dim dbTable As DataTable = dbSet.Tables("Table"
dbTable.TableName = "TableParent

Dim dbChildAdapt As New OleDbDataAdapter("SELECT * FROM TableChild", dbConn
Dim dbChildCB As New OleDbCommandBuilder(dbChildAdapt
dbChildAdapt.FillSchema(dbSet, SchemaType.Source

dbTable = dbSet.Tables("Table"
dbTable.TableName = "TableChild

Dim dr As New DataRelation("ParentChild",
dbSet.Tables("TableParent").Columns("ParentId"),
dbSet.Tables("TableChild").Columns("ParentId")

dbSet.Relations.Add(dr

Dim oParentRow As DataRow = dbSet.Tables("TableParent").NewRow(
oParentRow("ParentText") = Now().ToStrin
dbSet.Tables("TableParent").Rows.Add(oParentRow

Dim oChildRow As DataRow = dbSet.Tables("TableChild").NewRow(
oChildRow("ChildText") = Now().Ticks.ToStrin

oChildRow.SetParentRow(oParentRow
dbSet.Tables("TableChild").Rows.Add(oChildRow

dbParentAdapt.Update(dbSet, "TableParent"
dbChildAdapt.Update(dbSet, "TableChild"

dbConn.Close(
End Su
 
Back
Top