Concurrency violation: The DeleteCommand affected 0 recordss

[TheNortonZ]

I am having a concurrency problem with a child table that I can't seem to
figure out.

I have a parent table 'ProposedCost'. Its primary key is a guid.
I have a child table 'TermsAndConditions', it has a primary key that is a
guid and a foreign key to the ProposedCost table.
I have a grandchild table 'TermsConditionsYears' that has a primary key that
is a guid and a foreign key to the guid primary key of
'TermsAndConditions'.

I can insert and update records with no problem through data adapters. For
example, for the ProposedCost table, my code looks like this:

Dim adpProposedCost As SqlDataAdapter
Try

'################# adapter for Proposed Cost
#####################################
adpProposedCost = New SqlDataAdapter
SetupProposedCostDeleteCommand(adpProposedCost, conn)
SetupProposedCostInsertCommand(adpProposedCost, conn)
SetupProposedCostUpdateCommand(adpProposedCost, conn)

With adpProposedCost
.Update(ds, "ProposedCosts")
End With
Catch ex As Exception
Throw New Exception(ex.Message)
Return False
End Try

Within my code, when a proposed cost item is deleted, I have a looping
structure setup to use the guid from the ProposedCost table and delete the
associated TermsAndConditions records. Within the loop for deleting any
TermsAndCondition records, I have code to delete any associated
TermsConditionsYears records that relate to the TermsAndConditions table.

The TermsConditionsYears table has 3 records for each TermsAndConditions ID.

When I execute my .Update statement for the TermsAndConditionsYears
(grandchild table), I get the following error:
Concurrency Violation: the DeleteCommand affected 0 records

I have tried putting the code for updating the child records ahead of the
code for the parent table, but I get the same results.

The stored procedure for my delete looks like this:
----------------------------------------------------------------------
CREATE PROCEDURE dbo.DeleteTermsAndConditionsYear
(
@uidTermsConditionsID uniqueidentifier
)

AS

BEGIN
DELETE FROM tblTermsAndConditionsYear
WHERE tblTermsAndConditionsYear.TermsConditionsID = @uidTermsConditionsID

END
-----------------------------------------------------------------------

Either this problem is right in front of my face and I can't see it or I
have missed some concept.

By the way, the ProposedCost and TermsAndConditions records don't have any
problem during deletion.

Any ideas?

Thanks.

Norton

 

[TheNortonZ]

Well, after many days of battle, it seems I have corrected this problem.

What I did was, I created a relationship (using a SQL Server diagram)
between TermsAndConditions and TermsConditionsYears whereby it allowed
cascaded deletes. Before where I was actually calling a delete stored
procedure for the TermsConditionsYears records, I now just delete the rows
as you normally would in the code.

Now, actually I'm going to be using MSDE, so if I find out cascaded deletes
don't work in msde, I'm screwed.

Norton