DataGridView将更改保存到数据库VB.NET

Question

你好，我有一个在VB.NET应用程序中加载到DataGridView的数据库。它可以正常加载，但是当我尝试保存日期时却无法成功。以下是代码：

    Private myConString As String
Private con As OleDbConnection = New OleDbConnection
Private Dadapter As OleDbDataAdapter
Private DSet As DataSet
Private DSet2 As DataSet
Private ConCMD As OleDb.OleDbCommand

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    myConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Database31.accdb"
    con.ConnectionString = myConString
    con.Open()
    Dadapter = New OleDbDataAdapter("select * from Table1", con)
    DSet = New DataSet
    Dadapter.Fill(DSet, "Table1")
    DataGridView1.DataSource = DSet.Tables("Table1")
    con.Close()
End Sub


Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    con.Open()
    Dadapter.Update(DSet, "Table1")
    DSet.AcceptChanges()
    con.Close()
End Sub

当传递包含新行的DataRow集合时，更新需要有效的InsertCommand。我该怎么办？

Access数据库有三列，ID是主键。 ID，Field1，Field2。

- FPGA

Answer 1

因此，您必须为您的 DataAdapter 定义一个InsertCommand。

注意：行DSet.AcceptChanges()是多余的，因为前一行Dadapter.Update将隐式调用AcceptChanges。

对于任何实现IDisposable的内容（如连接），应该使用using语句。即使在异常情况下，它也会隐式调用Dispose（关闭连接）。

所以替换为：

con.Open()
Dadapter.Update(DSet, "Table1")
DSet.AcceptChanges()
con.Close()

使用

Using con =  New OleDbConnection(myConString)
    con .Open()
    Dadapter.Update(DSet, "Table1")
End Using

- Tim Schmelter

---

谢谢，但插入命令是什么？！我的数据库只有3个字段：ID、Field1和Field2。那么我的插入命令应该长什么样子？ - FPGA

编辑了我的答案，提供了 OleDbDataAdapter.InsertCommand 的 MSDN 链接。其中有一个 SQL INSERT 语句的示例。 - Tim Schmelter

很好，现在假设我用循环为不同的字段填充了数据网格视图中的2000个新值..我想保存这些值，最好的方法是什么？插入应该在循环期间而不是循环后进行吗？ - FPGA

@user1492051：看一下这个示例代码，它展示了如何将DataGridView绑定到Access数据库以及如何选择、更新、删除和插入数据：http://www.dreamincode.net/forums/topic/148532-using-datagridview-with-access/ - Tim Schmelter

我还会在.Update方法之前添加Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(Dadapter)，以防止出现错误："当传递具有修改行的DataRow集合时，更新需要有效的UpdateCommand"。 - Simple Sandman

Answer 2

我的VB.NET代码适用于4种类型的数据库（将DataGridView中的信息更新到数据库）

Private Sub sqldb_savedata()
    Dim connectionString As String = "Server='" & sql_server & "';Database='" & sql_database & "';User Id='" & sql_user & "';Password='" & sql_pass & "'"
    Dim sqlCon = New SqlConnection(connectionString)
    If (sqlCon.State = ConnectionState.Closed) Then sqlCon.Open()
    Dim SQLAdapter = New SqlDataAdapter("SELECT * FROM clinics", sqlCon)
    Dim SQLDataSet As New DataSet
    Dim myTable = DataGridViewClinic.DataSource
    Dim cmdbuilder As New SqlCommandBuilder(SQLAdapter)
    SQLAdapter.Update(myTable, "clinics")
    MsgBox("Updated!", MsgBoxStyle.OkOnly, "")
End Sub

Private Sub mysqldb_savedata()
    Dim connectionString As String = "Server='" & mysql_server & "';Database='" & mysql_database & "';User Id='" & mysql_user & "';Password='" & mysql_pass & "'"
    Dim sqlCon = New MySqlConnection(connectionString)
    If (sqlCon.State = ConnectionState.Closed) Then sqlCon.Open()
    Dim SQLAdapter = New MySqlDataAdapter("SELECT * FROM clinics", sqlCon)
    Dim SQLDataSet As New DataSet
    Dim myTable = DataGridViewClinic.DataSource
    Dim cmdbuilder As New MySqlCommandBuilder(SQLAdapter)
    SQLAdapter.Update(myTable, "clinics")
    MsgBox("Updated!", MsgBoxStyle.OkOnly, "")
End Sub

Private Sub firebirddb_savedata()
    Dim connectionString As String = "Database='" & firebird_server & "';User=SYSDBA;Password=masterkey;Dialect=3;ServerType=1"
    Dim sqlCon = New FirebirdSql.Data.FirebirdClient.FbConnection(connectionString)
    If (sqlCon.State = ConnectionState.Closed) Then sqlCon.Open()
    Dim SQLAdapter = New FirebirdSql.Data.FirebirdClient.FbDataAdapter("SELECT * FROM clinics", sqlCon)
    Dim SQLDataSet As New DataSet
    Dim myTable = DataGridViewClinic.DataSource
    Dim cmdbuilder As New FirebirdClient.FbCommandBuilder(SQLAdapter)
    SQLAdapter.Update(myTable, "clinics")
    MsgBox("Updated!", MsgBoxStyle.OkOnly, "")
End Sub

 Private Sub localdb_savedata()
    DBconn = New SqlCeConnection("Data Source=Data Source=|DataDirectory|\Database.sdf")
    If (DBconn.State = ConnectionState.Closed) Then DBconn.Open()
    Dim SQLAdapter = New SqlCeDataAdapter("SELECT * FROM clinics", DBconn)
    Dim SQLDataSet As New DataSet
    Dim myTable = DataGridViewClinic.DataSource
    Dim cmdbuilder As New SqlCeCommandBuilder(SQLAdapter)
    SQLAdapter.Update(myTable, "clinics")
    MsgBox("Updated!", MsgBoxStyle.OkOnly, "")
End Sub

- Maxim Galushkin

Answer 3

你需要从数据网格中读取数据集。

con.Open()
DSet = DataGridView1.DataSource  '<<<<<<<<<<<<<<<<<<<<<<<
Dadapter.Update(DSet, "Table1")
DSet.AcceptChanges()
con.Close()

- Mark