Solved

building the tree view  from memory

Posted on 2001-09-11
15
159 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
  • 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
 

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 27

Accepted Solution

by:
Ark earned 200 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 27

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Share codes 68 115
Check Disk (CHKDSK) on all volumes and fix if needed. 8 157
Input past end of file vbs script 9 68
Access 2016 VB code 9 86
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now