How to use parameter with LIKE in Sql Server Compact Edition

Question

I'm trying to parameterise a search query that uses the LIKE keyword with a wildcard. The original sql has dynamic sql like this:

"AND JOB_POSTCODE LIKE '" + isPostCode + "%' "

So I've tried this instead, but I get a FormatException:

"AND JOB_POSTCODE LIKE @postcode + '%' "

Edit: I guess the FormatException isn't going to be coming from Sql Server CE, so as requested, here is how I set the parameter in my C# code. The parameter is set in code like this:

command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode;

I also tried:

"AND JOB_POSTCODE LIKE @postcode"

with

command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode + "%";

but that doesn't return any results. Can anyone advise how to use parameters in this search sql?

Answer 1

The short answer is that you should put the wildcard in the Value of the parameter, not in the CommandText. i.e.

not that: sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode%"

this:

sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode";
sqlCommand.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postCode + "%";

Long answer here:

I went back and stripped my code down to the essentials so that I could post it here, and while doing that I discovered that the last method I tried in my original question does actually work. Must have been something wrong in my testing. So here's a summary, with full code that's been run:

Original dynamic sql, vulnerable to sql injection:

//Dynamic sql works, returns 2 results as expected, 
//but I want to use parameters to protect against sql injection

string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE '" 
                         + postCode + "%'";
return Database.fGetDataSet(sqlCommand, 
                            iiStartRecord, 
                            iiMaxRecords, 
                            "JOBVISIT");

First attempt to use parameter gives an error:

//This syntax with a parameter gives me an error 
//(note that I've added the NVarChar length as suggested:
//System.FormatException : @postcode : G20 - 
//Input string was not in a correct format.
//at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
//at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor,
// Boolean& isBaseTableCursor)

string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode 
                         + '%'";
sqlCommand.Parameters.Add("@postcode", 
                          SqlDbType.NVarChar, 
                          10).Value = postCode;
return Database.fGetDataSet(sqlCommand, iiStartRecord, iiMaxRecords, "JOBVISIT");

Second technique does actually work:

///This syntax with a parameter works, returns 2 results as expected
string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode";
sqlCommand.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postCode 
                                                                   + "%";
return Database.fGetDataSet(sqlCommand, iiStartRecord, iiMaxRecords, "JOBVISIT");

Thanks for all the input, and sorry about the original misleading question...