FSO with Database

[F]

Hi all

I have some difficulty to manage FSO with Database.

Actually I am building a tree view of user files and folders.

I have saved record of each file and folder with its parent id in a table
for example

Table
------------------
Name --ID--ParentID
-------------------
Root--1--0
Mydoc--2--1
Mypic--3--2
songs--4--2
mov--5--2
eng--6--5
ger--7--5
fra--8--5
engus--9--6
enguk--10--6
mp3--11--2
wm--12--2
--------------------
If I delete folder name ="mov" ID="5" ParentID=2. I have to delete

1. Physically from hard disk folder name = "mov" (and all its child will be
deleted automatically)
2. All records from table which related to this folder so record no with ID
6,7,8,9,10 should be deleted from table.

But I am facing difficulty with deleting records 6,7,8,9,10 as dont have
idea to get these IDs

Any advise from ASP gurus.

Regards
Fayayz

[Aaron Bertrand [MVP]]

DELETE table WHERE ParentID = 5
OR ParentID IN (SELECT ID FROM table WHERE ParentID = 5)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"F@yy@Z" <fa**********@mvwebmaker.com> wrote in message
news:O5**************@TK2MSFTNGP12.phx.gbl...

Hi all

I have some difficulty to manage FSO with Database.

Actually I am building a tree view of user files and folders.

I have saved record of each file and folder with its parent id in a table
for example

Table
------------------
Name --ID--ParentID
-------------------
Root--1--0
Mydoc--2--1
Mypic--3--2
songs--4--2
mov--5--2
eng--6--5
ger--7--5
fra--8--5
engus--9--6
enguk--10--6
mp3--11--2
wm--12--2
--------------------
If I delete folder name ="mov" ID="5" ParentID=2. I have to delete

1. Physically from hard disk folder name = "mov" (and all its child will
be
deleted automatically)
2. All records from table which related to this folder so record no with
ID
6,7,8,9,10 should be deleted from table.

But I am facing difficulty with deleting records 6,7,8,9,10 as dont have
idea to get these IDs

Any advise from ASP gurus.

Regards
Fayayz

[F]

Thanks for immidiate reply.

What about record no 9,10 and what if some record have parent ID 9 or 10 and
if that have more childs.

Please look into this and advise.

regards

Fayyaz
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:OJ**************@tk2msftngp13.phx.gbl...

DELETE table WHERE ParentID = 5
OR ParentID IN (SELECT ID FROM table WHERE ParentID = 5)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"F@yy@Z" <fa**********@mvwebmaker.com> wrote in message
news:O5**************@TK2MSFTNGP12.phx.gbl...

Hi all

I have some difficulty to manage FSO with Database.

Actually I am building a tree view of user files and folders.

I have saved record of each file and folder with its parent id in a table for example

Table
------------------
Name --ID--ParentID
-------------------
Root--1--0
Mydoc--2--1
Mypic--3--2
songs--4--2
mov--5--2
eng--6--5
ger--7--5
fra--8--5
engus--9--6
enguk--10--6
mp3--11--2
wm--12--2
--------------------
If I delete folder name ="mov" ID="5" ParentID=2. I have to delete

1. Physically from hard disk folder name = "mov" (and all its child will
be
deleted automatically)
2. All records from table which related to this folder so record no with
ID
6,7,8,9,10 should be deleted from table.

But I am facing difficulty with deleting records 6,7,8,9,10 as dont have
idea to get these IDs

Any advise from ASP gurus.

Regards
Fayayz

[Aaron Bertrand [MVP]]

How many levels do you allow? You will have to run that many subqueries, I
think. Or, add a column indicating TOP and delete where top = 5.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"F@yy@Z" <fa**********@mvwebmaker.com> wrote in message
news:O3****************@TK2MSFTNGP09.phx.gbl...

Thanks for immidiate reply.

What about record no 9,10 and what if some record have parent ID 9 or 10
and
if that have more childs.

Please look into this and advise.

regards

Fayyaz

[F]

There is no limit of levels, and If I put a column "TOP" what about if some
one delete any child of this top.

This is very strange situation :(

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:u5**************@TK2MSFTNGP12.phx.gbl...

How many levels do you allow? You will have to run that many subqueries, I think. Or, add a column indicating TOP and delete where top = 5.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"F@yy@Z" <fa**********@mvwebmaker.com> wrote in message
news:O3****************@TK2MSFTNGP09.phx.gbl...

Thanks for immidiate reply.

What about record no 9,10 and what if some record have parent ID 9 or 10
and
if that have more childs.

Please look into this and advise.

regards

Fayyaz

[Manohar Kamath [MVP]]

Trees with unlimited levels are always tricky. The key is in when you
actually store the record, and not as much in SQL script itself. Consider
using an additional column of text type. Here's an example

Folder ID Parent CompositeParentKey
---------------------------------------
FolderA 1 0 0/
FolderB 2 1 0/1/
FolderC 3 2 0/1/2/
FolderD 4 2 0/1/2/
FolderX 5 3 0/1/2/3/
FolderY 6 3 0/1/2/3/
FolderI 8 6 0/1/2/3/6/
FolderJ 9 6 0/1/2/3/6/

If you notice, the composite is basically = Parent Composite Parent Key +
ParentKey + "/"

so for FolderC, the compositeParentKey is "0/1/" + 2 + "/"

You need to establish this key when you first write the record out. So, it
is a little work upfront, but makes your life easier later.

Consider you want to delete FolderC, you also need to delete folderX and
folderY. The way you do this is, for folderC, get the CompositeParenKey +
its own key

Key = "0/1/2/" + 3 + "/"

Now, delete all folders that have this to begin

DELETE FROM Folders WHERE CompositeParentKey LIKE '0/1/2/3/%'

And then delete FolderC itself. Hope this helps.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"F@yy@Z" <fa**********@mvwebmaker.com> wrote in message
news:O5**************@TK2MSFTNGP12.phx.gbl...

Hi all

I have some difficulty to manage FSO with Database.

Actually I am building a tree view of user files and folders.

I have saved record of each file and folder with its parent id in a table
for example

Table
------------------
Name --ID--ParentID
-------------------
Root--1--0
Mydoc--2--1
Mypic--3--2
songs--4--2
mov--5--2
eng--6--5
ger--7--5
fra--8--5
engus--9--6
enguk--10--6
mp3--11--2
wm--12--2
--------------------
If I delete folder name ="mov" ID="5" ParentID=2. I have to delete

1. Physically from hard disk folder name = "mov" (and all its child will b e deleted automatically)
2. All records from table which related to this folder so record no with ID 6,7,8,9,10 should be deleted from table.

But I am facing difficulty with deleting records 6,7,8,9,10 as dont have
idea to get these IDs

Any advise from ASP gurus.

Regards
Fayayz