ABCDE ABCDE - 1 month ago 15
Vb.net Question

Populating treeview with two tables in database

This is the code I've got from my last question:

Dim node As New TreeNode
Dim lastChapter As String = String.Empty

While dr2.Read()
Dim curChapter = dr2("chapterno").ToString
If curChapter Is Nothing OrElse curChapter <> lastChapter Then
node = New TreeNode(curChapter)
lastChapter = curChapter
geo.Nodes("toc").Nodes.Add(node)

End If
node.Nodes.Add(dr2("lessonno").ToString)
End While


It really helps a lot, but I have a problem. In my system, the chapter table and the lesson table are different tables but connected to each other. Chapter number is foreign key to the lesson table. It may happen that a chapter includes no lesson and I want that chapter to still appear in the treeview (named 'geo').

Big thanks!

Answer

I suppose that your two tables have this schema:

table Chapter
    ChapterNo  ==> PK
    Title

table Lesson
    LessonNo   ==> PK 
    Title
    ChapterNo  ==> FK to Chapter

Now if you want to retrieve all the chapters and the lessons for the chapters the query to be prepared is something like this

SELECT c.ChapterNo, c.Title, l.LessonNo, l.Title
FROM Chapter c LEFT JOIN Lesson l ON c.ChapterNo = l.ChapterNo

This will result in a record for every chapter also for chapters without lessons and the same ChapterNo is repeated for each individual lesson associated

The query should be used to populate your DataReader above (the dr2) and a little change is needed in code to avoid an exception when you read a record for a chapter without lessons

While dr2.Read()
    ....
    if Not IsDBNull(dr2("Lessonno") Then
        node.Nodes.Add(dr2("lessonno").ToString)
    End If
End While
Comments