How can I use the Like Operator with a Parameter in a SQLite query?

Question

I can get the result I expect by entering this in LINQPad:

SELECT * FROM WorkTable WHERE WTName LIKE "DSD__20090410014953000%"

(it shows me the record which has a WTName value of DSD__20090410014953000.xml")

But trying to do this programmatically is proving trying. I tried:

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName%";
using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
    con.Open();
    SQLiteCommand cmd = new SQLiteCommand(qry, con);
    cmd.Parameters.Add(new SQLiteParameter("wtName", tableName));
    siteNum = Convert.ToInt32(cmd.ExecuteScalar());
}

...but it causes the app to crash, and my log file tells me why:

Message: From application-wide exception handler: System.Data.SQLite.SQLiteException: SQL logic error or missing database
near "%": syntax error

So maybe it thinks the query parameter is named "wtName%" instead of "wtName"; but separating the parameter and the "whatever" opertor ("%") with a space doesn't work, either.

I could go retro/kludgy by just embedding the query parameter into the string like so:

const string qry = String.Format("SELECT SiteNum FROM WorkTable WHERE WTName LIKE {0}%", tableName);

...and doing without the query parameter altogether, but I'm afraid if I did that Troy Hunt would show up at my house and flail me with a bedrail while railing about SQL Injection.

How can I get my data and simultaneously write safe code?

Steve · Accepted Answer · 2015-01-22 23:06:53Z

18

+50

The wildcard % should be added to the parameter value, not to the parameter name

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName";
using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
    con.Open();
    SQLiteCommand cmd = new SQLiteCommand(qry, con);
    cmd.Parameters.Add(new SQLiteParameter("@wtName", tableName + "%"));
    siteNum = Convert.ToInt32(cmd.ExecuteScalar());
}

And, I am not sure if it matters here, but, usually, I insert the parameter name with the exact name used in the placeholder ( @wtName )

edited Jan 22, 2015 at 23:06

answered Jan 22, 2015 at 23:01

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kas · Accepted Answer · 2015-08-30 14:18:13Z

9

Easiest way to do this is to use '||'

Use :

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName || '%' ";

Instead Of:

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName%";

answered Aug 30, 2015 at 14:18

Kas

3,9635 gold badges31 silver badges57 bronze badges

1 Comment

Reteras Over a year ago

Why do one and not the other?

Dragan · Accepted Answer · 2015-01-22 23:06:43Z

-2

Try defining query like this:

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName + '%'";

answered Jan 22, 2015 at 23:06

Dragan

133 bronze badges

3 Comments

Dragan Over a year ago

Didn't try on SQLite but it works on SQL Server for sure. I copied it from existing project.

Steve Over a year ago

The concatenation operator for strings in SQLite is || so you should write @wtName || '%' Still I believe that this is too error prone and not portable

Kas Over a year ago

@Dragon your answer is valid for SQL Server but not for SQLite , As I have answred in SQLite you must use '||' instead of '+'

Collectives™ on Stack Overflow

How can I use the Like Operator with a Parameter in a SQLite query?

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related