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