DataTable not updating Database with DataAdapter (VB.NET)

Cory Rentz
Cory Rentz used Ask the Experts™
on
I am trying to update a database with info from a WinForm. I had no issues when using a “normal” SQL update command written by hand (parameters set to the text box values,) but I am trying to clean up and reduce my code and I thought I would bind the controls to a DataTable and use a DataAdapter's update command to achieve the same thing.

I have tried to get various combinations of setting parameters and update commands to work, but the Database is not getting updated from the new DataTable values. I have stepped through the code with each change and can see that the DataTable is getting the new textbox values, but those updates aren’t going to the Database. (This is seen when the Fill_Date block runs and selects all new data from the database.)

Things I’ve tried: Letting the binding get the new values vs. setting the parameters manually. Using the command builder to build the update command, using the .UpdateCommand.ExecuteNonQuery(), command and of course a straight.Update(DataTable) command.

Below is the code that I am using. I am hoping someone can tell me what it is I am doing wrong/missing, or what is the correct path to take. Is there a "best practice" or a better way to do this?

Public Class frmDATA
    Dim dt_Test As New DataTable
    Dim da_Test As New SqlDataAdapter
    Dim SQLcmd As SqlCommand

    Private Sub frmDemog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BuildSQL()
        Fill_Data()
        BindControls()
    End Sub

    Private Sub frmDemog_Closed(sender As Object, e As EventArgs) Handles Me.Closed
        If Not IsNothing(dt_Test) Then dt_Test.Dispose()
        If Not IsNothing(da_Test) Then da_Test.Dispose()
        If Not IsNothing(SQLcmd) Then SQLcmd.Dispose()
        Me.Dispose()
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Update_Me()
    End Sub

    Private Sub BindControls()
        txtLName.DataBindings.Add("Text", dt_Test, "Last_Name")
        txtFName.DataBindings.Add("Text", dt_Test, "First_Name")
        txtAKA.DataBindings.Add("Text", dt_Test, "AKA")
    End Sub

    Public Sub Update_Me(RefreshSearch As Boolean, RefreshView As Boolean)
        Try
            Dim testID As Integer = frmTest.dgvSearch.CurrentRow.Cells(0).Value
            da_Test.UpdateCommand.Parameters("@ID").Value = testID
            da_Test.Update(dt_Test)

            Fill_Data()

        Catch SqlExceptionErr As SqlException
            MsgBox(SqlExceptionErr.Message, vbCritical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error")
        End Try
    End Sub

    Public Sub Fill_Data()
        Try
            dt_Test.Clear()
            da_Test.SelectCommand.Parameters("@ID").Value = testID
            da_Test.Fill(dt_Test)

        Catch SqlExceptionErr As SqlException
            MsgBox(SqlExceptionErr.Message, vbCritical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error")
        End Try
    End Sub

    Private Sub BuildSQL()
        '** Build Selection Query
        SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
            "SELECT ",
                "data_Test.[Last_Name], ",
                "data_Test.[First_Name], ",
                "data_Test1.[Last_Name] + ', ' + data_Test1.[First_Name] as [AKA] ",
            "FROM [DB].data_Test ",
                "LEFT JOIN [DB].data_Test as data_Test1 ",
                "ON data_Test.[ID] = data_Test1.[AKA_Demog_ID] ",
            "WHERE data_Test.[ID]=@ID"
            ), Vars.sqlConnDB)

        SQLcmd.Parameters.Add("@ID", SqlDbType.Int)
        da_Test.SelectCommand = SQLcmd

        '** Build Update Query
        SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
            "UPDATE [DB].data_Test SET ",
                "[Last_Name]  = @LName,",
                "[First_Name]  = @FName",
            "WHERE [ID] = @ID"
            ), Vars.sqlConnDB)

        With SQLcmd.Parameters
            .Add("@LName", SqlDbType.NVarChar, 255, "Last_Name") 'Required
            .Add("@FName", SqlDbType.NVarChar, 255, "First_Name") 'Required
            .Add("@ID", SqlDbType.Int, 0, "ID")
        End With

        da_Test.UpdateCommand = SQLcmd
    End Sub
End Class

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Support Analyst/Programmer
Commented:
The issue was that the DataTable was not ending the edit, so the updates were not going to the DataAdapter.  The fix is to bind the data to BindingSource and then bind the controls and DGV to the BindingSource.  When updating the records, calling Me.Validate and BindingSource.EndEdit() will confirm the updates so they get updated on the database.  

Here is the final code:

Public Class frmDemog
    Dim dt_Test As New DataTable
    Dim da_Test As New SqlDataAdapter
    Dim SQLcmd As SqlCommand
    Dim bindSource As New BindingSource

    Private Sub frmDemog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BuildSQL()
        Fill_Data()
        BindControls()
    End Sub

    Private Sub BindControls()
        txtLName.DataBindings.Add("Text", bindSource, "Last_Name")
        txtFName.DataBindings.Add("Text", bindSource, "First_Name")
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Update_Me(True)
    End Sub

    Public Sub Update_Me(RefreshView As Boolean)
        Try
            Me.Validate()
            bindSource.EndEdit()
            da_Test.Update(dt_Test)

            If RefreshView Then Fill_Data()

        Catch SqlExceptionErr As SqlException
            MsgBox(SqlExceptionErr.Message, vbCritical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error")
        End Try
    End Sub

    Public Sub Fill_Data()
        Try
            dt_Test.Clear()
            da_Test.SelectCommand.Parameters("@ID").Value = frmDGV.dgvSearch.CurrentRow.Cells(0).Value
            bindSource.DataSource = dt_Test

        Catch SqlExceptionErr As SqlException
            MsgBox(SqlExceptionErr.Message, vbCritical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error")
        End Try
    End Sub

    Private Sub BuildSQL()
        '** Build Selection Query
        SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
            "SELECT ",
                "data_Test.[ID], ",
                "data_Test.[Last_Name], ",
                "data_Test.[First_Name] ",
            "FROM [DB].data_Test ",
            "WHERE data_Test.ID=@ID"
            ), Vars.sqlConnDB)

        SQLcmd.Parameters.Add("@ID", SqlDbType.Int)
        da_Test.SelectCommand = SQLcmd

        '** Build Update Query
        SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
            "UPDATE [DB].data_Test SET ",
                "[Last_Name] = @LName,",
                "[First_Name] = @FName",
            "WHERE [ID] = @ID"
            ), Vars.sqlConnDB)

        With SQLcmd.Parameters
            .Add("@LName", SqlDbType.NVarChar, 255, "Last_Name")
            .Add("@FName", SqlDbType.NVarChar, 255, "First_Name")
            .Add("@ID", SqlDbType.Int, 4, "ID")
        End With

        da_Test.UpdateCommand = SQLcmd
        da_Test.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub
End Class

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial