Cannot figure out how to fix syntax error (missing operator) in query expression error in C#

Question

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.

What is the value of category? Can you post the full SQL after it has been evaluated? — Mark Byers
– Mark Byers, Commented May 5, 2012 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
– Raider00321, Commented May 5, 2012 at 17:56
I mean do this: Debug.WriteLine(sql); then post what you get. See here: support.microsoft.com/kb/815788 — Mark Byers
– Mark Byers, Commented May 5, 2012 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
– Raider00321, Commented May 5, 2012 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
– Raider00321, Commented May 5, 2012 at 18:22

Mark Byers · Accepted Answer · 2012-05-05 18:33:17Z

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

edited May 5, 2012 at 18:33

answered May 5, 2012 at 17:16

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raider00321 Over a year ago

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

Mark Byers Over a year ago

@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?

Raider00321 Over a year ago

the exact error was Syntax error (missing operator) in query expression 'like '@name''. like before

Collectives™ on Stack Overflow

Cannot figure out how to fix syntax error (missing operator) in query expression error in C#

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related