Hi,
I have 2 tables in my databasePrescriptionHeader and PrescriptionDetails.
My PrescriptionHeader table has the following fields:
PrescriptionID -identity field
PatientID
PatientfName
Patientlname
PrescriptionDetails table has the following fields:
PrescriptionDetailID -identity
PrescriptionID -from PrescriptionHeader table
MedicineDosage
The functionInsertPrescription inserts values into the tablePrescriptionHeader. I want the same function to then insert the value ofMedicineDosage intoPrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?
Thanks
Function InsertPrescription(ByVal PatientIDAsString, _
ByVal PatientFnameAsString, _
ByVal PatientlnameAsString, ByValMedicineDosage as String)
Dim DBAdapterAs SqlDataAdapter
Dim DBDataSetAs DataSet
Dim SQLStringAsString
Dim DBCommandBuilderAs SqlCommandBuilder
SQLString ="SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"
DBAdapter =New SqlDataAdapter(SQLString, DBConnection)
DBDataSet =New DataSet
DBAdapter.Fill(DBDataSet)
Dim AddedRowAs DataRow = DBDataSet.Tables(0).NewRow()
AddedRow("PatientID") = PatientID
AddedRow("PatientfName") = PatientFname
AddedRow("Patientlname") = Patientlname
DBDataSet.Tables(0).Rows.Add(AddedRow)
DBCommandBuilder =New SqlCommandBuilder(DBAdapter)
DBAdapter.Update(DBDataSet)
EndFunction
The following article is very helpful in your case
Inserting relational data using DataSet and DataAdapter
HTH
Regards
