views:

450

answers:

2

I'm having some trouble updating changes I made to a datatable via a dataadapter. I am getting "Concurrency violation: the UpdateCommand affected 0 of 10 rows"

'Get data
Dim Docs_DistributedTable As New DataTable("Docs_Distributed")
Dim sql = "SELECT DISTINCT CompanyID, SortKey, OutputFileID, SequenceNo, DeliveredDate, IsDeliveryCodeCounted, USPS_Scanned FROM Docs_Distributed_Test"

Using sqlCmd As New SqlCommand(sql, conn)
    sqlCmd.CommandType = CommandType.Text
    Docs_DistributedTable.Load(sqlCmd.ExecuteReader)
End Using

'Make various updates to some records in DataTable. 

'Update the Database

Dim sql As String = "UPDATE Docs_Distributed "
sql += "SET DeliveredDate = @DeliveredDate "
sql += "WHERE SequenceNo = @SequenceNo"

Using transaction As SqlTransaction = conn.BeginTransaction("ProcessConfirm")

    Try
        Using da As New SqlDataAdapter
            da.UpdateCommand = conn.CreateCommand()
            da.UpdateCommand.Transaction = transaction
            da.UpdateCommand.CommandText = sql

            da.UpdateCommand.Parameters.Add("@DeliveredDate", SqlDbType.DateTime).SourceColumn = "DeliveredDate"            
            da.UpdateCommand.Parameters.Add("@SequenceNo", SqlDbType.Int).SourceColumn = "SequenceNo"
            da.ContinueUpdateOnError = False
            da.Update(Docs_DistributedTable)
        End Using
        transaction.Commit()
    Catch ex As Exception
        transaction.Rollback()
    End Try
End Using

Now here's the catch. I am selecting DISTINCT records and essentially getting one row per SequenceNo. There may be many rows with the same SequenceNo, and I am hoping this will update them all. I'm not sure if this is related to my problem or not.

A: 

I don't understand the Microsoft-specific aspects of this, plus VB is often hard to follow. But this sequence seems suspect:

Using transaction As SqlTransaction = conn.BeginTransaction("ProcessConfirm")
    Try
        Using da As New SqlDataAdapter
            da.UpdateCommand = conn.CreateCommand()
            da.UpdateCommand.Transaction = transaction

conn.BeginTransaction is followed by conn.CreateCommand(). Isn't that a) useless, b) hazardous to the connection state, or c) potentially a race condition?

wallyk
Thanks. This code actually works as intended, although it's a bit sloppy. Now that I have the code working, cleaned it up using more straightforward methods. There are so many ways to do the same thing with these dataadapters.
Brett
A: 

Your select is from "Docs_Distributed_Test" and your update is to "Docs_Distributed" - this may be the cause of your issue. Are the sequence ID's the same? (If not then perhaps it is indeed affecting 0 rows with it's update).

Other than that, you can always disable optimistic concurrency on your table-adapter and it will no longer enforce the validation (Though in this case that would likely result in no error but not updating any rows).

Tim Schneider
OMG. That was it. I've been so obsessed with the error that I couldn't see the forest for the trees. I am so ashamed of myself.
Brett