• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Datatable.Merge not sending updates to DB

Hello,
i ve got a  Customers datatable. I can update the DB via dataadapter with no problems.
THen I open a second isntance and i update teh same record so i get a cocurrency error. Until here everything is fine.

What im trying to do is to merge the data ive changed into the datatable holding the current data but it is not updating the database after i merge them.
any ideas?
0
arcross
Asked:
arcross
  • 2
2 Solutions
 
Christopher KileCommented:
I'd need to see the code, but I'm guessing you haven't called .AcceptChanges, or you haven't properly defined .InsertCommand or .UpdateCommand.  Could you please attach the relevant code in a code snippet and send it back with your reply?
0
 
arcrossAuthor Commented:
Public Function UpdateRecords() As Boolean
             da = New SqlDataAdapter
            da.UpdateCommand = UpdateCustomer()
            da.ContinueUpdateOnError = True
            da.Update(tbl)
            Return True
End Function
 
Private Function UpdateCustomer() As SqlCommand

        Dim cmd As SqlCommand
        Dim cn As SqlConnection
        cn= New SqlConnection(cnStr)
        cmd = New SqlCommand("spUpdateCustomer", cn)
        cmd.CommandType = CommandType.StoredProcedure

        Dim param As SqlParameter
        param = cmd.Parameters.Add("@CName", SqlDbType.NVarChar, 50, "CName")
        param.SourceVersion = DataRowVersion.Current
        param.Direction = ParameterDirection.Input

        param = cmd.Parameters.Add("@CPhone", SqlDbType.NVarChar, 13, "CPhone")
        param.SourceVersion = DataRowVersion.Current
        param.Direction = ParameterDirection.Input

        '// Original Parameters
        param = cmd.Parameters.Add("@Orig_CName", SqlDbType.NVarChar, 50, "CName")
        param.SourceVersion = DataRowVersion.Original
        param.Direction = ParameterDirection.Input

        param = cmd.Parameters.Add("@Orig_CPhone", SqlDbType.NVarChar, 13, "CPhone")
        param.SourceVersion = DataRowVersion.Original
        param.Direction = ParameterDirection.Input
        '// ID
        param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4, "CustomerID")
        param.Direction = ParameterDirection.Input
        param.SourceVersion = DataRowVersion.Original


        Return cmd
    End Function

IM USING THIS PIECE OF CODE IN ANOTHER SUB
            ......
            bs.EndEdit()
            UpdateRecords()

            If tbl.HasErrors Then
                Dim conflict As New frmConflict2(tbl)   '// OPEN CONFLICT FORM TO RESOLVE ERRORS
                If conflict.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                         tbl.Merge(conflict.dt_DBDataFiltered, True)
                         tbl.AcceptChanges()
                             UpdateRecords()  '// UPDATE AGAIN
                End If
            End If
........
notes:
TBL (Datatable)=  Is filled with data from database when the form opens for first time.
conflict.dt_DBDataFiltered = Returned from the conflict form. It contains the rows that have been modified to new values. The ones The second instance tries to update. It contained last version of data from the database.

thanks for your help
0
 
grayeCommented:
It's the AcceptChanges() that's causing the problem....   comment out that line, and you'll be golden
That's a poorly named method that doesn't do what it appears it might do.   A better name (somewhat facetiously) is LetsPretendThatChangesHaveAlreadyBeenMadeAndEraseAllRowStateMarkers()
0
 
Christopher KileCommented:
Rather than graye's solution, I would transpose the UpdateRecords() call with the tbl.AcceptChanges() call - this way, the changes would be marked as Original in the DataSet, reflecting that the once-current values have now been posted to the database.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now