通过DataGridView更新Access数据库在VB.NET中使用OLEDB [英] Updating an Access Database via a DataGridView Using OLEDB in VB.NET

问题描述

我已将Access数据库链接到我的程序。它填充DataGridView，因为它是打算让程序的一部分工作。但是，我试图让DataGridView更新Access数据库文件与任何更改，但在尝试修复我的代码或寻找一个替代解决方案无数次尝试后，我被困扰。

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.

任何人都可以看到任何错误的东西，我错过了，会导致代码不能按要求的功能？提前感谢。

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

推荐答案

p>只需添加 OleDbCommandBuilder 到您的代码

Just add an OleDbCommandBuilder to your code

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

是必需的，因为OleDbDataAdapter本身不能创建在网格中更新新的/删除的或更改的行所需的DELETE / UPDATE / INSERT的命令。还要记住，如果SELECT命令不返回表的主键，OleDbCommandBuilder不能构建所需的命令。在这种情况下，您需要手动建立自己的命令。

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.

请注意，如 @ gordthompson 在下面的注释中，OleDbCommandBuilder采取的预防措施是添加CommandBuilder将使用的特殊字符围绕您的字段名称和表名称，以避免与保留关键字冲突if任何存在于您的表中

Note also, as pointed by @gordthompson in 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

这篇关于通过DataGridView更新Access数据库在VB.NET中使用OLEDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！