vb.net 如何在 Visual Basic 2013 .net 中从 DataGridView 更新 SQLite 数据库？

How to update SQLite database from DataGridView in Visual Basic 2013 .net?

提问by Dr. Atul Tiwari

I am displaying data from SQLite table into a DataGridView as following -

我将 SQLite 表中的数据显示到 DataGridView 中，如下所示 -

Private Sub Subjects_Manager_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con = New SQLiteConnection("Data Source = c:\demo\test.db;Version=3;")
        con.Open()
        sql = "SELECT * FROM med_subjects"
        da = New SQLiteDataAdapter(sql, con)
        da.Fill(ds, "SubjectsList")
        DataGridView1.DataSource = ds.Tables("SubjectsList").DefaultView
        With DataGridView1
            .RowHeadersVisible = False
            .Columns(0).HeaderCell.Value = "Subject Id"
            .Columns(1).HeaderCell.Value = "Subject Name"
        End With
        DataGridView1.Sort(DataGridView1.Columns(0), System.ComponentModel.ListSortDirection.Ascending)
        con.close()
End Sub

I want to save changes done in DataGridView (either Updation of Row/s or Insertion of Row/s) back to SQLite table. But I couldn't find a way to do so.

我想将 DataGridView 中所做的更改（行更新或行插入）保存回 SQLite 表。但我找不到这样做的方法。

Edit 1 :I know Insert/Update Queries of SQLite, but what I don't know is how & where to keep them so that they can be triggered in responses to changes made in DataGridView. e.g.

编辑 1：我知道 SQLite 的插入/更新查询，但我不知道如何以及在哪里保留它们，以便它们可以在响应 DataGridView 中所做的更改时被触发。例如

' I am using this variable for demonstration, in reality InsertSubjectSqlString will be equal to changes done in DataGridView

Dim InsertSubjectSqlString As String = "Insert into med_subjects (Subject_Name) Values ('_Miscellaneous')"
Dim SqliteInsertRow As SQLiteCommand
SqliteInsertRow = con.CreateCommand
SqliteInsertRow.CommandText = InsertSubjectSqlString
SqliteInsertRow.ExecuteNonQuery()

But I don't know, where should I put it?

但我不知道，我应该把它放在哪里？

Edit 2:After seeing comments and answers, I came to know that there is No direct wayto Insert/Update Sqlite database from DataGridView. So I was curious, if there is any event like RowSelectedwhich would

编辑 2：在看到评论和答案后，我开始知道没有直接的方法可以从 DataGridView 插入/更新 Sqlite 数据库。所以我很好奇，如果有什么事件RowSelected会

• trigger on selecting a row and get that row's data
• then taking the row's data into multiple text boxes and lastly
• triggering Insert/Update queries taking values from these textboxes by a button

• 在选择一行时触发并获取该行的数据
• 然后将行的数据放入多个文本框中，最后
• 触发插入/更新查询，通过按钮从这些文本框中获取值

I know it's highly hypothetical with NO sample codes, but it's because I am asking for Event name.

我知道这是高度假设的，没有示例代码，但这是因为我要求事件名称。

回答by CristiC777

Call this on LeaveRow event or CellEndEdit

在 LeaveRow 事件或 CellEndEdit 上调用它

  Private Sub DataGridView1_RowLeave(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowLeave
 Dim i as integer
 Try
 for i = 0 to datagrid.rows.count-1
      myUpdate(datagrid.item(0,i).tostring() , datagrid.item(1,i).tostring())
 next
 Catch ex As Exception
 MsgBox(Err.Description)
 End Try
 End Sub

note , you can also give the column name of your grid in place of column index , like this datagrid.item("fname",i).tostring()

注意，您还可以使用网格的列名代替列索引，例如 datagrid.item("fname",i).tostring()

Here we will save in database:

这里我们将保存在数据库中：

  Sub myUpdate(byval fname as string , byval lname as string)
Try
 dim con as new sqlconnection("you connection string")
 dim cmd as new sqlcommand
      con.open()
      cmd.connection= con
      cmd.commandtext="insert into table (fname,lname) values (@fname,@lname)"
      cmd.Parameters.AddWithValue("@fname", fname)
      cmd.Parameters.AddWithValue("@lname", lname)
      cmd.ExecuteNonQuery()
      Con.close()
      Catch ex As Exception
      MsgBox(Err.Description)
      End Try
 End sub

I hope this will help you to solve !
There are many ways to manipulate data .
CristiC

我希望这会帮助你解决！
有很多方法可以操作数据。
克里斯蒂

回答by Muhammad Tarique

Please use this code hopefully it will work

请使用此代码希望它会起作用

Imports System.Data.SQLite

Public Class Form       
Dim con As New SQLite.SQLiteConnection
        Dim da As New SQLite.SQLiteDataAdapter
        Dim dt As New DataTable
        Dim cmdbl As New SQLite.SQLiteCommandBuilder
        Dim dlgResult As DialogResult     

        Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
             dlgResult = MessageBox.Show("Do you want to save the changes you made?", "Confirmation!", MessageBoxButtons.YesNo)
                            If dlgResult = DialogResult.Yes Then
                                Try
                                    con.Open()
                                    cmdbl = New SQLiteCommandBuilder(da)

                                    da.Update(dt)
                                    MsgBox("Updated successfully!")
                                    con.Close()
                                Catch ex As Exception
                                    MsgBox(ex.ToString)
                                End Try

                            End If
    End Sub
End Class

回答by darbid

I dont think you will find some magic way that the DataGridView and DataTable are going to persist things automatically to the backend SQLite database. As you are hinting at I think you will have to rely on events.

我不认为您会发现 DataGridView 和 DataTable 将事物自动持久化到后端 SQLite 数据库的某种神奇方式。正如您所暗示的那样，我认为您将不得不依赖事件。

I think the event you are missing is answered here stackoverflow cell value changed event

我认为您缺少的事件在这里得到了回答stackoverflow 单元格值更改事件

回答by CristiC777

Ok I think is better to use CellEndEdit, because LeaveRow is triggered only if you click on other row.
Look on this example.
you must
use: da.Update(ds.Tables("SubjectsList").DefaultView)

好的，我认为最好使用 CellEndEdit，因为只有单击其他行才会触发 LeaveRow。
看看这个例子。
你必须
使用：da.Update(ds.Tables("SubjectsList").DefaultView)

 Imports System.Data.SqlClient

Public Class Form1

公开课表1

Const connectionstring As String = "server=(local);database=TestDB;uid=sa;pwd=sa;"
Private SQL As String = "select * from customer"
Private con As New SqlConnection(connectionstring)
Private dt As New DataTable
Private adapter As New SqlDataAdapter(SQL, con)
Private commandbuilder As New SqlCommandBuilder(adapter)

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    adapter.Fill(dt)
    DataGridView1.DataSource = dt
End Sub

Private Sub DataGridView1_RowLeave(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowLeave
    adapter.Update(dt)
End Sub

End Class

结束班