Click here to Skip to main content
15,886,519 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
VB
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.

*I use an Access database.(.mdb)

Thanks in advance.
Posted
Updated 5-Oct-12 4:07am
v2

If your SELECT statement has a JOIN in it, the DataAdapter can not be used to update, delete or insert on the tables involved.

You have to update the two tables sperately using two seperate queries.
 
Share this answer
 
Comments
fjdiewornncalwe 5-Oct-12 10:09am    
+5. Because it's the right answer...
Thanks Dave, but I know that. It is the codes for those queries that I'm looking for. Can you help?
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900