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

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

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.

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

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.
On Wed, 31 Jan 2007 09:40:09 GMT, v-wyw***@online.microsoft.com
("WenYuan Wang") wrote:

Show quoteHide quote

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

Close not closing...
Queries to an Access db from vs2005
MS SQL 2005 Configuration ???
Problem running query in code
Validating an XML file