在Visual Basic 2010中更新JOINed两个表吗? [英] Update JOIN'ed two tables in Visual Basic 2010?

问题描述

朋友们，
我正在开发人力资源应用程序.到目前为止，我有两个表，一个是tblDetails，另一个是学术.我已经创建了一个关系(从ID到AcademicID)，我已经能够通过此SELECT语句将数据提取到表单中:

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")

但是此方法不起作用，出现以下错误消息:多个基表不支持动态SQL生成."
Microsoft进一步说:"..这通常在数据库表没有主键列或SELECT命令使用JOINS时发生."
所以我认为我需要自定义代码来执行INSERT/UPDATE，因为我使用的是JOIN.我用谷歌搜索代码无济于事.有谁可以帮助我吗?使用JOIN的UPDATE/INSERT代码.

*我使用Access数据库.(.mdb)

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.

推荐答案

如果您的SELECT语句中有一个JOIN，则不能使用DataAdapter来更新，删除或插入所涉及的表.

您必须使用两个单独的查询分别更新两个表.

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.

感谢Dave，但我知道.这是我正在寻找的那些查询的代码.你能帮忙吗?

Thanks Dave, but I know that. It is the codes for those queries that I''m looking for. Can you help?

这篇关于在Visual Basic 2010中更新JOINed两个表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！