?
Solved

building the tree view  from memory

Posted on 2001-09-11
15
Medium Priority
?
176 Views
Last Modified: 2012-05-04
I have the following code to build the tree. I want to store the results in memory and if the same tree is reopen then populate the tree based on the results stored in memory. Going to the database and back is too slow for huge trees... Any ideas?


Dim rs As ADODB.Recordset
Dim ssql As String
Dim objNode As Node

Call RemoveNodes

ssql = "select id,name from cause where parent_id is null"
Set rs = New ADODB.Recordset
rs.Open ssql, conn

Screen.MousePointer = vbHourglass

  Do While Not rs.EOF
    tvwCause.Nodes.Add , tvwLast, "_" & rs("id"), rs("name")
    tvwCause.Nodes.Item(1).Expanded = True
    Call addChildren(rs.Fields(0).value)
     
    rs.MoveNext
  Loop
 
  'sort nodes
  For Each objNode In tvwCause.Nodes
     objNode.Sorted = True
  Next
 
Screen.MousePointer = vbDefault


Private Sub addChildren(pnode As Integer)
'add children given a parent_id
Dim ssql As String
Dim rs As ADODB.Recordset
Dim pid As Integer

pid = 0

ssql = "SELECT id, name from cause where parent_id=" & pnode & ";"
Set rs = New ADODB.Recordset
rs.Open ssql, conn

  Do While Not rs.EOF
    'add node
    tvwCause.Nodes.Add "_" & pnode, tvwChild, "_" & rs.Fields(0).value, rs.Fields(1).value
    pid = rs.Fields(0).value
    Call addChildren(pid)
  rs.MoveNext
  Loop

End Sub
0
Comment
Question by:ahuen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 6475154
What you can do is create a recordset in memory and place all data in there (retrieve them from the database, but make sure that the recordset is totally retrieved using a client-side cursor. Then you can always access that recordset whenever you need it (saves the database connection, but only the first time. You can even build in a little security check like if the recordset equals nothing that oly then the database will be examined.
It also has the minimum amount of change (I think,...)
0
 

Author Comment

by:ahuen
ID: 6475158
This means that i will have several recordsets opened...
0
 

Author Comment

by:ahuen
ID: 6475202
when the function is called the second time it resets the recordset because of the dim. How can i get around that?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ahuen
ID: 6475244
how can i build the tree from an array or vector?
increasing points to 200
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6475476
I would propose using XML

I am assuming that you fill the treeview with other tree-strutures in between.

you could save the XML in a string in memory in a file on disk.

If this is something you are interested in I could point
you forward
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6475478
There are at least a couple of approaches you can take.

1. Use the Recordset GetRows method to build an array.  You can then close the recordset and use the array to populate the TreeView.

2. Use the Recordset to populate the TreeView and then save it to disk, using the Save method.  You can then later open the recordset using this file.

Let me know, if you need some code or if I am off track.

Anthony
0
 
LVL 28

Accepted Solution

by:
Ark earned 800 total points
ID: 6476138
Hi
You can use UDT:

Private Type NODE_INFO
    Relative As Variant
    Relationship As TreeRelationshipConstants
    Key As String
    Text As String
    Image As Variant
    SelectedImage As Variant
    Sorted As Boolean
    Expanded As Boolean
'add more if you need
End Type

Dim nds() As NODE_INFO

Private Sub Command1_Click()
   SaveTV TreeView1
   TreeView1.Nodes.Clear
   TreeView1.Refresh
End Sub

Private Sub Command2_Click()
   RestoreTV TreeView1
End Sub

Private Sub Form_Load()
    Dim Root As Node
    With TreeView1.Nodes
         Set Root = .Add(, , , "Top-level Node #1")
             .Add Root.Index, tvwChild, , "Child Node #1"
             .Add Root.Index, tvwChild, , "Child Node #2"
             .Add Root.Index, tvwChild, , "Child Node #3"
             Root.Expanded = True
         Set Root = .Add(, , , "Top-level Node #2")
             .Add Root.Index, tvwChild, , "Child Node #1"
             .Add Root.Index, tvwChild, , "Child Node #2"
             .Add Root.Index, tvwChild, , "Child Node #3"
         Set Root = .Add(, , , "Top-level Node #3")
             .Add Root.Index, tvwChild, , "Child Node #1"
             .Add Root.Index, tvwChild, , "Child Node #2"
             .Add Root.Index, tvwChild, , "Child Node #3"
             Root.Expanded = True
         Set Root = .Add(, , , "Top-level Node #4")
             .Add Root.Index, tvwChild, , "Child Node #1"
             .Add Root.Index, tvwChild, , "Child Node #2"
             Root.Expanded = True
         Set Root = .Add(Root.Index, tvwChild, , "Child Node #3")
            .Add Root.Index, tvwChild, , "Grand Child Node #33"
             Root.Expanded = True
    End With
End Sub

Private Sub SaveTV(tv As TreeView)
   Dim i As Long, nd As Node
   ReDim nds(1 To tv.Nodes.Count)
   For Each nd In tv.Nodes
       i = i + 1
       With nds(i)
           .Key = nd.Key
           .Text = nd.Text
           .Image = nd.Image
           .SelectedImage = nd.SelectedImage
           .Sorted = nd.Sorted
           .Expanded = nd.Expanded
           If nd.Parent Is Nothing Then
              .Relative = ""
           Else
'              .Relative = nd.Parent.Key
              .Relative = nd.Parent.Index
              .Relationship = tvwChild
           End If
       End With
   Next
End Sub

Private Sub RestoreTV(tv As TreeView)
   Dim i As Long, nd As Node
   For i = 1 To UBound(nds)
       If nds(i).Relative <> "" Then
          Set nd = tv.Nodes.Add(nds(i).Relative, nds(i).Relationship, , nds(i).Text)
       Else
          Set nd = tv.Nodes.Add(, , , nds(i).Text)
       End If
       If nds(i).Key <> "" Then nd.Key = nds(i).Key
       If nds(i).Image Then nd.Image = nds(i).Image
       If nds(i).SelectedImage Then nd.SelectedImage = nds(i).SelectedImage
       nd.Sorted = nds(i).Sorted
       nd.Expanded = nds(i).Expanded
   Next
   tv.Refresh
End Sub

Cheers
0
 

Author Comment

by:ahuen
ID: 6476761
acperkins,

Can you please show me some sample code...?
I think storing in memory is sufficient for now.

Thanks.
Al
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6477591
ahuen,

I will post some code when I get home this evening.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6478250
I took a second look at the question as well as a previous question you posted back in July
treeview question
http://devx.experts-exchange.com/jsp/qShow.jsp?qid=20155771
which by the way is still open and you may want to followup on it ?vale?

The outcome of all of this is that because of the recursive nature of the calls, I now do not believe an array model using GetRows would be practical.  On the other hand Ark example using an array of UDT's may be worthwhile exploring.

Anthony
0
 

Author Comment

by:ahuen
ID: 6479727
Ark,

What is UDT? What does it do?
0
 

Author Comment

by:ahuen
ID: 6479870
greatly appreciate if you can explain UDT.

Excellent.

Thanks.
Al
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6479878
UDT = User Defined Type
As in:
Type EmployeeType
   EmployeeID as Long
   FirstName As String
   LastName As String
   DepartmentID As Long
   ManagerID As Long
   Salary As Currency
End Type

Anthony
0
 

Author Comment

by:ahuen
ID: 6479900
thank you
0
 
LVL 28

Expert Comment

by:Ark
ID: 6481238
Thanks for points. Glad I could help you. Asperkins already explained what is UDT. Just some more comments. VB have some predefined types of variables - Integer, Long, String, Single, Double etc. But you can construct your own type ov variable which consist from a number of predefined. For this you need to describe it:

Private Type NODE_INFO
   Relative As Variant
   Relationship As TreeRelationshipConstants
   Key As String
   Text As String
   Image As Variant
   SelectedImage As Variant
   Sorted As Boolean
   Expanded As Boolean
'add more if you need
'for example, Bold As Boolean
'Tag As Variant
End Type

and then assign this type to variable:
Dim ni As NODE_INFO

Cheers
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question