Results 1 to 10 of 10

Thread: I cannot delete a Newly added record CONCURRENCY VIOLATION

  1. #1
    John_neuman is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    May 2008
    Posts
    4
    Reputation
    0

    I cannot delete a Newly added record CONCURRENCY VIOLATION

    i am using a Bindinsoure navigator to connect to a Ms Acess 2003 file

    I cannot delete a Newly added record but i can delete old records

    but for the same Record if i try to delete it after i reopen the application it goes fine. i think the problem is that my dataset is not refred as i add and save the record.



    this is my code to Save the record



    Try



    If Me.Validate() Then

    Me.NewBindingSource.EndEdit()

    newBindingAdapter.Update(NewDataSet.DB_Worcs_repor t_documentation)

    MsgBox("Update Successful", MsgBoxStyle.Information, "Update")

    'Record the Changes Made

    ' This table will Record any changes Made to the Record its a sepate table than that of the one in bindingsource

    Next_sequence = Generate_NextSequence("d:\cs\worcs_data.mdb", ID.Text.ToString, "Tbl_WORCS_Comments", "Doc_ID")

    Query = "insert into Tbl_WORCS_Comments ( Doc_ID, Seq_Number, Comm_Text, Comm_Date, User_LogIn ) values( " & CType(ID.Text, Integer) & " , " & Next_sequence & " , '" & Query & "' , '" & Date.Now & "' , '" & LogInID & "')"

    Insert_Record("d:\cs\worcs_data.mdb", Query)

    Else

    MsgBox("Update NOT Successful", MsgBoxStyle.Exclamation, "Update")

    End If

    Catch ex As Exception

    MsgBox(ex.Message, MsgBoxStyle.Critical, "Update Failure")

    End Try









    HERE IS MY CODE TO DELETE THE RECORD





    NewBindingSource.RemoveCurrent()

    Me.NewBindingSource.EndEdit()



    newBindingAdapter.Update(NewDataSet.DB_Worcs_repor t_documentation) ' THE ERROR IS THROWN AT THIS LINE

    ' AND THE ERROR SAYS cONCUREENCY VIOLATION : THE DELETE COMMAND AFFECTED 0 OF EXPECTED 1 RECORDS

    NewDataSet.AcceptChanges()





    ANYBODY ANY HELP

  2. #2
    Stonkie is offline VB.NET Forum Master
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Sep 2007
    Posts
    279
    Reputation
    133
    What happens is that the row's RowState property is set to added when you add it, then to deleted when you delete it. So when the TableAdapter's Update method is called, it tries to delete a row that doesn't exist, so the operation won't delete anything.

    Now, the cause of the concurrency exception is that the TableAdapter doesn't use SQL like this :

    Code:
    DELETE FROM myTable WHERE Id = @Id
    It uses something like the following to detect changes in the data from someone else :

    Code:
    DELETE FROM myTable WHERE Id = @Id AND Column1 = @OldColumn1Value AND Column2 = @OldColumn2Value AND ...
    This effectively ensures that the deleted record was not modified by anyone else, otherwise nothing is done. Then, the TableAdapter checks if the number of deleted rows is 1, otherwise it assumes there was a concurrency problem and it throws the exception.

    Now, in your case, the record was never in the database so it deletes zero records and the TableAdapter thinks there was a concurrency problem. I could almost hear you cursing Microsoft developers now...

    The solution I found is that whenever you delete a row from the DataTable, check to see if the row's RowState property is set to Added and if so, remove the row from the RowCollection (The Rows property on the DataTable). This way, it will be removed from the DataTable altogether and the TableAdapter will never know you created it in the first place.

    There might be some common way to work around this problem, but this worked for me... Now I don't remember if I listened on the DataTable's event (can you still tell the initial RowState during this event?), I added a method on the DataTable or I hard coded "table.Rows.Remove(row)" everywhere. I do remember the frustration though...
    The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?

  3. #3
    Peinecone is offline VB.NET Forum Newbie
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Feb 2009
    Posts
    13
    Reputation
    69
    Quote Originally Posted by Stonkie View Post
    What happens is that the row's RowState property is set to added when you add it, then to deleted when you delete it. So when the TableAdapter's Update method is called, it tries to delete a row that doesn't exist, so the operation won't delete anything.

    Now, in your case, the record was never in the database so it deletes zero records and the TableAdapter thinks there was a concurrency problem. I could almost hear you cursing Microsoft developers now...

    The solution I found is that whenever you delete a row from the DataTable, check to see if the row's RowState property is set to Added and if so, remove the row from the RowCollection (The Rows property on the DataTable). This way, it will be removed from the DataTable altogether and the TableAdapter will never know you created it in the first place.

    There might be some common way to work around this problem, but this worked for me... Now I don't remember if I listened on the DataTable's event (can you still tell the initial RowState during this event?), I added a method on the DataTable or I hard coded "table.Rows.Remove(row)" everywhere. I do remember the frustration though...
    I am having this same problem, but I don't know how to access the RowState property. Here is my code for the delete function.

    Code:
        Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
            If MessageBox.Show("Do you really want to Delete this Record?", _
                "Delete", MessageBoxButtons.YesNo, _
                MessageBoxIcon.Warning) = DialogResult.No Then
                Exit Sub
            End If
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            If bs.Current.Row.RowState = Something? Then  'This line is what I am trying to work with.
                bs.RemoveCurrent()
                da.Update(ds, "SyscoPrice")
                Populate()
            End If
    
        End Sub

  4. #4
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1719
    Quote Originally Posted by Peinecone View Post
    I am having this same problem, but I don't know how to access the RowState property. Here is my code for the delete function.
    Peine, are you still having this problem?

  5. #5
    psionman is offline VB.NET Forum Newbie
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jul 2009
    Posts
    4
    Reputation
    0

    Concurrency issue

    Hi

    I have read this thread (and many others on this topic) but can find no help for my problem

    I am updating an MSDE (SQL) database in vb.net using ado.net

    SELECT, UPDATE and INSERT all work fine. But DELETE gives me this concurrency error

    I am the only user on database and so no one else can change the record

    My DELETE SQL command, which covers all the fields in the table, is:

    "DELETE FROM AAA WHERE Format_ID = '@Format_ID' AND Closed = @Closed AND CurrentVersion = '@CurrentVersion' AND Description = '@Description' AND Timestamp = @Timestamp"

    My code is:
    <code>

    Private Sub Delete_Record(ByVal sTableName As String, ByVal sKey As String)
    Dim clsTable As cDCAM_Table
    Dim cmdDel As SqlCommand
    Dim sSqlDelete As String
    Dim NN As Integer

    Try
    'Get table definition from the database definitions
    clsTable = gDatabase.Tables(sTableName)
    sSqlDelete = clsTable.SqlDelete

    'Create DELETE command
    cmdDel = New System.Data.SqlClient.SqlCommand(sSqlDelete, gDatabase.SqlConnection)

    'Create command parameters from the database definition
    With clsTable.SqlCommand
    For NN = 0 To .Parameters.Count - 1
    With .Parameters(NN)
    cmdDel.Parameters.Add(.ParameterName, .SqlDbType, .Size, .SourceColumn)
    End With
    Next NN
    End With
    gDatabase.Adapter.DeleteCommand = cmdDel

    'Locate Row in Dataset table and delete
    With gDatabase.DataSet.Tables(sTableName)
    For NN = 0 To .Rows.Count
    If .Rows(NN).ItemArray(0) = sKey Then
    .Rows(NN).Delete()
    gDatabase.Adapter.Update(gDatabase.DataSet, sTableName)
    Exit For
    End If
    Next NN
    End With
    Catch ex As SqlException
    MsgBox("Error: " & ex.ToString())
    End Try
    MsgBox(sKey & " deleted", , vbInformation)
    End Sub
    </code>

    Can someone please help?

  6. #6
    Stonkie is offline VB.NET Forum Master
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Sep 2007
    Posts
    279
    Reputation
    133
    That's really the hard way of doing a delete (I hear it looked like that in .NET 1.1 but I'm happy I never used it!). And you don't have a primary key? And are you using Format_ID as a String?

    Anyway, TableAdapters use the number of deleted rows to determine if there was a concurrency exception. The "update" method is used to synchronize the local DataTable cache with the database. If the where condition happens to find no row in the database that matches exactly all those criteria, it assumes the local cache has lost synch with the database.

    My guess is you are trying to delete a row that does not exist. Try the resulting SQL directly on the database. If it deletes nothing at all, then this is the cause of the problem.

    But the actual underlying problem is that you are not using the ADO.NET framework properly. You are actually hacking your way into something that works. Try to use the dataset designer instead of building your dataset in code. It will prevent you from making mistakes in the SQL and it will actually format your parameters for you. You will need to add a primary key to the tables to do that...

    So, first thing first : get the database in more standard shape (look up "database normalization" and "foreign keys"). Then move towards using the dataset designer in visual studio (look up "dataset designer" and possibly a good book on ADO.NET).


    Btw, isn't "For NN = 0 To .Rows.Count" going to give you an exception accessing a row after the last one?

    PS. I know it's a pretty big step to climb and you probably have code to write fast so you can't afford learning all this stuff for this single project, but I can't really hack this any better than you can...

    EDIT : I just thought maybe the problem comes from a first iteration of the loop deleting more than one row (because the condition is not unique) and then a later row trying to delete it again... Anyway, just an idea...
    Last edited by Stonkie; 07-28-2009 at 2:29 PM.
    The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?

  7. #7
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1719
    Quote Originally Posted by Stonkie View Post
    Anyway, TableAdapters use the number of deleted rows to determine if there was a concurrency exception
    He's using DataAdapters but I suspect the end result will be the same

    My guess is you are trying to delete a row that does not exist. Try the resulting SQL directly on the database. If it deletes nothing at all, then this is the cause of the problem.
    I follow this.
    If OP writes a delete command that takes 4 parameters
    And then creates a datarow that has a state deleted and 4 columns such that NO DB ROW has that combination of columns then 0 records will be affected and the DataAdapter reports this by throwing an exception


    But the actual underlying problem is that you are not using the ADO.NET framework properly.
    Echo

    Read DW2 link in my sig, section Creating a Simple Data App, and let's start you on doign your data access properly

  8. #8
    psionman is offline VB.NET Forum Newbie
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jul 2009
    Posts
    4
    Reputation
    0
    Thanks for your help guys

    First of all the database is normalised, Format_ID being the key of this table

    I too was horrified to think that ADO.NET wanted the Delete command in that format. I ended up with that because I misread Stonkies earlier posting in this thread. I now see he was describing how the data adapter might work and not what it demands (frustration leads us to try anything at the end of a long day). That's a relief and my SQL now looks like it should:

    Code:
    DELETE FROM Formats WHERE Format_ID = '@Format_ID'
    and this does work on the database but not in my procedure

    the loop
    Code:
          With gDatabase.DataSet.Tables(sTableName)
            For NN = 0 To .Rows.Count - 1
              If .Rows(NN).ItemArray(0) = sKey Then
                .Rows(NN).Delete()
                gDatabase.Adapter.Update(gDatabase.DataSet, sTableName)
                Exit For
              End If
            Next NN
          End With
    does find the Row in the dataset (because the .Delete line is accessed) and the record is in the table (I can see it)

    (Thanks for spotting the deliberate error (-1) - never got to it because of the Exit For)

    the .Rows(NN).RowState prior to the .Delete is Unchanged{2} and after the .Delete is Deleted{8} but I still get the concurrency error

    [EDIT]PS the reason that I am not using the data designer and was forced down this route in the first place is because I get this error

    https://connect.microsoft.com/SQLSer...dbackID=356453

    for which I cannot find a solution, and so I cannot access the designer

    PPS. I have now (with renewed vigour) solved this error, but I am still struggling with it. After defining the connection I get the message "the database you selected is new or does not contain any objects", which is not true - there are at least 10 tables on the database
    Last edited by psionman; 07-29-2009 at 8:46 AM. Reason: More information

  9. #9
    Stonkie is offline VB.NET Forum Master
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Sep 2007
    Posts
    279
    Reputation
    133
    I have now (with renewed vigour) solved this error
    Haha Keep it up! I've never seen the error message you mention, but googling it seems to return some results... Anyway, by my experience, your time is the biggest expense related to developing software.

    Buying a brand new computer just so you can use the dataset designer would more than pay for itself on the long run. I don't mean you need to change computer, but it's worth solving this issue to provide you with the most productive tools. It is worth it either from a return on investment, training or mood point of view (you can't imagine how irritable I would become if I lost my dataset designer!)...

    Btw, it seems people who got this error all had the standard of professional edition. If you have it too (I mean the *legal* one), you could ask the Microsoft help desk...
    The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?

  10. #10
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1719
    Quote Originally Posted by psionman View Post
    PPS. I have now (with renewed vigour) solved this error, but I am still struggling with it. After defining the connection I get the message "the database you selected is new or does not contain any objects", which is not true - there are at least 10 tables on the database
    Are you connecting to the right base and catalog?

    Does the user youre connecting with have permission to se ethe tables?

    Can you use VS to create a table using Server Explorer? Can you then see that table?

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Harvest time tracking