Question I cannot delete a Newly added record CONCURRENCY VIOLATION

[John_neuman]

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_report_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_report_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

 

[Stonkie]

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 :

DELETE FROM myTable WHERE Id = @Id

It uses something like the following to detect changes in the data from someone else :

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...

 

[Peinecone]

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.

    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 = [COLOR="Red"]Something?[/COLOR] Then  [COLOR="Red"]'This line is what I am trying to work with.[/COLOR]
            bs.RemoveCurrent()
            da.Update(ds, "SyscoPrice")
            Populate()
        End If

    End Sub

 

[cjard]

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?

 

[psionman]

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?

 

[Stonkie]

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...

 

[cjard]

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

 

[psionman]

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:

DELETE FROM Formats WHERE Format_ID = '@Format_ID'

and this does work on the database but not in my procedure

the loop

      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/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=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

 

[Stonkie]

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...

 

[cjard]

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?