Problems with Array, DataReader, etc

wabs27

Member
Joined
Jul 31, 2006
Messages
11
Programming Experience
5-10
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:

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]
 
I don't understand why you're messing with array here since you can't make an array hierarchic and bind it to Treeview anyway. I see you create Treenodes while reading data and that you don't add them to a Treeview. When you get linear data that contains structural information from a data source you must analyze it and add treenodes in relation to the parent-child information of recieved data. Depending on the parent-child data received you may perhaps have to do lookups to find the treenode to which the new treenode should append to.
 
I don't understand why you're messing with array here since you can't make an array hierarchic and bind it to Treeview anyway. I see you create Treenodes while reading data and that you don't add them to a Treeview. When you get linear data that contains structural information from a data source you must analyze it and add treenodes in relation to the parent-child information of recieved data. Depending on the parent-child data received you may perhaps have to do lookups to find the treenode to which the new treenode should append to.

Hi John,
Thanks for answering. I'm a bit confused though. The reason I used an array is because I can not have multiple datareaders open at one time. You mentioned
I see you create Treenodes while reading data and that you don't add them to a Treeview.
. The code to bind the created treenode to the treeview is the recursive function that was commented out:
VB.NET:
[SIZE=2][COLOR=#008000]
RunDR(TempRecord.yID, TempRecord.ThisNode)
[/COLOR][/SIZE]

Anyways, after playing around a little I noticed that my whole problem had to do with arrays. For some reason I can't append an object to an array. I tried substituting it with a string and it worked. My only problem is that I need more information. So my quick way around the problem would be to use multiple arrays instead of having one array with an object. I am not sure what you meant about hierarchic arrays. Would you be able to explain in simpler terms?

To summarize:

Doesn't work:
VB.NET:
[SIZE=2][COLOR=#008000]
MyArray(i) = ArrayRecord
'ArrayRecord is an instance of object TreeArray
[/COLOR][/SIZE]

Works:
VB.NET:
[SIZE=2][COLOR=#008000]
MyArray(i) = CatName
'CatName is a string
[/COLOR][/SIZE]

Does that make sense? Is there a way to make the first case work with an object?

Thanks.
 
Do you mean if you get all without the where clause it returns childs before parents so you have to reorder or work it from array?? That could be a possibility, I guess.

MyArray is strongly typed and same type as ArrayRecord so is not a problem adding that instance to the array, but your counting and redimming is strange order.
 
Back
Top