database Datagridview 将更改保存到数据库 vb.net

Datagridview save changes to Database vb.net

提问by FPGA

Hello i've a databse that i load to Datagridview in vb.net application. it loads fine, however when i try to save the date it doesn't work. here is the code

你好，我有一个数据库，我在 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

Update requires a valid InsertCommand when passed DataRow collection with new rows. what am i supposed to do?

使用新行传递 DataRow 集合时，更新需要有效的 InsertCommand。我应该做些什么？

the accessdatabase 3 colmuns and ID is primary key ID Field1 Field2

accessdatabase 3 colmuns 和 ID 是主键 ID Field1 Field2

回答by Tim Schmelter

So you must define an InsertCommandfor you DataAdapter

所以你必须InsertCommand为你定义一个DataAdapter

Side-note: The line DSet.AcceptChanges()is redundant since the previous line Dadapter.Updatewill call AcceptChangesimplicitely.

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

You should use using-statementfor anything implementing IDisposablelike a Connection. That would call Dispose(which closes the connection) implicitely even in case of an exception.

您应该using-statement用于任何实现IDisposable如连接。Dispose即使在发生异常的情况下，这也会隐式调用（关闭连接）。

So replace:

所以替换：

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

with

和

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

回答by Mark

You need to read back the dataset from the datagrid

您需要从数据网格中读回数据集

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

回答by Maxim Galushkin

My VB.NET code for the 4 type of databases (update information from DataGridView to Database)

我的 4 种数据库的 VB.NET 代码（将信息从 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