在 VB.NET 中使用 OLEDB 通过 DataGridView 更新 Access 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28985532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating an Access Database via a DataGridView Using OLEDB in VB.NET
提问by CuriousLekgolo
I have linked an Access database to my program. It populates the DataGridView as it is intended to so that part of the program works. However, I'm trying to get the DataGridView to update the Access database file with any changes that are made to it but after countless attempts at trying to fix my code or looking for an alternative solution, I am stumped.
我已将 Access 数据库链接到我的程序。它按照预期填充 DataGridView，以便程序的一部分工作。但是，我试图让 DataGridView 使用对其所做的任何更改来更新 Access 数据库文件，但经过无数次尝试修复我的代码或寻找替代解决方案后，我被难住了。
Can anyone see anything wrong or something I've missed out that would cause the code not to function as desired? Thank you in advance.
任何人都可以看到任何错误或我遗漏的东西会导致代码无法按预期运行吗？先感谢您。
Imports System.Data.OleDb
Public Class frmDatabase
Dim con As New OleDbConnection
Dim ds As New DataSet
Dim dt As New DataTable
Dim da As New OleDbDataAdapter
Private Sub frmDatabase_Load(sender As Object, e As EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\dbSchoolDatabase.mdb"
con.Open()
ds.Tables.Add(dt)
da = New OleDbDataAdapter("Select * from tableStudentDetails", con)
da.Fill(dt)
dgvStudentDetails.DataSource = dt.DefaultView
con.Close()
End Sub
Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\dbSchoolDatabase.mdb"
con.Open()
ds.Tables.Add(dt)
da = New OleDbDataAdapter("Select * from tableStudentDetails", con)
da.Update(dt)
con.Close()
End Sub
End Class
采纳答案by Steve
Just add an OleDbCommandBuilderto your code
只需在您的代码中添加一个OleDbCommandBuilder
Private Sub frmDatabase_Load(sender As Object, e As EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\dbSchoolDatabase.mdb"
con.Open()
ds.Tables.Add(dt)
da = New OleDbDataAdapter("Select * from tableStudentDetails", con)
Dim cb = new OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.Fill(dt)
dgvStudentDetails.DataSource = dt.DefaultView
con.Close()
End Sub
Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
da.Update(dt)
End Sub
This class is required because, by itself the OleDbDataAdapter cannot create the commands for DELETE/UPDATE/INSERT required to update new/deleted or changed row in your grid. Also keep in mind that OleDbCommandBuilder cannot build the required commands if the SELECT command don't return the primary key of your table. In that case you need to manually build yourself the commands.
此类是必需的，因为 OleDbDataAdapter 本身无法为更新网格中的新行/删除行或更改行所需的 DELETE/UPDATE/INSERT 创建命令。还要记住，如果 SELECT 命令不返回表的主键，则 OleDbCommandBuilder 无法构建所需的命令。在这种情况下，您需要手动构建自己的命令。
Note also, as pointed by @gordthompsonin its comment below, that a precautionary step to take with the OleDbCommandBuilder is to add the special characters that the CommandBuilder will use around your field names and tables names to avoid conflict with reserved keywords if any is present in your table
另请注意，正如@gordthompson在其下面的评论中指出的那样，对 OleDbCommandBuilder 采取的预防措施是添加 CommandBuilder 将在字段名称和表名称周围使用的特殊字符，以避免与保留关键字（如果存在）发生冲突在你的桌子上
