0

I'm having an issue with an error written above and cannot find a exact way to fix it.

OleDbDataAdapter dataAdapter = new OleDbDataAdapter("Select count(*) from [contractors$] where " + category + " like '*@name*'", eh.Connection);
dataAdapter.SelectCommand.Parameters.Add("@name", OleDbType.VarChar).Value = "*" + name + "*";
OleDbCommand command = dataAdapter.SelectCommand;
OleDbDataReader reader = command.ExecuteReader();

The exact error is..

Syntax error (missing operator) in query expression 'like '@name''.

I've also already looked for solutions to this problem and have attempted to adapt them to try to get this work work, but with no luck(the one above was one of the attempts)

Much thanks in advance!

Ok, so I have now change the code to this..

OleDbDataAdapter dataAdapter = new OleDbDataAdapter("Select count(*) from `contractors$` where " + category + " LIKE @name", eh.Connection);
dataAdapter.SelectCommand.Parameters.Add("@name", OleDbType.VarChar).Value = "%" + name + "%";
OleDbCommand command = dataAdapter.SelectCommand;
OleDbDataReader reader = command.ExecuteReader();

But I am still getting the same error.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raider00321
  • 57
  • 1
  • 9
  • What is the value of `category`? Can you post the full SQL after it has been evaluated? – Mark Byers May 05 '12 at 17:19
  • the value of category changed depending on the drop down menu, however, for the testing purposes it is 'Status' and sure, once i get this figured out, i will post the solution – Raider00321 May 05 '12 at 17:56
  • I mean do this: `Debug.WriteLine(sql);` then post what you get. See here: http://support.microsoft.com/kb/815788 – Mark Byers May 05 '12 at 18:03
  • I beleive you have found the problem. Select count(*) from [contractors$] where like '@name' Ill need to go back and see whats happenning with that text value and why it hasnt passed. – Raider00321 May 05 '12 at 18:16
  • Yes, that was the problem for that. I have another error now, but ill give it a crack first before actually posting it up. Could you please write your answer in the answer thign so i could accept your answer? Thankyou so much for the help =) – Raider00321 May 05 '12 at 18:22

1 Answers1

1

A parameter cannot be contained inside an SQL string literal. Use concatenation to build the string:

"... LIKE ('%' + @name + '%') ..."

Update

It seems that the value of category was null or empty, creating an invalid SQL statement:

 Select count(*) from [contractors$] where like '@name'
                                         ^^^ no category here
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Ok, i attempted to use concat in this way "Select count(*) from [contractors$] where " + category + " like '"+String.Concat('%',name,'%')+"'", eh.Connection but did not work. as for removing the brackets, that created another error it seems – Raider00321 May 05 '12 at 17:35
  • @Raider00321: `but did not work` Please be more precise. What was the error message? `as for removing the brackets, that created another error it seems`. Again, please be more precise. What was the error message? By the way, are you *sure* you are using MySQL? – Mark Byers May 05 '12 at 17:41
  • the exact error was Syntax error (missing operator) in query expression 'like '@name''. like before – Raider00321 May 05 '12 at 17:51