Concurreny violation on delete in dataset

[Flomo Togba Kwele]

I received the following error:

Concurrency violation: the DeleteCommand affected 0 of the expected 1
records.

on the Update method of the adapter below:

Me.ClientBindingSource.RemoveCurrent()
Me.Validate()
Me.ClientBindingSource.EndEdit()
Try
Me.clientAdapter.Update(DsClient.Client)
Catch ex As System.Data.SqlClient.SqlException

The ClientBindingSource has as its source a dataset and as its
datamember one of 3 tables contained in the dataset.

There is a PersonBindingSource whose datasource is the
ClientBindingSource and its datamember is the foreign relation between
the 2 tables.

Finally there is an AddressBindingSource whose datasource is
ClientBindingSource and its datamember is the foreign relation between
Person and Address.

I set both relations to cascade on delete in the Dataset designer. Why
is this happening?

Thanks, Flomo

 

[WenYuan Wang]

Hi Flomo,
According to your description, I understand that you encountered a
concurrency violation exception when updating the table by TableAdapter.
Please don't hesitate to correct me if I misunderstand anything here.

In general, the root cause of such issue is that the rows (deleted in
datasource) can not be found in the database. We suggest you can create
partial class and add the RowUpdating Event for the TableAdapter to check
the parameter values which are passed to delete command.

Namespace [TableAdapterNameSpace]
Partial Public Class [TableAdapterName]
Private Sub taRequest_RowUpdating(ByVal sender As Object, ByVal e
As System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles
_adapter.RowUpdating
Dim iCount As Integer
For iCount = 0 To _adapter.DeleteCommand.Parameters.Count - 1

Console.WriteLine(_adapter.DeleteCommand.Parameters(iCount).ToString() & "
= " & _adapter.DeleteCommand.Parameters(iCount).Value)
Next
Console.Write(_adapter.DeleteCommand.CommandText)
End Sub
End Class
End Namespace

This will display all the parameters and their values in output window .
Could you please paste
these values for further analysis ? Please feel free to reply me if you
have anything unclear and I'm glad to work with you.

Have a great day,
Best regards,
Wen Yuan

 

[Flomo Togba Kwele]

Wen Yuan,

I had to change your routine a little:

Partial Public Class ClientTableAdapter
Private Sub taRequest_RowUpdating(ByVal sender As Object, ByVal
e As System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles
_adapter.RowUpdating
Dim iCount As Integer
Dim str As String = String.Empty
For iCount = 0 To _adapter.DeleteCommand.Parameters.Count - 1
If _adapter.DeleteCommand.Parameters(iCount).Value Is
Nothing Then
str = "<null>"
Else
str =
_adapter.DeleteCommand.Parameters(iCount).Value.ToString
End If

Console.WriteLine(_adapter.DeleteCommand.Parameters(iCount).ToString()
& "= " & str)
Next
Console.Write(_adapter.DeleteCommand.CommandText)
End Sub
End Class

@Original_ClientID= <null>
@Original_Descr= <null>
@Original_PersonID= <null>
@Original_PrintAudit= <null>
@Original_ExcludeBase= <null>
@Original_ReportDirectory= <null>
@Original_AuditFrequencyType= <null>
@Original_AuditFrequencyUnits= <null>
@Original_CASSTemplateFirstClass= <null>
@Original_CASSTemplateBulk= <null>
@Original_IconFileName= <null>
DELETE FROM [Client] WHERE (([ClientID] = @Original_ClientID) AND
([Descr] = @Original_Descr) AND ([PersonID] = @Original_PersonID) AND
([PrintAudit] = @Original_PrintAudit) AND ([ExcludeBase] =
@Original_ExcludeBase) AND ([ReportDirectory] =
@Original_ReportDirectory) AND ([AuditFrequencyType] =
@Original_AuditFrequencyType) AND ([AuditFrequencyUnits] =
@Original_AuditFrequencyUnits) AND ([CASSTemplateFirstClass] =
@Original_CASSTemplateFirstClass) AND ([CASSTemplateBulk] =
@Original_CASSTemplateBulk) AND ([IconFileName] =
@Original_IconFileName))

I don't understand why all columns have no value. There is data in the
row which I can see in the above event.

ctype(e.Row, dsclient.clientrow) shows me that data is there.

 

[WenYuan Wang]

Hi Flomo,
Thanks for your reply.

Each tableAdapter have three commands(insert,update and delete).
TableAdapter.update method will check the RowState property of each row in
DataTable. If the RowState is "Modified" update command will be called
..Insert command will be called if the RowState is "Added" and Delete
command will be called if the RowState is "Deleted". Thus, there will will
be no parameters in Delete command if the RowState is "Added" or
"Modified". Because I noticed that Concurrency violation exception said
"DeleteCommand affected 0 of the expected 1records" in your first post, for
this reason, I suggest you can catch the deleteCommand and output each
parameter to drill down this issue.

But if deleteCommand. Parameter can not provide any information.We would
like to do the following two steps to trouble-shot this issue.
At First, please check the rowState of e.row in the taRequest_RowUpdating
event.
Second, we suggest you can output all commands (insert, update and delete)
to get detailed information.

Please add the following three methods in your class and call these methods
in the taRequest_RowUpdating event. Would you please also paste the output
information and we can perform further analysis. Many thanks

Private Sub showDeleteCommand()
Console.WriteLine("=====Begin of DeleteCommand")
Dim iCount As Integer
Dim str As String = String.Empty
For iCount = 0 To _adapter.DeleteCommand.Parameters.Count - 1
If _adapter.DeleteCommand.Parameters(iCount).Value Is
Nothing Then
str = "<null>"
Else
str =
_adapter.DeleteCommand.Parameters(iCount).Value.ToString()
End If

Console.WriteLine(_adapter.DeleteCommand.Parameters(iCount).ToString() & "=
" & str)
Next
Console.WriteLine(_adapter.DeleteCommand.CommandText)
Console.WriteLine("=====End of DeleteCommand")
End Sub

Private Sub showUpdateCommand()
Console.WriteLine("=====Begin of UpdateCommand")
Dim iCount As Integer
Dim str As String = String.Empty
For iCount = 0 To _adapter.UpdateCommand.Parameters.Count - 1
If _adapter.UpdateCommand.Parameters(iCount).Value Is
Nothing Then
str = "<null>"
Else
str =
_adapter.UpdateCommand.Parameters(iCount).Value.ToString()
End If

Console.WriteLine(_adapter.UpdateCommand.Parameters(iCount).ToString() & "=
" & str)
Next
Console.WriteLine(_adapter.UpdateCommand.CommandText)
Console.WriteLine("=====End of UpdateCommand")
End Sub

Private Sub showInsertCommand()
Console.WriteLine("=====Begin of InsertCommand")
Dim iCount As Integer
Dim str As String = String.Empty
For iCount = 0 To _adapter.InsertCommand.Parameters.Count - 1
If _adapter.InsertCommand.Parameters(iCount).Value Is
Nothing Then
str = "<null>"
Else
str =
_adapter.InsertCommand.Parameters(iCount).Value.ToString()
End If

Console.WriteLine(_adapter.InsertCommand.Parameters(iCount).ToString() & "=
" & str)
Next
Console.WriteLine(_adapter.InsertCommand.CommandText)
Console.WriteLine("=====End of InsertCommand")
End Sub
End Class


Private Sub taRequest_RowUpdating(ByVal sender As Object, ByVale As
System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles _adapter.RowUpdating

Console.WriteLine("***" + e.Row.RowState.ToString() + "***")
showDeleteCommand()
showUpdateCommand()
showInsertCommand()
End Sub

Hope this helps,
Best Regards,
Wen Yuan

 

[Flomo Togba Kwele]

WenYuan,

I am sorry to say that I've given up on this approach for right now. I
went back to my app and I couldn't even get to add an item.

I'll revisit it later. Thanks for all your help.

 

[WenYuan Wang]

Hi Flomo,
Thanks for your reply.

I'm very glad to assist you. You are welcome. Please feel free let me know
if you want to continue work on this issue .

Have a great weekend.
Best regards,
Wen Yuan