Update Sql Server With Changes Made To A Datagridview In Vb.net Using A Dataadapter

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, data

Solution 1:

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