Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

building the tree view from memory

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
ahuen
Asked:
ahuen
  • 7
  • 4
  • 2
  • +2
1 Solution
 
CJ_SCommented:
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
 
ahuenAuthor Commented:
This means that i will have several recordsets opened...
0
 
ahuenAuthor Commented:
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!

 
ahuenAuthor Commented:
how can i build the tree from an array or vector?
increasing points to 200
0
 
dekeldateCommented:
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
 
Anthony PerkinsCommented:
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
 
ArkCommented:
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
 
ahuenAuthor Commented:
acperkins,

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

Thanks.
Al
0
 
Anthony PerkinsCommented:
ahuen,

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

Anthony
0
 
Anthony PerkinsCommented:
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
 
ahuenAuthor Commented:
Ark,

What is UDT? What does it do?
0
 
ahuenAuthor Commented:
greatly appreciate if you can explain UDT.

Excellent.

Thanks.
Al
0
 
Anthony PerkinsCommented:
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
 
ahuenAuthor Commented:
thank you
0
 
ArkCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now