populate treeview

It's my first time to use the treeview control. I met a problem about it:
Suppose I have a table named as "book" which has field "bookid" and "chapterid". I used ADO to connect to the database.
How can I populate the treeview control and let every "bookid" as the parent node of "chapterid"?
Thanks a lot.
Jing

Dear Jing,

I hope the following code will work. I assumed that bookid and chapterid columns are numeric. Place a treeview in the form and run the code.


option Explicit
Dim iKey as Integer ' to save a unique key

' during form load, just fill the book ids.
' The chapter ids are filled when user expand a node
private Sub Form_Load()
Dim cnBook as ADODB.Connection
Dim rsBook as ADODB.Recordset
Dim NodeX as Node
Dim a as string
Dim b as Integer
Dim strSql as string

' open connection
set cnBook = new ADODB.Connection
cnBook.Open "mydsn", "kishore", "ksh"
set rsBook = new ADODB.Recordset

' open recordset
strSql = "SELECT DISTINCT bookid FROM book"
rsBook.Open strSql, cnBook, adOpenForwardOnly, adLockReadOnly

' loop through the recordset
While Not rsBook.EOF

' get next key
iKey = iKey + 1

' add a node
a = "B" & iKey ' the first character of the key should be alphabet
b = rsBook("bookid")
set NodeX = TreeView1.Nodes.Add(, , a, b)
NodeX.Tag = "N" ' N for Not filled

' add a dummy child to let the user expand the node
TreeView1.Nodes.Add NodeX, tvwChild, , "(ChapterId)"

rsBook.MoveNext
Wend

End Sub

' fill the chapter ids when the user expand a node of tree view
private Sub TreeView1_Expand(byval Node as MSComctlLib.Node)
Dim strSql as string
Dim cnBook as ADODB.Connection
Dim rsBook as ADODB.Recordset
Dim a as string
Dim X as Node
Dim b as string

' if the node was not filled before
If Node.Tag = "N" then

' remove all children of current node
While Node.Children > 0
TreeView1.Nodes.Remove Node.Child.Index
Wend

' open connection
set cnBook = new ADODB.Connection
cnBook.Open "mydsn", "kishore", "ksh"

' open the recordsset
strSql = "SELECT chapterid FROM book WHERE bookid = " _
& Node.Text
' open the recordset
set rsBook = new ADODB.Recordset
rsBook.Open strSql, cnBook, adOpenForwardOnly, _
adLockReadOnly

' loop through the recordset
While Not rsBook.EOF

' add the chapter id to tree view
a = rsBook("chapterid").Value
set X = TreeView1.Nodes.Add(Node.Key, tvwChild, , a)

' move to next record
rsBook.MoveNext
Wend

' set the tag as filled
Node.Tag = "F" ' F for filled
End If
End Sub




All the best.

Kishore