okay so i can get all my fields filled in besides the client id foreign key in all my tables. i am using the following code and as you can see it inputs the rest of the data perfectly i just need a a way to give all the tables that are being populated here the same client ID so i can search records later on. i know that id have to add to my insert statement but how?
Protected Sub Button14_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button14.Click
Dim ClientID As String = FormView1.Caption
myConn = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.mdf;Integrated Security=True;User Instance=True")
myCmd4 = myConn.CreateCommand
myCmd6 = myConn.CreateCommand
myCmd7 = myConn.CreateCommand
myCmd8 = myConn.CreateCommand
myCmd9 = myConn.CreateCommand
myCmd10 = myConn.CreateCommand
myCmd11 = myConn.CreateCommand
myCmd11.CommandText = "Insert into [Client_Info] ( GivenName, Surname, DOB, Gender, Suburb, Ethnicity, CountryOfBirth, HomeLanguage, Weight, Height, IndigenousStatus ) Values ( '" + GivenNameTxt.Text() + "', '" +
SurnameTxt.Text() + "','" + Calendar3.SelectedDate + "','" + DropDownListGender.SelectedValue + "','" + SuburbDrop.SelectedValue + "','" + DropDownListEthnicity.SelectedValue + "','" + DropDownListCountry.SelectedValue + "','" +
DropDownListLanguage.SelectedValue + "','" + HeightTxt.Text() + "','" + WeightTxt.Text() + "','" + NativeRadio.SelectedValue + "')"
myCmd10.CommandText = "Insert into [Family_info] (ClientID, PG1GivenName, PG1Surname,PG1Relation,PG1DOB, PG1Occupation, PG1Ethnicity, PG1Address, PG1Suburb, PG1State, PG1Postcode, PG1E_mail, PG1PhoneNum, PG1WorkNum, PG1MobileNum, PG1FaxNum, PG2GivenName, PG2Surname,PG2Relation,PG2DOB, PG2Occupation, PG2Ethnicity,CarersAllowance, SepratedParents, CourtOrder, PermissionNoncustodial, NoOfAdults, NoOfChildren, ECSurname, ECGivenName, ECAddress, ECSuburb, ECPostcode, ECPhoneNum, ECMobileNum) Values ('" + ClientID + "','" + TextBox171.Text() + "','" +
TextBox2.Text() + "','" + DropRelation1.SelectedValue + "','" + Calendar1.SelectedDate + "','" + P1OccupationBox.Text() + "','" + DropDownList1.SelectedValue + "','" + P1AddressBox.Text() + "','" + DropDownListP1Suburb.SelectedValue + "','" + DropDownListP1State.SelectedValue + "','" + P1PostcodeBox.Text() + "','" + P1EmailBox.Text() + "','" + P1PhoneBox.Text() + "','" + P1WorkBox.Text() + "','" + P1MobileBox.Text() + "','" + P1FaxBox.Text() + "','" + TextBox4.Text() + "','" + TextBox5.Text() + "','" + DropRelation2.SelectedValue + "','" + Calendar2.SelectedDate + "','" + TextBox6.Text() + "','" +
DropDownList2.SelectedValue + "','" + RadioButtonList1.SelectedValue + "','" + RadioButtonList2.SelectedValue + "','" + RadioButtonList3.SelectedValue + "','" +
RadioButtonList4.SelectedValue + "','" + TextBox169.Text() + "','" + TextBox170.Text() + "','" +
EmergencySurname.Text() + "','" + EmergencyGiven.Text() + "','" + EmergencyAddress.Text() + "','" + ECSuburbDrop.SelectedValue + "','" + EmergencyPostcode.Text() + "','" + EmergencyDayNumber.Text() + "','" + EmergencyMobileNumber.Text() + "')"
myCmd4.CommandText = "Insert into [Billing_info] (BillingName, BillingAddress, BillingState, BillingSuburb, BillingPostcode, PaymentMethod, CardID, CardHolder, CardNumber, ExpiryDate, CCVNumber) values ('" +
BillingName.Text() + "','" + BillingAddress.Text() + "','" + SuburbBillDrop.SelectedValue + "','" + SuburbBillDrop.SelectedValue + "','" + BillingPostCode.Text() + "','" +
DropDownListPayment.SelectedValue + "','" + CardTypeDrop.SelectedValue + "','" + CardHolderName.Text() + "','" + CreditCardNumber.Text() + "','" + ExpiryCalendar.SelectedDate + "','" + CCVNumber.Text() + "')"
myCmd6.CommandText = "Insert into [Medical_Info] ( Diagnosis, YearOfDiagnosis, DiagnosedBy, AditionalInfo, Paediatrician, MedicareNumber, GP, GPPhone, ReleventMedInfo, Medication) values ('" +
MedicalDiagnosis.Text() + "','" + Calendar4.SelectedDate + "', '" + DropDownListDiagnosedBy.SelectedValue + "','" + MedicalInformation.Text() + "','" + DropDownListPaed.SelectedValue + "','" +
PaediatricianMedicare.Text() + "','" + DropDownListGP.SelectedValue + "','" + GeneralPractitionMedicare.Text() + "','" + TextBox99.Text() + "','" + DropDownListMedication.SelectedValue + "')"
myCmd7.CommandText = "Insert into [Assessment_History] (AssessmentType, AssessmentDate, Diagnosis, Practitioner) values ('" +
TextBox45.Text() + "','" + Calendar5.SelectedDate + "','" + TextBox47.Text() + "','" + PracDrop.SelectedValue + "')"
myCmd8.CommandText = "Insert into [Services] (PsychServiceProvider, PsychOrganisation, PsychContactNumber, BSSirviceProvider, BSOrganisation, BSContactNumber, SPServiceProvider, SPOrganisation, SPContactNumber, OTServiceProvider, OTOrganisation, OTContactNumber, PhysioServiceProvider, PhysioOrganisation, PhysioContactNumber,OtherServiceType, OtherOrganisation, OtherServiceProvider) values ('" +
TextBox112.Text() + "','" + TextBox113.Text() + "','" + TextBox114.Text() + "','" + TextBox116.Text() + "','" + TextBox117.Text() + "','" + TextBox118.Text() + "','" + TextBox120.Text() + "','" + TextBox121.Text() + "','" + TextBox122.Text() + "','" + TextBox124.Text() + "','" + TextBox125.Text() + "','" + TextBox126.Text() + "','" + TextBo2.Text() + "','" + TextBo3.Text() + "','" + TextBo4.Text() + "','" + TextBo10.Text() + "','" + TextBo11.Text() + "','" + TextBo12.Text() + "')"
myCmd9.CommandText = "Insert into [EducationWork] (DCOrganisation, DCContactPerson, DCDateOfPlacement, PSOrganisation, PSContactPerson, PSDateOfPlacement, PGOrganisation, PGContactPerson, PGDateOfPlacement, EIOrganisation, EICContactPerson, EICDateOfPlacement, SchoolOrganisation, SchoolContactPerson, SchoolDateOfPlacement,WPOrganisation, WPContactPerson, WPDateOfPlacement) values ('" +
TextBox111.Text() + "','" + TextBox115.Text() + "','" + Calendar6.SelectedDate + "','" + TextBox123.Text() + "','" + TextBox127.Text() + "','" + Calendar7.SelectedDate + "','" + TextBox129.Text() + "','" + TextBox130.Text() + "','" + Calendar8.SelectedDate + "','" + TextBox132.Text() + "','" + TextBox133.Text() + "','" + Calendar9.SelectedDate + "','" + TextBox135.Text() + "','" + TextBox136.Text() + "','" + Calendar10.SelectedDate + "','" + TextBox138.Text() + "','" + TextBox139.Text() + "','" + Calendar11.SelectedDate + "')"
'Open the connection.
myConn.Open()
myCmd4.ExecuteNonQuery()
myCmd6.ExecuteNonQuery()
myCmd7.ExecuteNonQuery()
myCmd8.ExecuteNonQuery()
myCmd9.ExecuteNonQuery()
myCmd10.ExecuteNonQuery()
myCmd11.ExecuteNonQuery()
myConn.Close()
End Sub
