Click here to Skip to main content
13,148,573 members (66,473 online)

Update JOIN'ed two tables in Visual Basic 2010?

savedlema asked:

Open original thread
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")
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.

*I use an Access database.(.mdb)

Thanks in advance.
Tags: VB, VB.NET, Access


When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).

Advertise | Privacy |
Web04 | 2.8.170924.2 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100