Count Row from Select Statement

May 23, 2015 at 8:09 AM
Edited May 23, 2015 at 8:12 AM
Hi Guys

Just found this fantastic utility and have started using it but cant work something out

I use the following Select statement
RetVal = SQLite3PrepareV2(myDbHandle, "Select * From data where REPBY Like'" + "%" + str + "%" + "'", myStmtHandle)
I then need to do a loop to add each found row to a listbox
Dim bb, ROWS
ROWS= {Number of Rows}
For bb = 1 To ROWS
RetVal = SQLite3Step(myStmtHandle)
TestPrint myStmtHandle
Next bb
Problem is I don't know how to get the number of rows returned from the select statement i need to replace {Number of Rows} with the number of rows from the select statement

I'm really new to this could someone please help me with some code to loop through all rows found and count them>

I have tried a select count statement but don't know how to interperate the results

Any help is greatly appreciated

Mark
May 23, 2015 at 12:52 PM
Edited May 23, 2015 at 12:54 PM
I don't think you can find out how many rows there are in the result set until you've stepped through it.

The right pattern is probably a Do While statement:
Public Sub SQLite3ExecuteQuery(ByVal dbHandle As Long, ByVal sqlQuery As String)
    ' Dumps a query to the debug window. No error checking
    
    Dim stmtHandle As Long
    Dim RetVal As Long

    RetVal = SQLite3PrepareV2(dbHandle, sqlQuery, stmtHandle)
    Debug.Print "SQLite3PrepareV2 returned " & RetVal
    
    ' Move to next row
    RetVal = SQLite3Step(stmtHandle)
    Do While RetVal = SQLITE_ROW
        Debug.Print "SQLite3Step Row Ready"
        PrintColumns stmtHandle
        RetVal = SQLite3Step(stmtHandle)
    Loop

    If RetVal = SQLITE_DONE Then
        Debug.Print "SQLite3Step Done"
    Else
        Debug.Print "SQLite3Step returned " & RetVal
    End If
    
    ' Finalize (delete) the statement
    RetVal = SQLite3Finalize(stmtHandle)
    Debug.Print "SQLite3Finalize returned " & RetVal
End Sub
May 23, 2015 at 4:00 PM
Thank you so much for your help

I added a counter to the while loop and save it to a global variable so I can now access it from wherever I want

Great being able to use sqlite in excel.

Mark