none
Multiple row deletion not updating RRS feed

  • Question

  • Hi

    I am trying to commit deletions of rows  in the backend db. This code works fine if only one deleted row is present in the data table on update but if more than one deleted row is present in data table I get below error on the line CompanyAdapter.Update(CompanyTable);

    System.Data.DBConcurrencyException was unhandled by user code
    
     Message="Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."
    
     RowCount=1
    
     Source="System.Data"
    
     StackTrace:
    
      at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
    
      at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
    
      at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
    
      at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
    
      at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
    
    

     

    The code is given below. What is the problem and how can I handle the issue of committing multiple deleted rows in one update? 

    Many Thanks

    Regards

     

     Sub UpdateRecord()
    
      Dim I As Integer
    
      Dim Row As DataRow
    
    
    
      For I = CompanyTable.Rows.Count - 1 To 0 Step -1
    
       Row = CompanyTable.Rows(I)
    
       If Row.RowState = DataRowState.Deleted Then
    
        CompanyAdapter.DeleteCommand = BuildDeleteCommand(Row)
    
    
    
        CompanyAdapter.Update(CompanyTable)
    
       End If
    
      Next
    
     End Sub
    
    
     Function BuildDeleteCommand(ByVal Row As DataRow) As OleDbCommand
    
      Dim St As String
    
      Dim Comm As OleDbCommand = New OleDbCommand
    
    
    
      St = "DELETE * FROM MyCompanyTable WHERE [ID] = ?"
    
    
    
      Comm.Connection = LocalConn
    
      Comm.CommandType = Global.System.Data.CommandType.Text
    
      Comm.CommandText = St
    
    
    
      Select Case CompanyTable.Columns("ID").DataType.ToString.ToLower
    
       Case "system.integer", "system.int32"
    
        Comm.Parameters.AddWithValue("@ID", CInt(Row.Item("ID", DataRowVersion.Original)))
    
       Case Else
    
        Comm.Parameters.AddWithValue("@ID", CStr(Row.Item("ID", DataRowVersion.Original)))
    
      End Select
    
      BuildDeleteCommand = Comm
    
     End Function
    
    
    
    
    Friday, December 31, 2010 11:00 PM

All replies

  • Hi

    I may have figured the problem; the Update method updates all rows in the table while my delete command only caters for one row at a time. Is there a way that Update method only updates one row at a time? Can I for instance use the CompanyAdapter.RowUpdating event to assign command for each row separately?

    Many Thanks

    Regards

    Saturday, January 1, 2011 1:35 AM