Can someone tell me why this SQL query not working

Question

I followed this answer,

How can I supply a List<int> to a SQL parameter?

Please see these questions of mine for understanding scenario,

How can I update Crate IDs of List of Fruits in single SQL query in c#

What I am trying and not working

private void relate_fruit_crate(List<string> selectedFruitIDs, int selectedCrateID)
{
    string updateStatement = "UPDATE relate_fruit_crate set CrateID = @selectedCrateID where FruitID = @selectedFruitIDs";

    using (SqlConnection connection = new SqlConnection(ConnectionString()))
    using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
    {
        connection.Open();
        cmd.Parameters.Add(new SqlParameter("@selectedCrateID", selectedCrateID.ToString()));
        cmd.Parameters.Add(new SqlParameter("@selectedFruitIDs", String.Join(",",selectedFruitIDs.ToArray())));

        cmd.ExecuteNonQuery();
    }
}

My code runs without any error,

define "not working". The data does not update in database? Do you try to run in SQL server directly ? — Raptor, May 14 '13 at 10:36
Please read http://meta.stackexchange.com/questions/10647/how-do-i-write-a-good-title — Soner Gönül, May 14 '13 at 10:37

score 4 · Accepted Answer · edited May 14 '13 at 11:27

4

You need to use the IN keyword in your scenario. The problem is that the SqlCommand.Parameters pattern does not build the query itself, but calls a stored procedure on the database:

exec sp_executesql N'UPDATE relate_fruit_crate set CrateID = @selectedCrateID where FruitID in(''@selectedFruitIDs'')', N'@selectedCrateID nvarchar(1),@selectedFruitIDs nvarchar(5)', @selectedCrateID = N'1', @selectedFruitIDs = N'1,2'

This will not work as the array is escaped.

The workaround would be to either use a normal StringBuilder to create the query. (Warning! SQL Injection) or to call the query for each ID separately.

Maybe there's a way to do this with the SqlCommand.Parameters, but I could not find one.

OLD POST::

string updateStatement = "UPDATE relate_fruit_crate set CrateID IN ('@selectedCrateID') where FruitID = '@selectedFruitIDs'";

[....]

cmd.Parameters.Add(new SqlParameter("@selectedFruitIDs", String.Join("','",selectedFruitIDs.ToArray())));

and equals (=) query will only match a single value.

edited May 14 '13 at 11:27

marc_s

732,580
175
1,330
1,459

answered May 14 '13 at 10:39

krizzzn

1,413
11
13

Yes it needs to be `In` instead of `=`, but the parameter will still end up being just one concatenated string value. – juharr May 14 '13 at 10:49
this works only if your IDs are numeric. You need to add a singlequotes to each value as [juharr points out](http://stackoverflow.com/questions/16540951/can-someone-tell-me-why-this-sql-query-not-working/16541024#comment23757338_16541108) – krizzzn May 14 '13 at 10:50
But you cannot parameterize a concatenated list of strings with quotes in them as that will just escape the quotes. I'm not sure how to accomplish this with parameters. – juharr May 14 '13 at 10:53
@krizzzn in your latest post above, you are using "=" not "In" – Mathematics May 14 '13 at 11:12
In the code shown you are looking for a literal `'@selectedFruitIDs'` - I doubt that is the `FruitID` of **any** of them, but is doesn't relate ***at all*** to the parameter value – Marc Gravell May 14 '13 at 11:17

Marc Gravell · Answer 2 · 2013-05-14T11:19:55.870

Multi-value parameter queries are a bit of a pain in TSQL. There are options like table-valued parameters, or "split" UDFs - otherwise... it is a bit tricky. You end up having to add multiple parameters (depending on the data), and change the query to suit. If I may suggest... a library like "dapper" may help you here - it is designed to make scenarios like this easy:

using Dapper; // at the top of your code file, to enable dapper
...
private void relate_fruit_crate(List<string> selectedFruitIDs, int selectedCrateID)
{
    // note the slightly unusual "in" here (no paranethesis) - that is because
    // dapper is going to do some voodoo...    
    using (SqlConnection connection = new SqlConnection(ConnectionString()))
    {
        connection.Open();
        connection.Execute(
            "UPDATE relate_fruit_crate set CrateID = @selectedCrateID where FruitID in @selectedFruitIDs",
            new { selectedFruitIDs, selectedCrateID });
    }
}

here "dapper" does all the work of figuring out how to express that in using multiple parameters, adding the correct number of parameters. It is also just much easier (in particular, look at how little work we did with commands and parameters; it handles readers nicely too).

Dapper is freely available from NuGet

Can someone tell me why this SQL query not working

2 Answers2