I am working on some project ( VB.Net & SQL Server ) where I have
Patient & Appointment scenario like this:
This is 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 = TableName
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 with 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 save successfully of course if I tried to add appointment to some patient who has already data saved to the DB
but I want to ask if there some way where I can save data of 2 Adapters at once sequentially