System.Data.Sqlite FormatException using a parameter with LIKE

Question

I'm using Sqlite as my database of choice in a C# forms app, with http://sqlite.phxsoftware.com/ System.Data.SQLite provider. I'm trying to implement a search function, but it's not playing nice... or I'm missing something.

The simplified sql I'm using looks like this:

SELECT *
FROM Table
WHERE column LIKE @boundParameter ESCAPE '!'

When I run this, in any permutation with a parameter (using ? or ?001 or :boundParameter or @boundParameter), it gives me a FormatException: "Input string was not in a correct format." I haven't been able to find anything that says I can't use parameters with LIKE. Anyone know something about this? Do I need to do it some other way?

Answer 1

How do you connect and add parameters?

I haven't been using SQLite much, but the following should work;

SQLiteCommand command = _yourConnection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM Table WHERE column LIKE @boundParameter";
command.Parameters.Add(new SQLiteParameter("@boundParameter", _yourSearchCriteria));
...

Answer 2

"Input string was not in a correct format" is not an error message returned by any version of SQLite

It must be being returned by the wrapper. SO ... I am going to guess that you are using the ADO.NET 2.0 Provider from sqlite.phxsoftware.com

You must remember to quote the value you are binding to the parameter.

For example, if you use

command.Parameters.Add(new SQLiteParameter("@boundParameter", _pattern));

then _pattern = "'test'" and not "test"

Answer 3

I would recommend trying something like this:

"SELECT * FROM [Table] WHERE [column] LIKE @boundParameter ESCAPE @escape";

and then:

command.Parameters.AddWithValue("@boundParameter", parameter));
command.Parameters.AddWithValue("@escape", "!");

Parameters.AddWithValue is the SQLite way of adding a bound parameter, rather than having to declare a new one each time.

@Noah (sorry, can't comment yet)

Stephen Jennings is right, you don't have to quote the value you are binding.

Answer 4

This program-code executes a query, that includes PARAMETER SUBSTITUTION and PATTERN FITTING in one step. Here, the string variable myNamePattern is the string that we wanna find the customers for, so that all returned customers will INCLUDE THE variable myNameattern string. I had the same problem, but i solved it! This is the perfect way, to substitute a string pattern (that is also a parameter) into SQLiteCommand.CommandText:

SQLiteCommand command = conn.CreateCommand(); command.CommandText = "select * from Customer where name like @myStringParameter"; command.Parameters.Add("myStringParameter", System.Data.DbType.String).Value = "%"+ myNamePattern+ "%";