Empty Recordset [Resolved]

[odamsr]

I added a record to the table and I am still getting empty recordset. I have two tables:

tblClassification - id, classification
tblSnipplets - id, dateadded, classification, code, title

I am getting the following error:
Run-Time Error '3021'. Either BOF or EOF is True, or the current record has been deleted.

Private Sub initializeTree()

tvwItems.ImageList = imgList
    
    Dim nodClass As Node
    Dim nodTitle As Node
    
    tvwItems.LineStyle = tvwRootLines
    tvwItems.Nodes.Clear
    
    'Opens the recordset for Class
    Set rsClass = New ADODB.Recordset
    strSQLClass = "SELECT id, classification FROM tblClassification ORDER BY classification"
    rsClass.Open strSQLClass, conTree, adOpenStatic, adLockOptimistic
    
    'set node for treeview
    If rsClass.RecordCount > 0 Then
        While Not rsClass.EOF
            Set nodClass = tvwItems.Nodes.Add(, , (rsClass.Fields("classification")), (rsClass.Fields("classification")), 1)
            rsClass.MoveNext
            DoEvents
        Wend
    End If
    
    'Opens the recordset for Title
    Set rsTitle = New ADODB.Recordset
    strSQLTitle = "SELECT title, classification, code, dateadded FROM tblSnipplets WHERE classification LIKE '" & (rsClass.Fields("classification")) & "%'"
    rsTitle.Open strSQLTitle, conTree, adOpenStatic, adLockOptimistic
    
    'set node for treeview
    If rsTitle.RecordCount > 0 Then
        While Not rsTitle.EOF
            Set nodTitle = tvwItems.Nodes.Add((rsClass.Fields("classification")), tvwChild, (rsTitle.Fields("title")), (rsTitle.Fields("title")), 2)
            rsTitle.MoveNext
            DoEvents
        Wend
    End If
    
End Sub

[ahara]

Looks like the error is coming from the record set rsClass which you advance until the EOF, then try to pull a field from it in the latter part of your code.

[brucevde]

Whether rsClass contains 0, 100 or 1,000,000 records, by the time the following code is executed, rsClass.EOF will be True.

strSQLTitle = "SELECT title, classification, code, dateadded FROM tblSnipplets WHERE classification LIKE '" & (rsClass.Fields("classification")) & "%'"

You will need to store the classification value in a variable before this line executes. Which one depends on your needs?

Also, base on your posted code, if rsClass.RecordCount is 0 then you do not want to execute the code for rsTitle, but simply drop out of the sub

[odamsr]

I changed my code to the following and now it is not showing all of the classifications. I am only getting the first one.

'set node for treeview
 Set nodClass = tvwItems.Nodes.Add(, , (rsClass.Fields("classification")), (rsClass.Fields("classification")), 1)

[brucevde]

??? what has changed. That line is the same as your old code.

Let me know if this is correct - you are trying to
Add a Root node for every Classification
Add child nodes for every Title in the Classification.

You should execute your rsTitle query for each row in your rsClass recordset.

Private Sub initializeTree()
    tvwItems.ImageList = imgList
    
    Dim nodClass As Node
    Dim nodTitle As Node
    
    tvwItems.LineStyle = tvwRootLines
    tvwItems.Nodes.Clear
    
    'Initialize the recordsets
    Set rsClass = New ADODB.Recordset
    Set rsTitle = New ADODB.Recordset
 
    'Open the Class recordset
    strSQLClass = "SELECT id, classification FROM tblClassification ORDER BY classification"
    rsClass.Open strSQLClass, conTree, adOpenStatic, adLockOptimistic
    
    'set node for treeview
    If rsClass.RecordCount > 0 Then
        While Not rsClass.EOF
            Set nodClass = tvwItems.Nodes.Add(, , (rsClass.Fields("classification")), (rsClass.Fields("classification")), 1)
 
            'Opens the recordset for Title
            strSQLTitle = "SELECT title, classification, code, dateadded FROM tblSnipplets WHERE classification LIKE '" & (rsClass.Fields("classification")) & "%'"
            rsTitle.Open strSQLTitle, conTree, adOpenStatic, adLockOptimistic
            
            'set node for treeview
            If rsTitle.RecordCount > 0 Then
                While Not rsTitle.EOF
                    Set nodTitle = tvwItems.Nodes.Add((rsClass.Fields("classification")), tvwChild, (rsTitle.Fields("title")), (rsTitle.Fields("title")), 2)
                    rsTitle.MoveNext
                    DoEvents
                Wend
            End If
            rsTitle.Close
            
            rsClass.MoveNext
            DoEvents
        Wend
    End If
End Sub

You can probably optimize this by using a one recordset. Your existing sql statements can be combined using a Left Join. Then you only need to loop through a single recordset.

[odamsr]

That was what I was trying to do. Thanks for you help. The code worked.