oledb 中的适配器更新语句

Question

我有一个表单，其中包含检索到的数据表单ms访问数据库。使用适配器更新语句进行更新时，它显示语法错误。

以下是表单加载事件和更新按钮单击事件中的连接代码。

你能告诉我这有什么问题吗？

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:DESKTOPVBATESTINGADPE ProjectProject TablesADPE_Table.accdb"
inc = 0
Dim Con As New OleDbConnection()
Dim Cmd As New OleDbCommand
Dim SQL As String = "SELECT * FROM Heidelberg"
Con.ConnectionString = strConn
Try
Con.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Cmd = New OleDbCommand(SQL, Con)
Adapter.SelectCommand = New OleDbCommand(SQL, Con)
ds = New DataSet
Adapter.Fill(ds, "testing")
MaxRows = ds.Tables("testing").Rows.Count
Label1.Text = "Total Records :" & MaxRows
NavigateRecords()
End Sub

Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
Dim cb As New OleDb.OleDbCommandBuilder(Adapter)
ds.Tables("testing").Rows(inc).Item(5) = TextBox1.Text
ds.Tables("testing").Rows(inc).Item(2) = TextBox2.Text
ds.Tables("testing").Rows(inc).Item(3) = TextBox3.Text
ds.Tables("testing").Rows(inc).Item(4) = TextBox4.Text
Try
Adapter.Update(ds, "testing")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Answer 1

你的 Form.Load 中有太多命令。

一

Dim Cmd As New OleDbCommand

二

Cmd = New OleDbCommand(SQL, Con)

三

Adapter.SelectCommand = New OleDbCommand(SQL, Con)

每次使用 New 关键字时，都会创建另一个命令。

如果只处理单个表，则不需要数据集。额外的对象增加了额外的权重，并使代码变得有点复杂。

Private inc As Integer
Private Adapter As New OleDbDataAdapter
Private MaxRows As Integer
Private dat As New DataTable
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:DESKTOPVBATESTINGADPE ProjectProject TablesADPE_Table.accdb"
inc = 0
Using Con As New OleDbConnection(strConn)
Using Cmd As New OleDbCommand("SELECT * FROM Heidelberg;", Con)
Adapter.SelectCommand = Cmd
Try
Adapter.Fill(dat)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Using
End Using
MaxRows = dat.Rows.Count
Label1.Text = "Total Records :" & MaxRows
NavigateRecords()
End Sub
Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
dat.Rows(inc).Item(5) = TextBox1.Text
dat.Rows(inc).Item(2) = TextBox2.Text
dat.Rows(inc).Item(3) = TextBox3.Text
dat.Rows(inc).Item(4) = TextBox4.Text
Dim cb As New OleDbCommandBuilder(Adapter)
'This will take care of any spaces that you have in column names.
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
Try
Adapter.Update(dat)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub