使用 DataAdapter 使用对 VB.NET 中的 DataGridView 所做的更改来更新 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20338489/
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
Update SQL Server with changes made to a DataGridView in VB.NET using a DataAdapter
提问by MrBill
I am trying to create a form to edit the data in a SQL Server table using a DataAdapter (in code) rather than a TableAdapter (drag and drop). I define the connection, dataset, dataapapter, and datatable when the form loads and populate the datagridview, but then I can't trigger the Update with a button because the DataAdapter expires after the load event is done. With the TableAdapter, it persisted somehow so that I could refer to it in the code for the button. How can I do this?
我正在尝试使用 DataAdapter（在代码中）而不是 TableAdapter（拖放）来创建一个表单来编辑 SQL Server 表中的数据。当表单加载并填充 datagridview 时，我定义了连接、数据集、数据适配器和数据表，但随后我无法使用按钮触发更新，因为数据适配器在加载事件完成后过期。使用 TableAdapter，它以某种方式持续存在，以便我可以在按钮的代码中引用它。我怎样才能做到这一点？
Imports System.Data.SqlClient
Public Class frmGroceries2
Private Sub frmGroceries2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim oCon As New SqlConnection
oCon.ConnectionString = "Data Source=IPS-03042013\sqlexpress;Initial Catalog=SqlToVbExamples;Integrated Security=True"
Dim dsSupplies As New DataSet
Dim daLocalGroceries As New SqlDataAdapter("SELECT * FROM GROCERIES", oCon)
Dim tblLocalGroceries As DataTable
Try
daLocalGroceries.FillSchema(dsSupplies, SchemaType.Source, "LocalGroceries")
daLocalGroceries.Fill(dsSupplies, "LocalGroceries")
tblLocalGroceries = dsSupplies.Tables("LocalGroceries")
dgvLocalGroceries.DataSource = tblLocalGroceries
Catch ex As Exception
MsgBox("Something has gone wrong..." & vbNewLine & ex.Message)
End Try
End Sub
Private Sub cmdSaveChanges_Click(sender As Object, e As EventArgs) Handles cmdSaveChanges.Click
'I want to put the update method in here, but can't
End Sub
End Class
回答by NoChance
You are correct, the data is not saved because the objects are defined in the sub. You could use a form level definitions to overcome this. I have provided a sample below that actually works.
你是对的，数据没有保存，因为对象是在子中定义的。您可以使用表单级别定义来克服这个问题。我在下面提供了一个实际有效的示例。
Imports System.Data.SqlClient
Public Class Form1
'*** Define form level variables so that they are visible from other methods
Dim tblLocalGroceries As DataTable
Dim daLocalGroceries As SqlDataAdapter
Dim dsSupplies As New DataSet
Dim oCon As SqlConnection
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
oCon = New SqlConnection
oCon.ConnectionString = "Data Source=IPS-03042013\sqlexpress;Initial Catalog=SqlToVbExamples;Integrated Security=True"
dsSupplies = New DataSet
daLocalGroceries = New SqlDataAdapter("SELECT * FROM GROCERIES", oCon)
'*** Define command builder to generate the necessary SQL
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(daLocalGroceries)
builder.QuotePrefix = "["
builder.QuoteSuffix = "]"
Try
daLocalGroceries.FillSchema(dsSupplies, SchemaType.Source, "LocalGroceries")
daLocalGroceries.Fill(dsSupplies, "LocalGroceries")
tblLocalGroceries = dsSupplies.Tables("LocalGroceries")
dgvLocalGroceries.DataSource = tblLocalGroceries
Catch ex As Exception
MsgBox("Something has gone wrong..." & vbNewLine & ex.Message)
End Try
End Sub
Private Sub pbUpdate_Click(sender As System.Object, e As System.EventArgs) Handles pbUpdate.Click
'*** Sub responds to event of button 'update' is clicked. It is intended to reflect
'*** grid changes back to db
Dim tblChanges As DataTable = tblLocalGroceries.GetChanges()
Try
If Not (tblChanges Is Nothing) Then
daLocalGroceries.Update(tblChanges)
End If
Catch ex As Exception
MsgBox("Something has gone wrong..." & vbNewLine & ex.Message)
End Try
End Sub
End Class
