Declan Basile
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
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.