none
Concurrency Violation: the Delete Command Affected 0 records RRS feed

  • Question

  • Background Info: I am building a tools that verifies data on many PC's(Nodes),  All nodes need to work together for the systems to operate correctly.  I upload data from all Nodes (Ex: NodeA - NodeZ), after finding a configuration error on NodeA I fix it then need to upload only NodeA not NodeB - NodeZ.  I pass into the function  DeleteRows the "NodeA" which is in the PROJECTNAME column is selected and removed for 20 tables in SQL 2005.  I am trying to set it up as Last in wins.  Here is is what I thought accomplished this...

     

    I built my own insert, delete, update commands.  Added an index column to each of 20 tables and made them the Primary key, then used the primary key in my where statement when building my delete command.  I then use a for loop to mark each row as deleted then call the update command, and the update command works when inserting so my tables continue to grow and I am unable to remove the old information from my tables making the reports incorrect. 

     

     

    Public da As SqlDataAdapter

    Public ds As DataSet = New DataSet()

     

    Public Function DeleteRows(Optional ByVal sFilter As String = "") As Integer

    Dim blnChangesMade As Boolean

    Try

    'Set query

    Dim sql As String = "select * from "m_sTableName

    ' Create data adapter

    da = New SqlDataAdapter(sql, m_Conn)

    'Set query

    Dim del As String = _

    "delete from " & m_sTableName _

    & " where " _

    & " ID = @ID "

    Dim cmd As SqlCommand = New SqlCommand(del, m_Conn)

    ' Map parameters

    cmd.Parameters.Add("@ID", SqlDbType.Int, 128, "ID")

    da.DeleteCommand = cmd

    'Get the data table reference

    Dim dt As DataTable = ds.Tables(m_sTableName)

    ' Delete Project from data table

    For Each row As DataRow In dt.Select("PROJECTNAME = '" & sFilter & "'")

    row.Delete()

    blnChangesMade = True

    Next

    ' Update database

    If blnChangesMade Then

    da.Update(ds, m_sTableName)

    End If

    Catch ex As Exception

    MsgBox(Err.Description)

    End Try

    End Function

     

    I have burned 2 days on web fixes that have produced no change to this error.  This is my first application using datasets and I like the concept but if I can't get them to work I will go back to connected ADO and recordsets since they are bulletproof.

     

    If anyone sees an error in the above code or knows what I am doing to create this problem please let me know.

    Thursday, July 5, 2007 8:17 PM

Answers

  • Hi,

     

    What i understand in your problem was you were trying to update an uncommitted delete transaction. since both action are in the same transaction it will cause what so called "concurrency violation".

     

    I think the best solution is to separate the delete and update procedures in separate command. wrap these into a transaction and commit in the end.

     

    I hope this is useful.

     

    Goodluck,

     

    Wilmar Acosta

     

     

    Tuesday, July 10, 2007 4:12 PM

All replies

  • If anyone is interested I found a work around.

    I added :

     

    'Clear dataset

    ds.Tables(m_sTableName).Clear()

    'Create and fill my dataset

    da.Fill(ds, m_sTableName)

     

    This is the first thing I do in my delete function.  This way I start with an exact copy of my table before I modify the data in the dataset.  It is a work around because I still don't understand why my datasets original data did not match my SQL table.  The SQL server is on my local systems and it is in development stages and no one has access to the database so no one 'else' is changing the table data.  If anyone knows what I may be doing to create this error please reply.

      

    Tuesday, July 10, 2007 3:13 PM
  • Hi,

     

    What i understand in your problem was you were trying to update an uncommitted delete transaction. since both action are in the same transaction it will cause what so called "concurrency violation".

     

    I think the best solution is to separate the delete and update procedures in separate command. wrap these into a transaction and commit in the end.

     

    I hope this is useful.

     

    Goodluck,

     

    Wilmar Acosta

     

     

    Tuesday, July 10, 2007 4:12 PM
  • Thank You for responding,

     

    I read some information on the transactions after I saw your reply.  I won't be able to test it until later next week, but this is exactly what's happening, Thank You. 

     

    I will respond next week with results.

     

    Thanks Again.

    Wednesday, July 11, 2007 12:14 PM