Cascading Delete

11 Apr 2007 3:26 PM

Jason Weier

I am having a strange issue with cascading deletes in that it simply isn't
cascading. Heres what I'm using:

VB .NET 2005 sp1
SQL Express 2005

For simplicity, I will give 2 of the many table adapters in the app and
their relations:

Sites
-----
(PK) Name
ContractType
ContractExperation
Address1
Address2
City
State
Zip
Phone
Fax
VPNType
VPNName
VPNUser
VPNPass
VPNNotes
VPNHostOrIP
VPNUserConfigFile
VPNFileName
VPNFile
Office
--------------

Servers
--------
(FK)Site
(PK)Name
ServerRole
IPAddress
NetworkType
ProtocolType
DNS
WINS
Gateway
LocalUser
LocalPass
Location
--------------

Relationship
------------
(P)Sites:Name<----------(C)Servers:Site
Both Relational and Foreign Key Constraint
Update Rule:Cascade
Delete Rule:Cascade
Accept/Reject Rule:Cascade

I use an SQL command as part of the DataTable in the designer called
FilLByOffice to load the dataset for a given office name for the Sites
DataTable.
These results are populated into a TreeView

I use an SQL command called FillBySite to populate the Servers DataTable for
the given Site.
These results are populated into a ListView

I am able to create records, and looking at the Database itself I see that
they records are being saved correctly.

However, when I run the SQL Command DeleteByName(@Name) from the Sites
DataTable (in code), the cascade does not work, leaving orphan records in
Servers. Thus, the next time the Servers DataTable is loaded, an error occurs
because there is not a parent record in Sites (the parent record was deleted
correctly).

I cleared all other relationships to rule out problems on that end
(ultimately I would like to have a few cascading deletes) and am still having
a problem. What could be causing this? Any help would be greatly appreciated,
I have not been able to find a similar problem by searching several forums
and knowledgebases.

13 Apr 2007 11:36 AM

pvdg42

"Jason Weier" <Jason We***@discussions.microsoft.com> wrote in message
news:C6896A21-A6FF-49A4-9069-A37D5ADAA2A6@microsoft.com...
>I am having a strange issue with cascading deletes in that it simply isn't
> cascading. Heres what I'm using:
>
> VB .NET 2005 sp1
> SQL Express 2005
>

<snip>

Does the same thing happen if you run the DELETE query directly in the
database (independent of your application), or does it work correctly? The
results will determine if it's a SQL Server problem or an ADO.NET/your
application problem.

13 Apr 2007 1:05 PM

Miha Markic

Hi Jason,

a) you should have relations defined at database level, too. Otherwise your
data might become inconsistent as it happens to you.
b) I don't think that any command will do cascade delete for you (unless
cascade delete relation is active at server). Rather, those cascade deletes
mean that when you delete a row from DataTable Sites it will mark
appropriate rows in DataTable Servers as Deleted, too.
If you want to commit deletes to database then you have to invoke
adapter.Update on Servers and then Update on Sites (note that if you have
mixed row states then it gets a bit more complicated)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quoteHide quote

"Jason Weier" <Jason We***@discussions.microsoft.com> wrote in message
news:C6896A21-A6FF-49A4-9069-A37D5ADAA2A6@microsoft.com...
>I am having a strange issue with cascading deletes in that it simply isn't
> cascading. Heres what I'm using:
>
> VB .NET 2005 sp1
> SQL Express 2005
>
> For simplicity, I will give 2 of the many table adapters in the app and
> their relations:
>
> Sites
> -----
> (PK) Name
> ContractType
> ContractExperation
> Address1
> Address2
> City
> State
> Zip
> Phone
> Fax
> VPNType
> VPNName
> VPNUser
> VPNPass
> VPNNotes
> VPNHostOrIP
> VPNUserConfigFile
> VPNFileName
> VPNFile
> Office
> --------------
>
> Servers
> --------
> (FK)Site
> (PK)Name
> ServerRole
> IPAddress
> NetworkType
> ProtocolType
> DNS
> WINS
> Gateway
> LocalUser
> LocalPass
> Location
> --------------
>
> Relationship
> ------------
> (P)Sites:Name<----------(C)Servers:Site
> Both Relational and Foreign Key Constraint
> Update Rule:Cascade
> Delete Rule:Cascade
> Accept/Reject Rule:Cascade
>
> I use an SQL command as part of the DataTable in the designer called
> FilLByOffice to load the dataset for a given office name for the Sites
> DataTable.
> These results are populated into a TreeView
>
> I use an SQL command called FillBySite to populate the Servers DataTable
> for
> the given Site.
> These results are populated into a ListView
>
> I am able to create records, and looking at the Database itself I see that
> they records are being saved correctly.
>
> However, when I run the SQL Command DeleteByName(@Name) from the Sites
> DataTable (in code), the cascade does not work, leaving orphan records in
> Servers. Thus, the next time the Servers DataTable is loaded, an error
> occurs
> because there is not a parent record in Sites (the parent record was
> deleted
> correctly).
>
> I cleared all other relationships to rule out problems on that end
> (ultimately I would like to have a few cascading deletes) and am still
> having
> a problem. What could be causing this? Any help would be greatly
> appreciated,
> I have not been able to find a similar problem by searching several forums
> and knowledgebases.