I've ran into a problem that's been driving me crazy.
To introduce, I have a self-reference table in my database to manage categories. For instance:
ID, Name, Path
Where path references the the ID of another record in the same table.
Now, I am trying to create a treeview of all the categories, sub-categories, sub-sub, etc.
I initially got it to work using a recursive function. I would open a datareader and add to the treeview from the datareader. This worked when I used sample data, but when I used live data, I got an error message that I had too many datareaders open (makes sense because recursively the datareaders don't close until all recursion).
So instead of using datareaders I tried with arrays. My problem was that for some reason all the entries (or records, or members - not sure what it's called in the array world) got the value of the last entry added to the array. I tried debugging with F8 and noticed that instead of appending one new record to the array, it changed the values of the whole array. My code seemed right to me but maybe someone can find out what was wrong with it or another method around this.
Thanks:
To introduce, I have a self-reference table in my database to manage categories. For instance:
ID, Name, Path
Where path references the the ID of another record in the same table.
Now, I am trying to create a treeview of all the categories, sub-categories, sub-sub, etc.
I initially got it to work using a recursive function. I would open a datareader and add to the treeview from the datareader. This worked when I used sample data, but when I used live data, I got an error message that I had too many datareaders open (makes sense because recursively the datareaders don't close until all recursion).
So instead of using datareaders I tried with arrays. My problem was that for some reason all the entries (or records, or members - not sure what it's called in the array world) got the value of the last entry added to the array. I tried debugging with F8 and noticed that instead of appending one new record to the array, it changed the values of the whole array. My code seemed right to me but maybe someone can find out what was wrong with it or another method around this.
Thanks:
VB.NET:
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] TreeArray[/SIZE]
[SIZE=2][COLOR=#0000ff] Public[/COLOR][/SIZE][SIZE=2] yID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff] Public[/COLOR][/SIZE][SIZE=2] ThisNode [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] TreeNode[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] RunDR([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] YPath [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] MyNode [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] TreeNode)[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] MyArray(5) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] TreeArray[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] ArrayRecord [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] TreeArray = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] TreeArray[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0[/SIZE]
[SIZE=2] YPath = YPath.ToUpper[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] sSQL [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Select yID, CategoryName, yPath From Cats WHERE ucase(ypath)='"[/COLOR][/SIZE][SIZE=2] & YPath & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] DR [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataReader[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] con [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbConnection[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand()[/SIZE]
[SIZE=2] con = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(ConStr)[/SIZE]
[SIZE=2] cmd.Connection = con[/SIZE]
[SIZE=2] cmd.CommandText = sSQL[/SIZE]
[SIZE=2] con.Open()[/SIZE]
[SIZE=2] DR = cmd.ExecuteReader()[/SIZE]
[SIZE=2][COLOR=#0000ff] While[/COLOR][/SIZE][SIZE=2] DR.Read[/SIZE]
[SIZE=2][COLOR=#0000ff] If[/COLOR][/SIZE][SIZE=2] DR.GetValue(DR.GetOrdinal([/SIZE][SIZE=2][COLOR=#800000]"ypath"[/COLOR][/SIZE][SIZE=2])) = YPath [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] NewNode [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] TreeNode = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] TreeNode[/SIZE]
[SIZE=2][COLOR=#0000ff] Dim[/COLOR][/SIZE][SIZE=2] CatName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = DR.GetValue(DR.GetOrdinal([/SIZE][SIZE=2][COLOR=#800000]"CategoryName"[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2] NewNode = MyNode.Nodes.Add(CatName)[/SIZE]
[SIZE=2] NewNode.Tag = DR.GetValue(DR.GetOrdinal([/SIZE][SIZE=2][COLOR=#800000]"yid"[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2][COLOR=#008000] 'Get all subcategories[/COLOR][/SIZE][COLOR=#008000]
[/COLOR][SIZE=2][COLOR=#008000] 'MyArray(i).ThisNode = NewNode[/COLOR][/SIZE][COLOR=#008000]
[/COLOR][SIZE=2][COLOR=#008000] 'MyArray(i).yID = NewNode.Tag[/COLOR][/SIZE][COLOR=#008000]
[/COLOR][SIZE=2] ArrayRecord.yID = NewNode.Tag[/SIZE]
[SIZE=2] ArrayRecord.ThisNode = NewNode[/SIZE]
[SIZE=2] MyArray(i) = ArrayRecord[/SIZE]
[SIZE=2] i = i + 1[/SIZE]
[SIZE=2] ReDim[/SIZE][SIZE=2]Preserve[/SIZE][SIZE=2] MyArray(i + 2)[/SIZE]
[SIZE=2][COLOR=#008000] 'RunDR(YId, NewNode)[/COLOR][/SIZE][COLOR=#008000]
[/COLOR][SIZE=2][COLOR=#0000ff] End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff] End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE]
[SIZE=2]DR.Close()
con.Close()
[/SIZE][SIZE=2][COLOR=#008000]'I added this section with the textbox as a test
[/COLOR][/SIZE][SIZE=2]TextBox1.Text = [/SIZE][SIZE=2][COLOR=#800000]"i, yid, text, tag"[/COLOR][/SIZE][SIZE=2] & vbCrLf & vbCrLf[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] j [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] j = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] (i - 1)[/SIZE]
[SIZE=2][COLOR=#008000]'Each ArrayRecord In MyArray
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] TempRecord [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] TreeArray[/SIZE]
[SIZE=2]TempRecord = MyArray(j)
[/SIZE][SIZE=2][COLOR=#008000]'RunDR(TempRecord.yID, TempRecord.ThisNode)
[/COLOR][/SIZE][SIZE=2]TextBox1.Text += j & [/SIZE][SIZE=2][COLOR=#800000]","[/COLOR][/SIZE][SIZE=2] & TempRecord.yID & [/SIZE][SIZE=2][COLOR=#800000]", "[/COLOR][/SIZE][SIZE=2] &
TempRecord.ThisNode.Text & [/SIZE][SIZE=2][COLOR=#800000]", "[/COLOR][/SIZE][SIZE=2] & TempRecord.ThisNode.Tag & vbCrLf[/SIZE]
[SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/COLOR][/SIZE]