CODEWITHSUNDEEP
c#mysqlsqlexecute
AdamMc331
AdamMc331

Reputation: 16690

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?

Upvotes: 0

Views: 485

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your resulting query is:

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

Which is the same as:

WHERE p.State_Id = '1, 2, 3, 4, 5' <-- a single string value

If you don't care about indexes, you can do what you want as:

SELECT p.Name, p.State_Id
FROM Projects p
WHERE find_in_set(p.State_Id, @states) > 0

Note: you should remove the spaces after the commas for this.

Alternatively, do a direct substitution of the list into the string. Unfortunately, I don't think there is a better, convenient solution.

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

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.

Upvotes: 3

Related Questions