Save data of 2 SqlDataAdapters Sequentially

Question

I am working on some project ( VB.Net & SQL Server ) where I have Patient & Appointment scenario.

These are the public declarations:

    Dim DS As New DataSet
    Dim SqlAdap, SqlAdapAppointment As SqlDataAdapter
    Dim BindSrc, BindSrcAppointment As New BindingSource

and I set the binding in the Load event:

    SqlAdap = New SqlDataAdapter(String.Format("select * from {0}",Patient),SqlConMain)
    SqlAdap.Fill(DS,"Patient")
    Dim SqlCmd As New SqlCommandBuilder(SqlAdap)
    SqlAdap.InsertCommand = SqlCmd.GetInsertCommand
    SqlAdap.DeleteCommand = SqlCmd.GetDeleteCommand
    SqlAdap.UpdateCommand = SqlCmd.GetUpdateCommand
    BindSrc.DataSource = DS
    BindSrc.DataMember = "Patient"
    BindNavMain.BindingSource = BindSrc

    SqlAdapAppointment = New SqlDataAdapter("select * from Appointment", SqlConMain)
    SqlAdapAppointment.Fill(DS, "Appointment")
    Dim SqlCmdAppointment As New SqlCommandBuilder(SqlAdapAppointment)
    SqlAdapAppointment.InsertCommand = SqlCmdAppointment.GetInsertCommand
    SqlAdapAppointment.DeleteCommand = SqlCmdAppointment.GetDeleteCommand
    SqlAdapAppointment.UpdateCommand = SqlCmdAppointment.GetUpdateCommand

    Dim Rel As New DataRelation("FK_Patient_Appointment",
     DS.Tables("Patient").Columns("PatientId"),
     DS.Tables("Appointment").Columns("PatientId"), False)

    Rel.Nested = False
    DS.Relations.Add(Rel)
    BindSrcAppointment.DataSource = BindSrc
    BindSrcAppointment.DataMember = "FK_Patient_Appointment"
    BindNavAppointment.BindingSource = BindSrcAppointment

everything works well .. but if I added new patient using its BindingNavigator, then added an appointment for him using the appointment's BindingNavigator

then I tried to save all of it, it will save the patient data successfully but will throw an error after, because it couldn't save the appointment data that its related to the new patient ID

this is my save code:

    BindSrcAppointment.EndEdit()
    BindSrc.EndEdit()

    Dim TblPatient As DataTable = DS.Tables("Patient").GetChanges()

    If TblPatient IsNot Nothing Then
        SqlAdap.Update(TblPatient)
        DS.Tables("Patient").AcceptChanges()
    End If

    Dim TblAppointment As DataTable = DS.Tables("Appointment").GetChanges()

    If TblAppointment IsNot Nothing Then
        SqlAdapAppointment.Update(TblAppointment)
        DS.Tables("Appointment").AcceptChanges()
    End If

It saves successfully of course if I tried to add appointment to some patient who has data already been saved to the DB

but I want to know if there a way to save data of 2 Adapters at once sequentially?

Answer 1

I use SqlDataAdapter.RowUpdated event to 'catch' the identity of the most recently added record, but it seems that there are a lot of different possibilities: http://www.mikesdotnetting.com/article/54/getting-the-identity-of-the-most-recently-added-record

So you have to update Patient table, retrieve the most recently added record's autoincrement identity value and then update Appointment table.