[RESOLVED] using Like in sql statement in C#

[sunshine123]

Hi,

Im passing a sql query using Like. It works fine when I run in Access.

But when I try to run it thru C# it does not retrieve any data into the datatable. So its something to do on the c# end and not the database.

this is my code:

string strSql = "Select * from tablename where timefield Like '* + s2 + "*'";

timefield is of datetime datatype.
s2 is a string value that has something like 9:45.

What could be the problem??

[jmcilhinney]

That's not valid C# code but I'm guessing that that's a typo. Use "%" and "_" as multi-character and single character wildcards in SQL, not the Windows wildcards "*" and "?".

[penagate]

You've got the quote characters mixed up.

In any case, you should not be embedding variables in a query like that. You should use parameterised queries:

SqlCommand mycommand = new SqlCommand("select * from tablename where timefield like @s2");
mycommand.Parameters.Add("@s2", s2);
SqlDataReader results = mycommand.ExecuteReader()

[sunshine123]

Hi guys,

thanks for your input. As Jmcilhinney wrote * should be replaced with % as shown below.

string sSQL = "Select * from tablename where datefield Like '%" + s2 + "%'";

I was passing the sql statment string to a method in another class. Thats why I didnt use parameters

[jmcilhinney]

Then you should restructure your code so that you can pass parameters. Instead of passing a string to this other method you should pass a string and an array of parameters. It is just plain bad to use string concatenation to build SQL statements except in the rare cases where it's necessary. Those cases are where the values you're inserting are identifiers rather than values. In those cases you need to validate stringently to avoid SQL injection attacks.