Solved: Load data into TreeView

[JKwan]

Location License Num Line Num
10-17 PL 20174 63
10-17 PL 20174 68
10-17 PL 20174 69
10-17 PL 33880 001
10-17 PL 33880 002
10-17 PL 33880 003
10-17 PL 33880 004
10-17 PL 33880 005
10-22 PL 31011 001
10-22 PL 31011 002
10-22 PL 31011 003
10-22 PL 31011 004
10-22 PL 31011 005
10-22 PL 31012 001
10-22 PL 31012 002
10-22 PL 31012 003
10-22 PL 31012 004
10-22 PL 31012 005

The TreeView is really giving me a problem, been trying to load it properly without success, hoping someone can help. The final structure should be like this:
10-17 PL
---20174
------63
------68
------69
---33880
------001
------002
------003
------004
------005
10-22 PL
---31011
------001
------002
------003
------004
------005
---31012
------001
------002......

[Bob Phillips]

Is this a treeview on a form? Colo has a good example http://puremis.net/excel/code/080.shtml

[JKwan]

Yes, the treeview is on a form.
Thank you for the example, looking at the example, one would need to id the relationships, I was hoping that I could skip it. As the data is from a SQL extract..... I was hoping that I could load it into the treeview. Maybe this is why I am having a heck of a time loading it?

[Bob Phillips]

What do you mean, you were hoping to dump the data onto the treeview in one hit? How would the treeview know about the relationships?

[JKwan]

Well, that is what I was hoping. Since I only started looking at the control, I am very novice with it. I was able to load:
10-17 PL
---20174
------63
------68
------69

As soon as I hit 33880, well, I don't know how to load it to 10-17 PL branch. I was running a check to see if the element exists if it does not, add it to the tree. No matter what I tried to do, it is not loading to the "proper" branch that I wanted.

[Bob Phillips]

Can you post your code to look at?

[JKwan]

xld:
Thank you for your interest with your assistance. I finally got my tree loaded (yeah). However, the downside is that it is really really slow and I know why. Maybe you can give me pointers as to how I can speed it up (the data presented is a subset of my data, the whole set range from 2500 - 3000 rows). The only way to speed it up - I think the whole loading process need to be scrap and rewrite (urgh), however, I think it is working. Any assistance would be greatly appreciated, thanks.

[Bob Phillips]

What do you count as slow? I selected Edson, and it loaded in under 3 secs for me. There was some weird behaviour on the expanding/collapsing, but it loaded fast enough.

[JKwan]

Actually, I removed all the coding for data retrieval, so, no matter what you select it will only load the data from sheet1.
As to loading..... well, it loaded around 5 seconds for me. When I first got it to run, I swear that it took close to a minute. I don't know if it was the debug / watch window that I got loaded? This load time is not bad, now that it loads around 5 seconds. I really cannot do this without you giving me the article from Colo's web site, I took his idea and programmed it. Thanks.

By the way, as to the "weird clicking", I had an event set, so whenever you click on the branch, it will expand it.

[Bob Phillips]

I realised that you commented out the data retrieval, seeing as I din't have the data.

My only thought on looking at the code is that your inner loops going through all the nodes might get quite inefficient. As the treeview gets larger, those loops will take longer and longer. My thought was that it might be better identify a start and end row for each licence with a Location, and for each location, and then to load the treeview from there. If I get a moment this afternoon, I will take a look and see if it is quicker.

[JKwan]

My oh my, am I bad.... Too forgetful!
I just realized that data is only a subset (about 700 rows), the whole set of data is about 2600 rows, it took 1:40 to load.

[Bob Phillips]

I really cannot do this without you giving me the article from Colo's web site, I took his idea and programmed it.

I stumbled across another one by Ken Puls yesterday which is probably better, it does start by assuming images.

[JKwan]

I just got this running and it definitely loads much faster. You may call it a hack - but works. So, instead of loading the entire dataset, I do a filter on the Location, this is not the best, but it does solve my loading problem. I hope you have better idea as to how to speed it up, I will look into your suggestion as well and see if I can program it. Thanks again.

[Bob Phillips]

This is the sort of thing I envisaged.

On the pure treeview load with your 650 rows of data, your code averaged 2.46875 secs, mine averaged 0.03125 secs, an improvement of some 8,000%. And yours will get geometrically slower as the rows of data, and the attendant number of nodes, increase, whilst mine will get arithmetically slower as the rows of data increase.



Sub GetData()
Dim FOCondition As String
Dim oRS As New ADODB.Recordset
Dim Schema As String
Dim sSQL As String
Dim FieldOffice As String
Dim xNode As Node
Dim prevLocation As String
Dim prevLicense As String
Dim i As Long, j As Long, k As Long
Dim indexLevel1 As Long
Dim indexLevel2 As Long
Dim tvm As Nodes

Dim nTime As Double
nTime = Timer

With frmMaximo.cmbFieldOffices
FieldOffice = .List(.ListIndex, 1)
FOCondition = " And SITEID = '" & FieldOffice & "' "
End With

stcon = "Provider=MSDAORA.1;" & _
"Persist Security Info=false;" & _
"User ID=xxx;" & _
"Password=xxx;" & _
"Data Source=TLMGIST"
Schema = "GIS_APP_MAXIMO"
Table = "TLMPSASSET_O_IFACE_VW"

sSQL = "Select Distinct " & _
s04 & s05 & s06 & s07 & s08 & s09 & s10 & _
s11 & s12 & s13 & s14 & s15 & s16 & s17 & s18 & s19 & s20 & _
s21 & s22 & s23 & s24 & s25 & s26 & s27 & s28 & s29 & s30 & _
s31 & s32 & s33 & _
" FROM " & Schema & "." & Table & _
" WHERE ((STATUS='ACTIVE') or (STATUS='COMMISSION') or (STATUS='PLAN'))" & _
FOCondition & _
" Order By LOCATION, APPROVALNO, SEGMENTNUM"

' Set oRS = CreateObject("ADODB.Recordset")
' oRS.Open sSQL, stcon, adOpenStatic, adLockOptimistic

' If Not oRS.EOF Then
' CopyDataToSheet oRS

lastrow = FindLastRow("A")

On Error GoTo HandleError
With frmMaximo

Set tvm = .TreeView1.Nodes
tvm.Clear
.TreeView1.LineStyle = tvwRootLines

i = 2
Do

prevLocation = Sheet1.Cells(i, 1).Value
Set xNode = tvm.Add(, tvwChild, , Sheet1.Cells(i, 1))
indexLevel1 = xNode.Index

j = i
Do

prevLicense = Sheet1.Cells(j, 2).Value
Set xNode = .TreeView1.Nodes.Add(indexLevel1, tvwChild, , Sheet1.Cells(j, 2).Value)
indexLevel2 = xNode.Index
k = j - 1
Do

k = k + 1
Set xNode = tvm.Add(indexLevel2, tvwChild, , Sheet1.Cells(k, 3))
Loop While Sheet1.Cells(k + 1, 2).Value = prevLicense And _
k <= lastrow

j = k + 1

Loop While Sheet1.Cells(j, 1).Value = prevLocation And j <= lastrow

i = j
Loop While Sheet1.Cells(i, 1).Value <> prevLocation And i <= lastrow

i = k + 1

End With

'Close the recordset, connection and release objects from memory
' With oRS
' .Close
' .ActiveConnection = Nothing
' End With
Set oRS = Nothing
' Else
' MsgBox "There is nothing in the field office - " & FieldOffice
' End If

Debug.Print Timer - nTime

Exit Sub

HandleError:
MsgBox "Max Nodes reached, not all data was loaded into the form"
End Sub

[JKwan]

xld:
I don't know how to thank you. To load my 2600 rows it took less than a second! Now, I am going to alter the code to have 4 levels, to include the Field Office! I did not do this from the start, knowing it will be really really slow, now, you gave me something to work on, thanks a ton.

[Bob Phillips]

Looking at that code again, that line i = k + 1 just before the End With is superfluous. I thing it is a relic of the development process.