Clark Posted February 13, 2012 Share Posted February 13, 2012 (edited) Hello all I have the feeling my brain is getting old, as I cannot figure out how to achieve the following. I wish to display a list of folders in a treeview heirarchical structure. I am retrieving the folders from a database with the attributes of folder_id, folder_id_2 (which is the parent folder id), and the folder name (which is used for display purposes). Firstly, here is the code for retrieving the folder structure from the database. To me, it seems inelegant, but I can't work out how to elengatize it. expandcollapse popup$sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = 7;" ; top level defect folders $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) if $iRval <> $SQL_OK then Msgbox(0 + 16 +262144,"Error","Unable to populate folder listbox") _SQL_Close() Exit EndIf for $j = 1 to $gRows ; for each high level folder $agArray[1][$j][0]=$ahArray[$j][0] ; Folder ID $agArray[1][$j][1]=$ahArray[$j][1] ; Folder name $agArray[1][$j][2]=7 ; Parent folder Next ; Now we go to the second level for $j = 1 to $gRows $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[1][$j][0] & ";" $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) $agArray[2][$j][0]=$ahArray[$j][0] ; Folder ID $agArray[2][$j][1]=$ahArray[$j][1] ; Folder name $agArray[2][$j][2]=$agArray[1][$j][0] ; Parent folder Next ; Now for the third level for $j = 1 to $gRows $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[2][$j][0] & ";" $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) $agArray[3][$j][0]=$ahArray[$j][0] ; Folder ID $agArray[3][$j][1]=$ahArray[$j][1] ; Folder name $agArray[3][$j][2]=$agArray[2][$j][0] ; Parent folder Next ; and the fourth level for $j = 1 to $gRows $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[3][$j][0] & ";" $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) $agArray[4][$j][0]=$ahArray[$j][0] ; Folder ID $agArray[4][$j][1]=$ahArray[$j][1] ; Folder name $agArray[4][$j][2]=$agArray[3][$j][0] ; Parent folder Next ; and the Fifth level for $j = 1 to $gRows $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[4][$j][0] & ";" $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) $agArray[5][$j][0]=$ahArray[$j][0] ; Folder ID $agArray[5][$j][1]=$ahArray[$j][1] ; Folder name $agArray[5][$j][2]=$agArray[4][$j][0] ; Parent folder Next ; and the sixth and final level for $j = 1 to $gRows $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[5][$j][0] & ";" $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) $agArray[6][$j][0]=$ahArray[$j][0] ; Folder ID $agArray[6][$j][1]=$ahArray[$j][1] ; Folder name $agArray[6][$j][2]=$agArray[5][$j][0] ; Parent folder Next Apart from the elegance issue, here is where I run into trouble, converting it into a treeview. First I tried this. Yes, I know this wont work as uBound doesn't work like this. In this context I was trying to use uBound to determine the number of rows to display at each level. Obvious fail. GUICtrlCreateButton("Folders ",144,40,257,15) $hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR( $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES)) for $j = 1 to (uBound($agArray)-1) ; For all the rows in the array $tv1=GUICtrlCreateTreeViewItem($agArray[1][$j][1], $hTreeView) ; Top level for $j = 1 to (UBound($agArray,3)-1) $tv2=GUICtrlCreateTreeViewItem($agArray[2][$j][1], $tv1) ; Second level items for $j = 1 to (UBound($agArray,4)-1) $tv3=GUICtrlCreateTreeViewItem($agArray[3][$j][1], $tv2) ; Third level items for $j = 1 to (UBound($agArray,5)-1) $tv4=GUICtrlCreateTreeViewItem($agArray[4][$j][1], $tv3) ; Fourth level items for $j = 1 to (UBound($agArray,6)-1) $tv5=GUICtrlCreateTreeViewItem($agArray[5][$j][1], $tv4) ; Fifth level items for $j = 1 to (UBound($agArray,7)-1) GUICtrlCreateTreeViewItem($agArray[6][$j][1], $tv5) ; Sixth level items Next Next Next Next Next Next Next, I tried this, but this doesn't work either. (I haven't ran the code, I just can tell it wont work looking at it) GUICtrlCreateButton("Folders ",144,40,257,15) $hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR( $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES)) for $j = 1 to (uBound($agArray)-1) ; For all the rows in the array $tv1=GUICtrlCreateTreeViewItem($agArray[1][$j][1], $hTreeView) ; Top level $Parent=$agArray[1][$j][0] for $j = 1 to (uBound($agArray)-1) ; Check all rows to see if its a child if $agArray[2][$j][2]=$Parent Then $tv2=GUICtrlCreateTreeViewItem($agArray[1][$j][1], $hTreeView) EndIf etc etc for each level Next Next Is anyone able to offer any suggestions? I've been on this two days and not really getting anywhere. Edited February 13, 2012 by Clark Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 13, 2012 Moderators Share Posted February 13, 2012 Clark,Can you please post an example of the $iRval array and a rough diagram of what you want the final TreeView to look like. Trying to code something without those is far too likely to be wasted time as it probably will not meet the real-world conditions with which you are working. M23 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Tvern Posted February 13, 2012 Share Posted February 13, 2012 You could do this with a recursive function. I assume your data will never have more than a dozen of levels, so recursion errors should not be a problem. Here is an example, but it's untested and you'll have to add the SQL stuff and GUI first: $hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR( $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES)) _FillTree_Rec($hTreeView) Func _FillTree_Rec($hParent, $iFID = 7) Local $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $iFID & ";" ;query to select children of the passed folder ID Local $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) ;fetch to array ;it's probably best to check if any values are returned here and return from the function if not. (I wasn't sure if there is a return count in $iRval[0][0]) Local $hItem For $i = 1 To UBound($iRval) -1 $hItem=GUICtrlCreateTreeViewItem($iRval[$i][1], $hParent) ;create item for each child _FillTree_Rec($hItem, $iRval[$i][0]) ;run again with the last child as parent. Next EndFunc Link to comment Share on other sites More sharing options...
Clark Posted February 14, 2012 Author Share Posted February 14, 2012 Thanks guys I shall subject my brain to another day of torture whilst I work through Tvern's solution. If I can't get anywhere I will post back with more info as per Melba's request. I'm sure when I was younger my brain could have done this no problems. regards Clark Link to comment Share on other sites More sharing options...
Clark Posted February 14, 2012 Author Share Posted February 14, 2012 Right, well after some experiment and more brain torture, I conclude there is a problem with the recursive solution. Here is the code edited: Func _FillTree_Rec($hParent, $iFID) Local $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $iFID & " AND folder_id <> 7;" ;query to select children of the passed folder ID Local $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) ;fetch to array if $iRval = 0 then Local $hItem For $i = 1 To $gRows $hItem=GUICtrlCreateTreeViewItem($ahArray[$i][1], $hParent) ;create item for each child _FillTree_Rec($hItem, $ahArray[$i][0]) ;run again with the last child as parent. Next EndIf EndFunc The problem seems to lie in the fact that $ahArray needs to be a global variable in order to query the Treeview later, and have it return results. Due to this the recursive nature of the function means that it is always being overwritten. If it wasn't for this I'm sure this would work, but as it is I'm still stuck. Link to comment Share on other sites More sharing options...
Tvern Posted February 14, 2012 Share Posted February 14, 2012 Hmm I see I was pretty sloppy there with not declaring variables and using the wrong return value from _Sql_GetTable2D. If you want to keep an array that contains all elements of the treeview it's probably best to copy the content over to another array. (You can use a global array for this, or a local one and pass it ByRef) The problem with this approach is that ReDim is a slow function, so you don't want to add the items one by one and redim for each one. Another option could be to copy each version of $ahArray into another array, but putting arrays into arrays is frowned upon and with good reason, although they have their uses. I'm not sure how many tree nodes you will have, but this should reduce the amount of ReDims to once per parent node Hopefully that would give an acceptable result. It's getting a little hard to write these examples without testing them, so I hope it's not too far off. Global $aTreeViewItems[1][2] ;I'm using [0][0] and [0][1] to keep track of array size and last filled row. ;I should really set them to a known value for this, but as I know the value to be 0 at this point I'm not going to. Global $hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR($TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES)) _FillTree_Rec($hTreeView,7) Func _FillTree_Rec($hParent, $iFID) Local $ahArray, $gRows, $gColumns, $hItem Local $sSQL = "SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $iFID & " AND folder_id <> 7;" Local $iRval = _Sql_GetTable2D($hDBHandle, $sSQL, $ahArray, $gRows, $gColumns) If $iRval Then Return $aTreeViewItems[0][0] += $gRows ;increase the row count with the number of returned rows. ReDim $aTreeViewItems[$aTreeViewItems[0][0]+1][2] ;resize the array. For $i = 1 To $gRows $aTreeViewItems[0][1] += 1 ;copy data to the first empty row. $aTreeViewItems[$aTreeViewItems[0][1]][0] = $ahArray[$i][0] $aTreeViewItems[$aTreeViewItems[0][1]][1] = $ahArray[$i][1] $hItem = GUICtrlCreateTreeViewItem($ahArray[$i][1], $hParent) ;You might want to store the handle in $aTreeViewItems here, just add a 3rd collumn if needed. _FillTree_Rec($hItem, $ahArray[$i][0]) Next EndFunc Since you seem to want to have all treeview items in one array, wouldn't it make more sense to read the entire array with a single Sql query, rather than merging multiple ones? Link to comment Share on other sites More sharing options...
Clark Posted February 15, 2012 Author Share Posted February 15, 2012 (edited) Nice work! Here is the result without changing a single byte of your code. Thanks kindly for your help. Edited February 15, 2012 by Clark Link to comment Share on other sites More sharing options...
Tvern Posted February 15, 2012 Share Posted February 15, 2012 Hmm I didn't expect it to work right away to be honest. Glad you got it working. Do the Redims slow it down at all? Link to comment Share on other sites More sharing options...
Clark Posted February 16, 2012 Author Share Posted February 16, 2012 Hmm I didn't expect it to work right away to be honest.Glad you got it working. Do the Redims slow it down at all?Nah, not at all. Except when in debugging mode of course, but in normal use they have no effect that I can see. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now