SQLite SELECT using a variable as a condition

Databases
1

This is such a simple problem that I feel almost embarrassed to ask. I am trying to retrieve selected records from an SQLite Database using a condition. Suppose I have an integer field where I want to retrieve records whose value is great (or less) than a certain number, if I use the syntax “… SELECT FROM tablename WHERE fieldname > 10”, it works fine. Likewise, if I use the the value of the content of a text field - "…SELECT FROM tablename WHERE fieldname > " + txtxyz.Text, it also works OK. But if I convert the value to an integer variable, I just can’t get the syntax right. I suspect it’s something quite simple, but can some kindly soul out there please help !!

Mike

2

you must convert your integer variable to a string using Str(my_integer_variable)

3

ie "SELECT FROM tablename WHERE fieldname > " + Str(my_integer_variable)

but if my_integer_variable is extremely large (in the billions) the STR() command may turn it into something like ‘1.23e10’, so I favour something like this as being more future-proof:

"SELECT FROM tablename WHERE fieldname > " + FORMAT("#############0", my_integer_variable)
4

You should never ever use variables in queries without escaping. Even if there is not that big sql-injection-problem with integer…
You should really use a prepared statement.

Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT FROM tablename WHERE fieldname > ?”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, txtxyz)
dim rs as RecordSet = ps.SQLSelect