SQLite question

Databases
1

Sorry, there is probably an easy answer for this but I can’t figure it out.
This example will show the complete database but how do I only show the last 5 records added?

[code]// mDB is a previously connected database

Dim sql As String
sql = “SELECT * FROM Team”

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, _
data.IdxField(3).StringValue, data.IdxField(4).StringValue)

data.MoveNext

Wend
data.Close
End If[/code]

2
  1. you need to be able to order the data such that the “last 5 records added” are definable. Do you have an integer primary autoincrement key for this purpose? Or a date added field?
  2. use LIMIT and OFFSET in your sql statement to read just those 5 records.
3

Another approach would be to order them in reverse chronological order and read the first 5 records.

4

yep autoincrement primarykey showed in reverse order, and limit to the 5 records.

5

To amend what Tim and Jean-Yves said, if you don’t order the Select statement by something, the resultant order is not reliable. Even though it seems to always list the records in a certain order, there’s no guarantee that it always will. That could come back to bite you down the road.

6

Try: sql = "SELECT * FROM Team ORDER BY RowID DESC LIMIT 5;"

7

It’s funny Neil because I was just going to post your example. Thanks for the help, lots of my hangups are figuring out the correct syntax.
BTW, is there a reason to add the semicolon at the end? Code seems to work without it.

8

usually not required… except if multi-statements are supplied (not supported by all database engines)