Search code examples
c#mysqlsqlexecute

Unable to execute MySQL query using the IN clause

I am trying to perform a query that reads a list of projects from a database where the project's state is within a given list. I have written this query (using C#, also):

string statesList = "(1, 2, 3, 4, 5)"; // This is built inside a function, it could include any or all of these values, e.g. (1, 3, 4);

cmd.CommandText = "SELECT p.Name, p.State_Id FROM Projects p
    WHERE p.State_Id IN @states";
cmd.Parameters.AddWithValue("@states", statesList);

When I run this query, I am given this error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1, 2, 3, 4, 5)'' at line 1".

I'm stumped. Can anyone see what I'm missing?

Solution

  • You have the misconception that using parameterized queries works the same as a string replacement. The resulting query is not

    SELECT p.Name, p.State_Id FROM Projects p WHERE p.State_Id IN (1,2,3,4,5)

    You can not use a parameterized query the way you do here.