Update JOINed tables in Visual Basic 2010?

[savedlema]

Hi friends,
I'm developing an application for HR. I have two tables so far.One is tblDetails and the other is Academic. I have created a relationship (ID to AcademicID), I have been able to pull data to a form by this SELECT statement:

 sqlSearch = "SELECT tblDetails.ID, tblDetails.FirstName, tblDetails.Surname, Academic.AcademicID, Academic.FirstLevel, Academic.FirstAward, Academic.PlaceFirst, Academic.YearFirst " _
                + "FROM tblDetails INNER JOIN Academic ON " _
                + "tblDetails.ID = Academic.AcademicID " _
                + "WHERE tblDetails.ID = '" & Trim(Me.txtSearch.Text) & "'"

    da = New OleDb.OleDbDataAdapter(sqlSearch, con)
    da.Fill(ds, "HumanResource")
    con.Close()

After pulling the data, I want to update this data (I think its the same as adding new data) and I tried to use this code:

       Dim updateCommand As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("HumanResource").Rows(inc).Item("FirstLevel") = cmbFirstLevel.Text
        ds.Tables("HumanResource").Rows(inc).Item("FirstAward") = txtFirstAward.Text
        ds.Tables("HumanResource").Rows(inc).Item("PlaceFirst") = txtPlaceFirst.Text
        ds.Tables("HumanResource").Rows(inc).Item("YearFirst") = txtYearFirst.Text

        da.Update(ds, "HumanResource")

But this method does not work, with the following error msg "Dynamic SQL generation is not supported against multiple base tables."
Microsoft further says that "..This often occurs when the database table does not have a primary key column, or the SELECT command uses JOINS."
So I think I need custom codes for INSERT/UPDATE to work here, since I use JOIN. I have googled for the codes to no avail. Can someone help me please? The codes for UPDATE/INSERT where JOIN is used.

Thanks in advance.

[G_Waddell]

Hi
Yes because the data is not coming from an individual table you will need to run the DELETE / UPDATE / INSERT queries as a seperate query for each table.

sub UpdateHumanResource()
dim cmd as OleDbCommand
dim sSQL as string
....
con.open

sSQL ="UPDATE Academic SET FirstLevel ='" &cmbFirstLevel.Text &"', FirstAward ='" &txtFirstAward.Text &"' etc..."

cmd = new OleDBCommand
With cmd
     .CommandType = CommandType.Text
     .CommandText = sSQL
     .Connection = con
End With
cmd.ExecuteNonQuery

sSQL ="UPDATE tblDetails ...."
cmd = new OleDBCommand
With cmd
     .CommandType = CommandType.Text
     .CommandText = sSQL
     .Connection = con
End With
cmd.ExecuteNonQuery
end sub

[savedlema]

Hi Waddell, your post has been very helpfull to me. Thank you very much for responding, and thank you for taking your time to type all that code for me.
Thank you all.