Counting Rows in SQLite

Databases
1

Hello all!

I am trying to return the number of rows in my table to a label. I am using the following code:

Var ds as RowSet
ds = db.SelectSQL("SELECT COUNT(*) FROM TestTable")

Try
  Label1.text = ds.RowCount.ToString
Catch e As UnsupportedOperationException
  Label1.text = e.Message
End Try

This only seems to return “1” as the number of rows in my table even though I know there to be many more.

Would anyone have a possible solution to display the correct number?

Thank you in advance!

Robin

2

You’ve asked for one row and one row is what you get.

You might do better with:


Var ds as RowSet
ds = db.SelectSQL("SELECT COUNT(*) as ct FROM TestTable")

Label1.text = ds.Column("ct").IntegerValue.ToString
4 Likes
3

Tim, you have saved me once more! (again from a silly mistake).

Thank you kindly sir!

4

You can get more records from count if you group, this is handy to work out how many duplicates you may have, for example

rowset=db.selectSQL(“select count(*) as results ,name form name group by names”)

This will give you a result with a rowset of two columns, results and name. It will calculate how many duplicate names exist in the table and results will hold the result for each.

2 Likes