Question treeview group by in sqlquery in dataset

darkcat02

Active member
Joined
Mar 4, 2009
Messages
38
Programming Experience
1-3
hi.. its me again... sorry for being noob...

its been days since i am working this one out... when i put GROUP BY into my sql query it says about "You tried to execute a query that does not include the specified expression 'BookNum' as part of an aggregate function."

i just need this simple result

BookName
-Chapter1
-Verse1
-Verse2
-Chapter2
-Verse1

my screen shot of table in db is attached and also my output from my code


Private Sub _PopulateBible()

TreeViewBible.Nodes.Clear()

'// Declare Data Objects
Dim dbFile As String
dbFile = strBibleDataPath & strBibleFilename
'
Dim cnnBL As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & dbFile & ";Mode=Read;Jet OLEDB:Database Password=" & strBiblePw(strBibleID))

Dim dsBL As New DataSet

Dim cmdBibleBookCategory As String = "SELECT BookCategoryNum, BookCategory FROM BibleBookCategory"
Dim cmdBibleBooks As String = "SELECT BookNum, BookStdName, BookName, BookAbbrev, BookCategoryNum FROM BibleBooks"
Dim cmdBibleChapter As String = "SELECT Chapter, BookNum, Verse FROM BibleVerses GROUP BY Chapter"
'Dim cmdBibleInformation As String = "SELECT * FROM Information"

Dim daBBC As New OleDb.OleDbDataAdapter(cmdBibleBookCategory, cnnBL)
Dim daBL As New OleDb.OleDbDataAdapter(cmdBibleBooks, cnnBL)
Dim daBC As New OleDb.OleDbDataAdapter(cmdBibleChapter, cnnBL)

daBBC.Fill(dsBL, "dtBibleBookCategory")
daBL.Fill(dsBL, "dtBibleBooks")
daBC.Fill(dsBL, "dtBibleVerses")

cnnBL.Close()

dsBL.Relations.Add("BookCategoryToBooks", dsBL.Tables("dtBibleBookCategory").Columns("BookCategoryNum"), dsBL.Tables("dtBibleBooks").Columns("BookCategoryNum"))
dsBL.Relations.Add("BooksToVerses", dsBL.Tables("dtBibleBooks").Columns("BookNum"), dsBL.Tables("dtBibleVerses").Columns("BookNum"))

Dim nodeCategory, nodeBooks, nodeChapter, nodeVerse As TreeNode
Dim rowCategory, rowBooks, rowChapter, rowVerse As DataRow

For Each rowCategory In dsBL.Tables("dtBibleBookCategory").Rows
nodeCategory = New TreeNode
nodeCategory.Text = rowCategory("BookCategory")
nodeCategory.Tag = rowCategory("BookCategoryNum")
TreeViewBible.Nodes.Add(nodeCategory)
For Each rowBooks In rowCategory.GetChildRows("BookCategoryToBooks")
nodeBooks = New TreeNode
nodeBooks.Text = rowBooks("BookName")
nodeBooks.Tag = rowBooks("BookNum")
nodeCategory.Nodes.Add(nodeBooks)
For Each rowChapter In rowBooks.GetChildRows("BooksToVerses")
nodeChapter = New TreeNode
nodeChapter.Text = "Chapter " & rowChapter("Chapter") '& ":" & rowChapter("Verse")
nodeChapter.Tag = rowChapter("BookNum")
nodeBooks.Nodes.Add(nodeChapter)
nodeVerse = New TreeNode
nodeVerse.Text = "Verse " & rowChapter("Verse")
nodeVerse.Tag = rowChapter("Chapter")
nodeChapter.Nodes.Add(nodeVerse)
Next
Next
Next

dsBL.Dispose()
daBL.Dispose()
daBC.Dispose()
cnnBL.Close()
cnnBL.Dispose()

End Sub


anyone?? T__T i need it tomorrow... just a simple explanation what should i do....
 

Attachments

  • bibleverses_ss.jpg
    bibleverses_ss.jpg
    25.1 KB · Views: 28
  • bibleverses_ss1.jpg
    bibleverses_ss1.jpg
    26.4 KB · Views: 24
Last edited:
still have no luck at all... any other ideas?

by the way GROUP BY is not supported by DataSet...

i want to be to be distinct or something else...

just to be like this

BookName
-Chapter1
-Verse1
-Verse2
 
Last edited:
Back
Top