fedeteka fedeteka - 1 year ago 172
Vb.net Question

Check if a table is empty on SQLite

I'm trying to adapt to VB.NET the code of the most voted answer from this post:

Sqlite Check if Table is Empty

Original code is

SQLiteDatabase db = table.getWritableDatabase();
String count = "SELECT count(*) FROM table";
Cursor mcursor = db.rawQuery(count, null);
int icount = mcursor.getInt(0);
//populate table

My code looks like ('Only to have a message on the screen I will fill the If - Else code later')

Using conn As New SQLiteConnection("Data Source=myDataBase.sqlite;Version=3;foreign keys=true")

Dim emptyUserTable = "SELECT COUNT(*) FROM usersTable"
Dim cmdIsEmpty As SQLiteCommand = New SQLiteCommand(emptyUserTable, conn)

Dim Answer As Integer
Answer = cmdIsEmpty.ExecuteNonQuery()
Catch ex As Exception
End Try

End Using

But the "Answer" is allways -1, with empty table or not.

I don´t know how to use getWritableDataBase because I get a
getWritableDatabase is not a member of SQLiteConnection

The same with rawQuery.

How can I check if usersTable is empty or not on VB.NET?

Answer Source

I've abstracted your code a little so it can be used for any table:

Private Function IsTableEmpty(tblName As String) As Boolean
    Dim sql = String.Format("SELECT COUNT(*) FROM {0}", tblName)

    Using conn As New SQLiteConnection(LiteConnStr)
        Using cmd As New SQLiteCommand(sql, conn)


            Dim rows = Convert.ToInt32(cmd.ExecuteScalar())
            Return rows = 0
        End Using
    End Using
End Function


If IsTableEmpty("usersTable") Then
End If


  • The command object should be disposed when you are done with it, so it is used on a Using block.
  • There is not need to copy your connection string everywhere. You can define it once as a form/class level variable and reuse it everywhere
  • ExecuteScalar() gets the count back, then it is tested for 0 rows
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download