使用DataAdapter在VB.NET中对DataGridView所做的更改更新SQL Server [英] Update SQL Server with changes made to a DataGridView in VB.NET using a DataAdapter

问题描述

我正在使用DataAdapter（代码）而不是TableAdapter（拖放）创建一个表单来编辑SQL Server表中的数据。当表单加载和填充datagridview时，我定义了连接，数据集，dataapapter和datatable，但是由于DataAdapter在加载事件完成后过期，所以我无法使用按钮触发更新。使用TableAdapter，它以某种方式持续存在，以便我可以在代码中查看按钮。如何做到这一点？

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?

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

推荐答案

你是正确的，因为在子目录中定义了对象，所以不保存数据。您可以使用表单级别定义来克服这一点。我已经提供了一个实际工作的例子。

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

这篇关于使用DataAdapter在VB.NET中对DataGridView所做的更改更新SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！