Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 25, 2012 08:40 PM by mattcase
May 24, 2012 05:39 PM|LINK
I have a table that includes a list of all the folders in an account. In my table I have a "FolderID" column and a "ParentFolderID" column.
When a user creates a new subfolder, it takes the Parent Folder's ID (FolderID) and writes it to the "ParentFolderID" column. This relates the subfolder to the Parent Folder.
When they create a Sub-sub-Folder, it takes the Sub-folder's ID and writes it to the "ParentFolderID" column.
Each user is allowed to create as many subfolders as they would like.
My question is, when they want to delete a Parent Folder, what query can I use to find all the sub-folders that are located underneath it?
May 24, 2012 05:49 PM|LINK
You'll do something called a Recursive CTE. here is a simple example, and you can google Recursive CTE for lots more:
May 24, 2012 07:23 PM|LINK
Thanks! I would have never figured that out.
I got the Recursive CTE to work perfectly (tested it on my SQL server). However, when I try to convert the dataset to an array I get the following error:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Line 107: ' of our String instead of the Len() function
Line 109: For a As Integer = 1 To m_plaintext.Length Line 110:
Line 111: ' Initialize an integer variable we will use in this loop
Here is my code:
Dim connstrNow As String = ConfigurationManager.ConnectionStrings("String").ToString()
Dim sqlStrNow As String = "WITH FolderCount AS (Select Column1 FROM Table1 WHERE Column2 Is NULL AND Column1 = '" & Variable1 & "' UNION ALL Select e.Column1 FROM Table1 e INNER JOIN FolderCount ecte ON ecte.Column1 = e.Column2) SELECT * FROM FolderCount"
Dim dtNow As New DataSet
Dim dataAdapterNow As New SqlClient.SqlDataAdapter(sqlStrNow, connstrNow)
Dim count1 As Integer = dtNow.Tables("matches").Rows.Count
Dim j As Integer
For j = 0 To count1 - 1
Dim b As Integer
b = dtNow.Tables("matches").Rows(j).Item(0)
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim Var4 As String
Dim AccountString As String
AccountString = ConfigurationManager.ConnectionStrings("String").ToString()
Dim SqlQueryAcc As New SqlConnection(AccountString)
Dim ResultsAcc As New SqlCommand("SELECT [Column1], [Column2], [Column3], [Column4] FROM Table3 WHERE ([Column5] = @Column5)", SqlQueryAcc)
ResultsAcc.Parameters.AddWithValue("@Column3", b).Value = b
Dim r As SqlDataReader = ResultsAcc.ExecuteReader()
If Not r("Column1").Equals(DBNull.Value) Then
Var4 = CStr(r("Column1"))
If Not r("Column2").Equals(DBNull.Value) Then
Var1 = CStr(r("Column2"))
If Not r("Column3").Equals(DBNull.Value) Then
Var3 = CStr(r("Column3"))
If Not r("Column4").Equals(DBNull.Value) Then
Var2 = CStr(r("Column4"))
May 24, 2012 07:38 PM|LINK
I don't see the line with the error: For a As Integer = 1 To m_plaintext.Length
in the code you posted. It must be in another part of your code. Judging from the error, I'd guess that m_plaintext is not getting instantiated for some reason. But I don't even see "m_plaintext" in the code you posted.
May 24, 2012 07:49 PM|LINK
You are correct.
Nowhere in my code at all is there any m_plaintext.length or anything like it. Apparently this is something happening in .Net when it iterates through my SQL. When I comment out only the ArrayList section, the error goes away.
I have no idea why it is doing that.
However, the arraylist is necessary so that I can pull the Column1 out of the Recursive query and add it as a paremeter in my SQL DELETE statement.
May 24, 2012 08:03 PM|LINK
I figured it out. It was something else totally unrelated to the SQL statement in one of my class files.
Thanks again for your incredible help!
May 25, 2012 03:54 PM|LINK
I have a follow-up to the same question. I have it working great for the original post. However, I am trying to modify it so that I can use it in a different way and I am having trouble.
What I want to do is take the current folder that a user is in and select all parent folders up to the highest point then list them as quick links to go back to any given parent folder.
It doesn't seem to be working regardless of how I adjust it.
What are your thoughts?
The following statement works in the it will do what I need it to, except that it stops working when I get down to the last folder in the chain. In addition, it shows both the current folder that the user is in and also its immediate sub-folder (child folder).
WITH FolderCount AS (Select FolderID FROM Table1 WHERE ParentFolderID IS NULL AND FolderID = '" & FolderID & "' UNION ALL Select e.FolderID FROM Table1 e INNER JOIN FolderCount ecte ON ecte.FolderID = e.ParentFolderID) SELECT FolderID FROM FolderCount
May 25, 2012 04:51 PM|LINK
I'm not sure I understand your question, but you can make the recursive CTE go in either direction by reversing the JOIN. Doing it this way will start with a child and return all the parents:
WITH FolderCount AS (
Select FolderID, ParentFolderID
WHERE FolderID = '" & FolderID & "'
Select e.FolderID, ParentFolderID
FROM Table1 e
INNER JOIN FolderCount ecte
ON ecte.ParentFolderID = e.FolderID
) SELECT FolderID FROM FolderCount
May 25, 2012 08:14 PM|LINK
That was exactly what I needed! Thanks!
Along that same note, I am trying to create a bread crumb trail of folder quick links as they descend into their subfolders. Like the following:
Parent Folder Name - Child Folder Name 1 - Child Folder Name 2 - etc., etc.
It seems to be working with what I have so far, except that it is not breaking up each folder's link into a seperate link. It is creating one single link for them all.
Do you know how I could resolve this?
Here is what I have so far:
<a id="FolderString" runat="server" visible="false" style="color: Blue"></a>
''''''''Server side code
Dim count2 As Integer = dt2b.Rows.Count
Dim j2 As Integer
For j2 = 0 To count2 - 1
If dt2b.Rows.Count <> 0 Then
FolderString.Visible = True
FolderString.InnerText += FolderName2
FolderString.HRef += "WebPage.aspx?folder=" & FolderID
FolderString.InnerText += " - "
May 25, 2012 08:40 PM|LINK
Looks like I figured it out!
Thanks for your help!