Update SQL Server with changes made to a DataGridView in VB.NET using a DataAdapter

Question

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

Outside the form load event handler, the data adapter seems not to exist any more, I am guessing it is cleared because the sub ended — MrBill
– MrBill, Commented Dec 2, 2013 at 23:14

NoChance · Accepted Answer · 2013-12-03 08:49:07Z

3

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

answered Dec 3, 2013 at 8:49

NoChance

5,7844 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrBill Over a year ago

Thanks, really simple but I couldn't see it.

MrBill Over a year ago

Now that I have put this in place I realize you showed me a lot more than just where I should define my objects. I appreciate the extra mile.

Collectives™ on Stack Overflow

Update SQL Server with changes made to a DataGridView in VB.NET using a DataAdapter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related