Issue with SQL Parameter when used with % character.

Hi all,

is there a known issue when using a SQL Paramater in a SQL string that uses a '%' character for wildcard string searches?

For example, if I used the following...

SQLCeParameter pKeyword = new SQLCeParameter("@.Keyword", SQLDbType.Char);
pKeyword.value = "Some Text";

string strSQL = "SELECT * FROM tbl_Keywords WHERE (Keyword = @.Keyword%)";

This just never returns and results. But if I run the query in a query analyser window replacing the @.Keyword% with "Some Text", I get results.

I guessing that the placing of the character '%' in the string is the cause maybe...

try putting the % in the string...

pKeyword.value = "Some Text%";

|||

for wildcard searches, you need the LIKE statement. example:

SELECT * FROM someTable WHERE FieldName LIKE @.parameter + '%'

|||Sorry, my query does actually have the LIKE statemet, its just when I post the thread I wrote the query off the top of my head.|||

try this

string strSQL = "SELECT * FROM tbl_Keywords WHERE Keyword LIKE" + @.Keyword + "%";

|||

>>This just never returns and results. <<

What does this mean? It returns nothing? Or it takes forever?

Check the plan of the query using the Display Estimated Plan feature. You should see a very large hotspot in there somewhere that is taking large amounts of the plan (since you are only dealing with one table, it might not be as useful). Then run it without the wildcard and see how the plan looks.

My guess is that it is just taking forever. And is there lots of data? Is that column indexed? An index will work with a LIKE 'value%' criteria, so that shouldn't be a problem. It could be that your indexes aren't optimized too, but first things first :)

|||My Issue is that I no resuts are being returned. I doubt it is taking forever as I am doing a search on a table that has no more that 100 records.

Thanks|||

so have you tried my suggestion earlier?

if you copy and paste your query into query analyzer or SQL management console or something, and execute it - does it bring back results?

so do you get results now, or are you have a perf issue?

another way to increase the perf really is to use stored procedures, if you havent already - safer and faster than standard SQL Injection as you maybe doing ;-)