add record in multiple table

[cyberdaemon]

Good day.

I am having a trouble on how to update my reocrds on my 2 tables. 1 table is sample and the other table is tbl_educ. my sample table which has a pk of emp_id while the tbl_educ has a field of emp_id they are related by one-many relationship.

sample table                      tbl_educ
emp_id(pk)                         emp_id
lname                              school_name
fname                              school_address
mname                              level

heres my code.

Imports System.Data
Imports System.Data.OleDb

Public Class education

    Private con As New OleDb.OleDbConnection()
    Private dtAdapter As OleDb.OleDbDataAdapter
    Private cmdBuilder As OleDb.OleDbCommandBuilder
    Private dtTable As New DataTable
    Private rowpos As Integer = 0
    Private Sub education_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ' con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\jesspr\Desktop\mydbase\sample.accdb"
        'con.Open()

        'dtAdapter = New OleDb.OleDbDataAdapter("SELECT sample.emp_ID, tbl_educ.School_Name, tbl_educ.School_Address FROM sample RIGHT JOIN tbl_educ ON sample.emp_ID = tbl_educ.emp_id;", con)

        'cmdBuilder = New OleDb.OleDbCommandBuilder(dtAdapter)


        'dtAdapter.Fill(dtTable)
        
       




    End Sub

    Private Sub srecord()
        If dtTable.Rows.Count = 0 Then


            txtEmp.Text = ""
            txtSName.Text = ""
            txtSAddress.Text = ""
            txtLevel.Text = ""
            'txtCert.Text = ""
            'txtLicense.Text = ""
            Exit Sub
        End If

        txtEmp.Text = dtTable.Rows(rowpos)("emp_ID").ToString()

        txtSName.Text = dtTable.Rows(rowpos)("School_Name").ToString()
        txtSAddress.Text = dtTable.Rows(rowpos)("School_Address").ToString
        txtLevel.Text = dtTable.Rows(rowpos)("Level").ToString
        'txtCert.Text = dtTable.Rows(rowpos)("Certificate").ToString
        'txtLicense.Text = dtTable.Rows(rowpos)("LIcence").ToString
    End Sub



    

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim ds As New DataTable
        Dim DAdapter As OleDb.OleDbDataAdapter
        Dim dr As DataRow = ds.NewRow()
        Dim cmd As New OleDbCommand
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\jesspr\Desktop\mydbase\sample.accdb"

        con.Open()

        'dtAdapter = New OleDb.OleDbDataAdapter("Insert into sample (emp_id)values('" & txtEmp.Text & "' )", con)
        'DAdapter = New OleDb.OleDbDataAdapter("Insert into tbl_educ (school_name,school_address,level)values('" & txtSName.Text & "'," & txtSAddress.Text & "," & txtLevel.Text & " )", con)
        dtAdapter = New OleDb.OleDbDataAdapter("Insert into sample (emp_id)values(@emp_id )", con)
        DAdapter = New OleDb.OleDbDataAdapter("Insert into tbl_educ (school_name,school_address,level)values(@school_name,@school_address,@level)", con)

        cmdBuilder = New OleDb.OleDbCommandBuilder(dtAdapter)
        cmdBuilder = New OleDb.OleDbCommandBuilder(DAdapter)


        'cmd = New OleDb.OleDbCommand(DAdapter)

        dr("emp_id") = txtEmp.Text
        dr("school_name") = txtSName.Text
        dr("school_address") = txtSAddress.Text
        dr("level") = txtLevel.Text

        dtTable.Rows.Add(dr)
        ds.Rows.Add(dr)

        rowpos = dtTable.Rows.Count - 1
        rowpos = ds.Rows.Count - 1
        Me.srecord()

       

    End Sub

   
End Class

the error that i encounter is emp_id is not a category of the table..
how can i add record for my table.

hope can help me...

email me.. <<Email Snipped>>

[the_carpenter]

You database is not set up correctly. What it sounds like you want is to have a one to many relationship between sample and tbl_educ. But you can't without having a unique field in tbl_educ.

Change tbl_educ to include a new field that becomes it's primary index, so call the field tbl_educ_id, make it a long integer (or integer) make the field unique and auto increment (or an identity Column) and make it primary index.

Now make a relationship between emp_id between the two tables and now it will be a one to many setup.

The way you had it, it could only be a one to one relationship.

[cyberdaemon]

@ the_carpenter thanks for reply. it helps me a lot..