时间戳

首页 > 解决方案 > 如何从 datagridview 更新连接查询

vb.net - 如何从 datagridview 更新连接查询

问题描述

我有一些示例代码

在这段代码中，我在查询中添加了一列（A.TUTAR+2）

但是当我更改datagridview时我无法更新访问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```

标签： vb.netdatagridview

解决方案

您不能使用命令生成器。您需要自己创建操作命令。如果您只想更新一个表，那么您只需要一个数据适配器，但如果您想同时更新两个表，因为 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为FalseUpdateAcceptChangesDataTable

推荐阅读