need help cascading identity key to child tables

[purplehaze1]

I need to know how to cascade identity key from parent table down to child tables. I have been doing one way but most articles I've read suggest using datarelation to cascade identity key. However, in my code, I save one table a time (see below) and so I am confused about how to create relation b/w parent and child tables in this sub routine and cascade identity key.

Below, customer (parent) has patients(child).
I save customer table and get identity key (m_custID). I want to cascade this
key to child table(patient). Currently, I do it the following way in the client program. It works, but I think I should use data relation as correct way to do it.
I'd appreciate your suggestions. Thanks.

'client program

Dim oCustomer as new customer
Dim oPatient as new patient

if oCustomer.SaveCustomer() then
oPatientID.customerID = oCustomer.CustomerID
End if


' Customer class

Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
' Create another Command to get IDENTITY Value
Dim newID As Integer = 0
Dim myCommand As OleDbCommand
Dim strSQL As String

myCommand = New OleDbCommand("SELECT @@IDENTITY", myConnection, trans)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
newID = myCommand.ExecuteScalar()
e.Row.AcceptChanges()
m_custID = newID
End If

End Sub

Public Function SaveCustomer(Optional ByVal bolStartTran As Boolean = True) As Boolean
Dim strsql As String, bolInTran As Boolean
Dim myCommand As OleDbCommand
Dim dr As DataRow, i As Integer

Try
If Not IsDirty Then GoTo Skip_Save

If bolStartTran Then
myConnection.Open()
trans = myConnection.BeginTransaction(IsolationLevel.RepeatableRead)
bolInTran = True
End If

strsql = "SELECT * FROM t_customer WHERE cust_id=" & m_custID
myCommand = New OleDbCommand(strsql, myConnection, trans)
Dim da As New OleDbDataAdapter(myCommand)
Dim cb As New OleDbCommandBuilder(da)
Dim ds As New DataSet()

da.Fill(ds, "Customer")

If ds.Tables(0).Rows.Count = 0 Then
dr = ds.Tables(0).NewRow()
IsNew = True
GoTo SaveRecord
Else
dr = ds.Tables(0).Rows(0)
End If

SaveRecord:
dr("cust_typ_id") = m_custTypeID
dr("cust_cde") = m_custCode
dr("cust_name") = IIf(m_custName = String.Empty, Nothing, m_custName)


If IsNew Then
ds.Tables(0).Rows.Add(dr)
End If

''Delegate for Handling RowUpdated event
AddHandler da.RowUpdated, AddressOf HandleRowUpdated
da.Update(ds, "Customer")

Skip_Save:
If bolInTran Then
trans.Commit()
bolInTran = False
End If

IsDirty = False
IsNew = False
Return True
Rollback_Save:
If bolInTran Then trans.Rollback()
myConnection.Close()
Catch ex As Exception
trans.Rollback()
myConnection.Close()
Fina

 

[purplehaze1]

Here's the SavePatient routine that uses customer id as foreign key.
Any suggestions how I can create data relation b/w here and cascade
customer id to patient table? Thanks.

'client
opatient.SavePatient()


' Patient class

Public Function SavePatient(Optional ByVal bolStartTran As Boolean = True) As Boolean
Dim strsql As String
Dim i, j, k, m, n As Integer
Dim ds As New DataSet()
Dim dr As DataRow, bolInTran As Boolean


Try
If Not IsDirty Then GoTo Skip_Save

If bolStartTran Then
myConnection.Open()
trans = myConnection.BeginTransaction(IsolationLevel.RepeatableRead)
bolInTran = True
End If

strsql = "SELECT * FROM t_recip WHERE recip_id=" & m_recipientID
Dim mycommand As New OleDbCommand(strsql, myConnection, trans)
Dim da As New OleDbDataAdapter(mycommand)
Dim cb As New OleDbCommandBuilder(da)

da.Fill(ds, "Recipient")

If ds.Tables(0).Rows.Count = 0 Then
dr = ds.Tables(0).NewRow()
IsNew = True
GoTo SaveRecord
Else
dr = ds.Tables(0).Rows(0)
End If

If m_IsDeleted Then
dr.Delete()
End If
SaveRecord:
dr("cust_id") = m_custID
dr("gender_id") = CType(m_genderID, Integer)
dr("first_nme") = IIf(m_fName = String.Empty, String.Empty, m_fName)
dr("mi_nme") = IIf(m_Init = String.Empty, String.Empty, m_Init)
dr("last_nme") = IIf(m_lName = String.Empty, String.Empty, m_lName)


If IsNew Then
ds.Tables(0).Rows.Add(dr)
End If


'Delegate for Handling RowUpdated event
AddHandler da.RowUpdated, AddressOf HandleRowUpdated

da.Update(ds, "Recipient")
Skip_Save:


If bolInTran Then
trans.Commit()
bolInTran = False
End If
IsDirty = False
IsNew = False
Return True
Rollback_Save:
If bolInTran Then trans.Rollback()
myConnection.Close()
Catch ex As Exception
trans.Rollback()
myConnection.Close()
MsgBox(ex.Message)
Finally
myConnection.Close()
End Try

End Function