如何从datagridview更新联接的查询

Question

我有一些示例代码

在这些代码中，我在查询中添加了一列(a.TUTAR+2(

但当我更改数据网格视图时，我无法更新访问mdb

注意：我可以组合多个表并添加更多列

Imports System.Data.OleDb
Public Class Form1
Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles  
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|MYDB.mdb")  ' Use relative path to database file
Dim cmd As OleDbCommand = New OleDbCommand("SELECT  A.*,A.TUTAR+2  FROM CARTH001 A WHERE KAYITNO = 1", con)
con.Open()
Dim dt = New DataTable()
myDA = New OleDbDataAdapter(cmd)

Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "CARTM001")
DataGridView1.DataSource = myDataSet.Tables("CARTM001").DefaultView
con.Close()
con = Nothing
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.myDA.Update(Me.myDataSet.Tables("CARTM001"))
Me.myDataSet.Tables("CARTM001").AcceptChanges()
Me.myDA.Fill(Me.myDataSet.Tables("CARTM001"))
End Sub
End Class```

Answer 1

您不能使用命令生成器。您需要自己创建动作命令。如果只想更新一个表，那么只需要一个数据适配器，但如果要更新两个表，则由于Access每个命令只支持一条SQL语句，因此需要两个数据适配器。

单个数据适配器可能如下所示：

Dim updateCommand As New OleDbCommand("UPDATE MyTable SET MyColumn = @MyColumn WHERE Id = @Id", myConnection)
updateCommand.Parameters.Add("@MyColumn", OleDbType.VarChar, 50, "MyColumn")
updateCommand.Parameters.Add("@Id", OleDbType.Integer, 0, "Id")
myDataAdapter.UpdateCommand = updateCommand
myDataAdapter.Update(myDataTable)

多个数据适配器可能如下所示：

Dim firstUpdateCommand As New OleDbCommand("UPDATE FirstTable SET FirstColumn = @FirstColumn WHERE FirstId = @FirstId", myConnection)
firstUpdateCommand.Parameters.Add("@FirstColumn", OleDbType.VarChar, 50, "FirstColumn")
firstUpdateCommand.Parameters.Add("@FirstId", OleDbType.Integer, 0, "FirstId")
firstDataAdapter.UpdateCommand = firstUpdateCommand
firstDataAdapter.AcceptChangesOnUpdate = False
firstDataAdapter.Update(myDataTable)
Dim secondUpdateCommand As New OleDbCommand("UPDATE SecondTable SET SecondColumn = @SecondColumn WHERE SecondId = @SecondId", myConnection)
firstUpdateCommand.Parameters.Add("@SecondColumn", OleDbType.VarChar, 50, "SecondColumn")
firstUpdateCommand.Parameters.Add("@SecondId", OleDbType.Integer, 0, "SecondId")
secondDataAdapter.UpdateCommand = secondUpdateCommand
secondDataAdapter.Update(myDataTable)

请注意，必须在第一个数据适配器上将AcceptChangesOnUpdate设置为False，否则第一个Update调用将隐式调用DataTable上的AcceptChanges，第二个数据适配器将不会保存任何更改。