UPDATE with WHERE IN clause

  • Thread starter Thread starter spp
  • Start date Start date
S

spp

I want to UPDATE a SQL table with a SqlCommand
The Update command is
UPDATE mytable SET col1 = @param1 WHERE STR(intcolumn,6,0) IN (@param2)
My questions are
what type of parameter should be @param2 to satisfy my update because
intcolumn is an int column and I populate @param2
with a loop like
foreach (int index in GridView1.GetSelectedIndices()
{
string key = (string)GridView1.DataKeys[index].value.ToString();
mysearchlist += "'"+key+"'"+","
}
and @param have the value of mysearchlist.SubString(0,
mysearchlist.Length -1)
 
spp said:
I want to UPDATE a SQL table with a SqlCommand
The Update command is
UPDATE mytable SET col1 = @param1 WHERE STR(intcolumn,6,0) IN
(@param2) My questions are
what type of parameter should be @param2 to satisfy my update because
intcolumn is an int column and I populate @param2
with a loop like
foreach (int index in GridView1.GetSelectedIndices()
{
string key = (string)GridView1.DataKeys[index].value.ToString();
mysearchlist += "'"+key+"'"+","
}
and @param have the value of mysearchlist.SubString(0,
mysearchlist.Length -1)
Click to expand...

I'd pass each individual value as a parameter:
UPDATE mytable SET col1 = @param1 WHERE str(intcolumn, 6, 0) IN
(@param2, @param3, ... , @paramN)

The way you want to do it doesn't work, parameters are values and
never 'part of' the query and you want to make @param2 part of the
query.

Frans

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
But I don't know how many parameters to pass.
It's not always the same number.

Frans Bouma said:
spp said:
I want to UPDATE a SQL table with a SqlCommand
The Update command is
UPDATE mytable SET col1 = @param1 WHERE STR(intcolumn,6,0) IN
(@param2) My questions are
what type of parameter should be @param2 to satisfy my update because
intcolumn is an int column and I populate @param2
with a loop like
foreach (int index in GridView1.GetSelectedIndices()
{
string key = (string)GridView1.DataKeys[index].value.ToString();
mysearchlist += "'"+key+"'"+","
}
and @param have the value of mysearchlist.SubString(0,
mysearchlist.Length -1)
Click to expand...

I'd pass each individual value as a parameter:
UPDATE mytable SET col1 = @param1 WHERE str(intcolumn, 6, 0) IN
(@param2, @param3, ... , @paramN)

The way you want to do it doesn't work, parameters are values and
never 'part of' the query and you want to make @param2 part of the
query.

Frans

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...
 
Just create the IN part dynamically (concatenate) and add all required
params.
Note that there is a limit of parameters (or values) in IN clause.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

spp said:
But I don't know how many parameters to pass.
It's not always the same number.

Frans Bouma said:
spp said:
I want to UPDATE a SQL table with a SqlCommand
The Update command is
UPDATE mytable SET col1 = @param1 WHERE STR(intcolumn,6,0) IN
(@param2) My questions are
what type of parameter should be @param2 to satisfy my update because
intcolumn is an int column and I populate @param2
with a loop like
foreach (int index in GridView1.GetSelectedIndices()
{
string key = (string)GridView1.DataKeys[index].value.ToString();
mysearchlist += "'"+key+"'"+","
}
and @param have the value of mysearchlist.SubString(0,
mysearchlist.Length -1)
Click to expand...

I'd pass each individual value as a parameter:
UPDATE mytable SET col1 = @param1 WHERE str(intcolumn, 6, 0) IN
(@param2, @param3, ... , @paramN)

The way you want to do it doesn't work, parameters are values and
never 'part of' the query and you want to make @param2 part of the
query.

Frans

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...
Click to expand...
 
Back
Top