I am creating a database search container control. It allows the user to specify any number of criteria which will all be “ANDed” together to form a query. Since the user will be entering text, among other data types, I’d like to use prepared statements for the queries. As designed, the container creates an SQL string (e.g., “SELECT * FROM myTable WHERE myTable.name LIKE ? AND myTable.age > ?”) and an array of 1-n (2 in the this example) variants representing the values with which each of the “?” characters in the string needs to be substituted.
I have a method that takes these two parameters (the string and the variant array). It instantiates a SQLitePreparedStatement, iterates over each element of the passed-in array, and does the appropriate data type bindings. It then passes the variant array to the SQLitePreparedStatement.sqlSelect function.
That results in this error:
Database Error: 0 - Mismatched parameter and type at 1
and it returns an empty recordSet.
It fails even if I try this with a single element in the array, BUT if I do this:
myRecordSet = myPreparedStatement.SQLSelect(theValues(0))
myRecordSet = myPreparedStatement.SQLSelect(theValues)
it works as expected with the one criterion. So, passing a single property to the SQLselect function works, but passing a variant array does not.
What am I doing wrong, and how can I construct whatever the function is looking for in terms of multiple values rather than just one?