Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Update Method of DataAdapter not updating all the fields in vb.net app

I developed a small vb.net program to learn how to implement different functionality before I create the bigger (full blown) program.  One of the things I've created is a user defined control with three checkboxes, and a custom property and event.  The main form has only two controls that aren't buttons, the user defined control and a textbox, each one bound to a field of a datatable.  I'm using the SQLDataAdapter's update method to update changes to the local datatable back to the SQL Server table.  I've seen that for any data row I've been on and traversed from to another row, the RowState is "16" which means "Modified", even if I didn't make any changes to it.  If I change a checkbox in the user defined control, it gets saved to the local datatable successfully.  I know this because the same checkbox is checked when I move from that row and back to it.  Also, any time I change the textbox contents it saves properly back to the SQL database when I invoke the update method on the data adapter.  However, the field bound to the usercontrol doesn't always update back to the SQL Server table.  For whatever reason, it seems to update successfully only when I click the "Next" button several times to transverse through the rows of the local datatable before issuing the update.  Why, is the field bound to the usercontrol not updating unless I traverse through several records before issuing the update?

Imports System.Data.SqlClient
Public Class Form1

    Dim dap1 As SqlDataAdapter
    Dim das1 As New DataSet
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim cn As New SqlConnection

        Try
            cn = New SqlConnection("ConnectStringGoesHere")
            dap1 = New SqlDataAdapter("SELECT * FROM Returns WHERE WorkOrderId = 51333 Order By SerialNo", cn)
            cn.Open()
            dap1.Fill(das1, "Returns")
            cn.Close()
            dap1.UpdateCommand = New SqlCommand("UPDATE Returns SET SerialNo = @SerialNo, " &
                "ICTResultsBefore = @ICTResultsBefore WHERE ReturnId = @ReturnId", cn)
            Dim prm1 As SqlParameter = dap1.UpdateCommand.Parameters.Add("@ReturnId", SqlDbType.Int)
            prm1.SourceColumn = "ReturnId"
            prm1.SourceVersion = DataRowVersion.Original
            dap1.UpdateCommand.Parameters.Add("@SerialNo", SqlDbType.VarChar, 50, "SerialNo")
            Dim prm2 As SqlParameter = dap1.UpdateCommand.Parameters.Add("@ICTResultsBefore", SqlDbType.Int)
            prm2.SourceColumn = "ICTResultsBefore"
            prm2.SourceVersion = DataRowVersion.Original

            Me.tbxSerialNo.DataBindings.Add("Text", das1, "Returns.SerialNo")
            Me.PassFailOptGrp1.DataBindings.Add("TestResults", das1, "Returns.ICTResultsBefore", True, DataSourceUpdateMode.OnPropertyChanged, Nothing)
            Me.BindingContext(das1, "Returns").Position = 0

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click

        Me.BindingContext(das1, "Returns").Position -= 1

    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click

        Me.BindingContext(das1, "Returns").Position += 1

    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        If Me.BindingContext(das1, "Returns").Position = 0 Then
            Me.BindingContext(das1, "Returns").Position -= 1
        Else
            Me.BindingContext(das1, "Returns").Position -= 1
            Me.BindingContext(das1, "Returns").Position += 1
        End If
        Try
            dap1.Update(das1, "Returns")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Private Sub btnTroubleShoot_Click(sender As Object, e As EventArgs) Handles btnTroubleShoot.Click

        For Each Rw As DataRow In das1.Tables("returns").Rows
            Debug.WriteLine(Rw("SerialNo") & " " & Rw.RowState)
        Next

    End Sub
End Class

Open in new window

Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

>>Why, is the field bound to the usercontrol not updating unless I traverse through several records before issuing the update?

How do you check?  In .net records are 'disconnected'  which means that updating the database does not happen immediately.  A connection has to be generated and then updating performed.  It could be that you are checking the underlying data before the system has performed a scheduled update.
Avatar of Declan Basile

ASKER

I invoke the update on click of a button.  After I click the button, I check the table in SQL Server.  Changes made to the textbox bound to the local table are synchronized, but changes made to the usercontrol bound to the local table are not.
I think I see my problem.  I shouldn't have set the @ICTTestResultsBefore parameter's DataRowVersion to original.  I copied the code from the @ReturnId parameter which is the primary key and didn't delete that line.  Because of this mistake, I believe it's taking two updates of a modified record to save the change, the first update to make the current value the original value, and the second update to save the original value.  Also, the datatype of @ICTTestResultsBefore is smallint, not int.  I'll test if this is my problem tomorrow when I get into work.
ASKER CERTIFIED SOLUTION
Avatar of Declan Basile
Declan Basile
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial