C# Sql update with multiple values in a single string not updating

Question

I want to update multiple values to sql at a time using following query but it is not updating. if I update single value it is updating. What mistake I am making it following C# update statement

for (i = 0; i < totalbarcodes; i++)
{

barcode = myCollection[i] + "," + myCollection[i + 1] + "," + myCollection[i + 2] + "," + myCollection[i + 3] + "," + myCollection[i + 4] ;

SqlCommand SqlString4 = new SqlCommand("Update quex set docudisp = 70 where docudisp = 60  and admindisp in (40,43,91) and barcode in (@barcode)", con);

SqlString4.Parameters.AddWithValue("@barcode", barcode);
try
{
     con.Open();
      SqlString4.ExecuteNonQuery();
      con.Close();
}
catch (Exception ex)
{ 
    MessageBox.Show(ex.Message); 
}

}

Thanks in advance for your replies..

are you receiving an error ? is this condition met in your DB - where docudisp = 60 and admindisp in (40,43,91) and barcode in (@barcode) - ? and im only seeing you update one value - set docudisp = 70 Please elaborate — JanivZ, Mar 30 '14 at 09:55

score 2 · Accepted Answer · edited May 23 '17 at 10:26

No you can't pass a string as a parameter for the IN clause.

From the point of view of your database it is like it is receiving this command

SqlCommand SqlString4 = new SqlCommand(@"Update quex set docudisp = 70 
      where docudisp = 60  and admindisp in (40,43,91) 
      and barcode in ('1,2,3,4,5')", con);

This is a case where you could try to use a string concatenation to build the sql command, provided that you have a full control over the values in the barcode string

SqlCommand SqlString4 = new SqlCommand(@"Update quex set docudisp = 70 
      where docudisp = 60  and admindisp in (40,43,91) 
      and barcode in (" + barcode + ")", con);

There is also this answer here on StackOverflow that show a technique to overcome this limitation using a parameter (the answer that use Table Valued Parameters, not the accepted one)

However, given the fact that you know the number of parameters required in the IN clause then why don't you build a specific query with the exact number of parameters required?

// Create the command outside the loop defining the 5 parameters required
SqlCommand SqlString4 = new SqlCommand(@"Update quex set docudisp = 70 
    where docudisp = 60 and admindisp in (40,43,91) 
    and barcode in (@b1, @b2, @b3, @b4, @b5)", con);

// Create the five parameters with a dummy integer 
SqlString4.Parameters.AddWithValue("@b1", 0);
SqlString4.Parameters.AddWithValue("@b2", 0);
SqlString4.Parameters.AddWithValue("@b3", 0);
SqlString4.Parameters.AddWithValue("@b4", 0);
SqlString4.Parameters.AddWithValue("@b5", 0);

con.Open();

// Increment the loop with i+=5 because it is not clear 
// if you have more that 5 items in the myCollection array
for (i = 0; i < totalbarcodes; i+=5)
{
    SqlString4.Parameters["@b1"].Value = myCollection[i]);
    SqlString4.Parameters["@b2"].Value = myCollection[i+1]);
    SqlString4.Parameters["@b3"].Value = myCollection[i+2]);
    SqlString4.Parameters["@b4"].Value = myCollection[i+3]);
    SqlString4.Parameters["@b5"].Value = myCollection[i+4]);

    try
    {
         SqlString4.ExecuteNonQuery();
    }
    catch (Exception ex)
    { 
        MessageBox.Show(ex.Message); 
    }
}
con.Close();

Notice that I have changed the increment in the for loop. If you have more that 5 items in the collection you need to index them correcly

score 0 · Answer 2 · answered Mar 30 '14 at 10:46

You can make an user defined function for this problem

ALTER FUNCTION [dbo].[fnDelimitedBigIntToTable]
(
@List varchar(max), @Delimiter varchar(10)
)
RETURNS @Ids TABLE
(Id bigint) AS
BEGIN
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @List = LTRIM(RTRIM(@List)) + @Delimiter
SET @pos = CHARINDEX(@Delimiter, @List, 1)
WHILE @pos > 0
    BEGIN
    SET @list1 = LTRIM(RTRIM(LEFT(@List, @pos - 1)))
    IF @list1 <> ''
        INSERT INTO @Ids(Id)
        VALUES (CAST (@list1 AS bigint))
        SET @List = SUBSTRING(@List, @pos + 1, LEN(@List))
        SET @pos = CHARINDEX(@Delimiter, @list, 1) END
RETURN

and the usage is

Update quex set docudisp = 70 where docudisp = 60  and admindisp in (select * from [dbo].[fnDelimitedBigIntToTable](@yourParameters,','))

@yourParameters is comma delimited string for your parameters

C# Sql update with multiple values in a single string not updating

2 Answers2