2

My inline sql query is like this

DataSet ds =  SqlHelper.ExecuteDataset(GlobalSettings.DbDSN, CommandType.Text, 
   "SELECT TOP 1000 [ID],[Project],[Owner],[Consultant],[Contractor],[Value],
      [Level1], [Level2] ,[Status] ,[Category]  ,[Country],[CreatedDate],
      [CreatedByID], [CreatedByName] 
    FROM [DBname].[dbo].[tbl_Projects] 
    where [Category] like %@Category% 
      and Value=1000 
      and Country like'%Bahrain%' 
    order by CreatedDate",
new SqlParameter("@Category","oil")  );

everything looks okay to me .But it throws an error

System.Data.SqlClient.SqlException: Incorrect syntax near 'Category'.

I believe it is something I had done wrong when using like query. Can any one point out what went wrong?

Taryn
  • 242,637
  • 56
  • 362
  • 405
None
  • 5,582
  • 21
  • 85
  • 170
  • 1
    You are missing the single quotes around `'%@Category%'` – Taryn Jul 19 '13 at 11:10
  • Was my first thought, too, but @Category is a parameter. – KekuSemau Jul 19 '13 at 11:13
  • You might as well use `WHERE CHARINDEX(@Category, [Category]) > 0` instead as you are adding both leading and trailing wildcards. Saves having to append them and also works correctly if your `@Category` value contains characters of special significance in a `LIKE` pattern without having to escape them. – Martin Smith Jul 19 '13 at 11:20

1 Answers1

2

I think this should work 

... LIKE '%' + @Category + '%'

cf. T-SQL and the WHERE LIKE %Parameter% clause

Community
  • 1
  • 1
KekuSemau
  • 6,830
  • 4
  • 24
  • 34