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.