What is the correct format for using SQLitePreparedSQL with ExecuteSQL?

Databases
1

Hello:

I’m having difficulty using preparedsql with ExecuteSQL. The error I receive is: ‘expected 0 parameters to be bound but received 1’

Not quite sure what I’m doing incorrectly here.

dbsource = new SQLiteDatabase

Try

dbsource.Connect

//This ExecuteSQL line works just fine

dbsource.ExecuteSQL(“CREATE TABLE mytable(id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT)”)

Var stmt As SQLitePreparedStatement = dbsource.Prepare(“insert into mytable (username) values(‘Name: ?’)”)

for x as integer = 1 to 100

stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

stmt.Bind(0, x.ToString)

stmt.ExecuteSQL(x.ToString) // this line is when the Error happens

next

2

The problem is here:

Var stmt As SQLitePreparedStatement = dbsource.Prepare(“insert into mytable (username) values(‘Name: ?’)”)

The parameter marker ? is inside a string, so to the prepare statement there is no marker. The SQL needs to look like this:

insert into mytable (username) values(?)

And then when you bind your value it should be:

stmt.Bind(0, “Name: “ + x.ToString)

And if you include the parameter values in ExecuteSQL they will override whatever Binding you did, so you don’t need both.

1 Like
3

Hi Keith,

Please wrap your code in code blocks (< / > button) to make it easier to read.

This might help (untested):

dbsource = new SQLiteDatabase

Try

dbsource.Connect

//This ExecuteSQL line works just fine

dbsource.ExecuteSQL(“CREATE TABLE mytable(id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT)”)

Var stmt As SQLitePreparedStatement = dbsource.Prepare("INSERT INTO mytable (username) VALUES (?)")

for x as integer = 1 to 100

stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

stmt.Bind(0, x.ToString)

stmt.ExecuteSQL("Name: " + x.ToString) 

next
1 Like