SqlParameter for IN-Clausel (int values)?

Hi Ng,

i have a sucking problem......


select * from test where nId IN (1, 2, 3)


Now i want to execute this with an sqlcommand and more interesting with
an sqlParameter:

sqlCommand.CommandText = select * from test where nId in (@IN-Values)";

I first tried:

SqlParameter sqlParameter = new SqlParameter("@Values", "1, 2, 3");

Exeption: Syntax error converting the nvarchar value '1, 2, 3' to a
column of data type int

This is very logical. But how can i do?

I then tried to use an int32 array or an arraylist with int values:


int[] test = new Int32[] {1, 2, 3};
ArrayList test2 = new ArrayList();
test2.Add(1);
test2.Add(2);

SqlParameter sqlParameter = new SqlParameter("@Values", test);
SqlParameter sqlParameter = new SqlParameter("@Values", test2);

This didn't work, too. I cannot give an array or arraylist to a
parameter as value.


How can i resolve my problem? I don't want to do without sqlparameter
and i only want ONE sqlparameter! Is it impossible?


Thanks,
Roland

KerryMoorman
Thu Sep 29 11:43:06 CDT 2005

Roland,

http://support.microsoft.com/default.aspx?scid=kb;en-us;555167

Kerry Moorman


"Roland Müller" wrote:

> Hi Ng,
>
> i have a sucking problem......
>
>
> select * from test where nId IN (1, 2, 3)
>
>
> Now i want to execute this with an sqlcommand and more interesting with
> an sqlParameter:
>
> sqlCommand.CommandText = select * from test where nId in (@IN-Values)";
>
> I first tried:
>
> SqlParameter sqlParameter = new SqlParameter("@Values", "1, 2, 3");
>
> Exeption: Syntax error converting the nvarchar value '1, 2, 3' to a
> column of data type int
>
> This is very logical. But how can i do?
>
> I then tried to use an int32 array or an arraylist with int values:
>
>
> int[] test = new Int32[] {1, 2, 3};
> ArrayList test2 = new ArrayList();
> test2.Add(1);
> test2.Add(2);
>
> SqlParameter sqlParameter = new SqlParameter("@Values", test);
> SqlParameter sqlParameter = new SqlParameter("@Values", test2);
>
> This didn't work, too. I cannot give an array or arraylist to a
> parameter as value.
>
>
> How can i resolve my problem? I don't want to do without sqlparameter
> and i only want ONE sqlparameter! Is it impossible?
>
>
> Thanks,
> Roland
>
>
>

john
Thu Sep 29 12:25:16 CDT 2005

An easier way to do this is write a stored procedure that uses the fn_split
function. Go online and look for the fn_split function, put it in your
master table and use it. This will allow you to pass in an IN parameter or
any length and have it work. This is a glaring oversite on SQL Server part.
The link here is complicated.

John
"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:217036B6-F252-47A2-A1A6-F2BA149B238D@microsoft.com...
> Roland,
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;555167
>
> Kerry Moorman
>
>
> "Roland Müller" wrote:
>
>> Hi Ng,
>>
>> i have a sucking problem......
>>
>>
>> select * from test where nId IN (1, 2, 3)
>>
>>
>> Now i want to execute this with an sqlcommand and more interesting with
>> an sqlParameter:
>>
>> sqlCommand.CommandText = select * from test where nId in (@IN-Values)";
>>
>> I first tried:
>>
>> SqlParameter sqlParameter = new SqlParameter("@Values", "1, 2, 3");
>>
>> Exeption: Syntax error converting the nvarchar value '1, 2, 3' to a
>> column of data type int
>>
>> This is very logical. But how can i do?
>>
>> I then tried to use an int32 array or an arraylist with int values:
>>
>>
>> int[] test = new Int32[] {1, 2, 3};
>> ArrayList test2 = new ArrayList();
>> test2.Add(1);
>> test2.Add(2);
>>
>> SqlParameter sqlParameter = new SqlParameter("@Values", test);
>> SqlParameter sqlParameter = new SqlParameter("@Values", test2);
>>
>> This didn't work, too. I cannot give an array or arraylist to a
>> parameter as value.
>>
>>
>> How can i resolve my problem? I don't want to do without sqlparameter
>> and i only want ONE sqlparameter! Is it impossible?
>>
>>
>> Thanks,
>> Roland
>>
>>
>>

rviray
Thu Sep 29 12:49:29 CDT 2005


please excuse my ignorance...in this scenario what is using
SQLParameter buying you? I mean, you are building the CommandText via
string? right?

So, why not just do

string infoBegin = "Select * from test where nID in(";
string info = ""
string infoEnd = ")"

info = info + <logic to build 1,2,3>

sqlCommand.CommandText = infoBegin + info + infoEnd;

If you want to use a Sql Store Proc to accomplish this, post back and
can give you some code..

--
rvira
-----------------------------------------------------------------------
rviray's Profile: http://www.msusenet.com/member.php?userid=421
View this thread: http://www.msusenet.com/t-187108856

roland
Fri Sep 30 01:36:24 CDT 2005

Not really. There is a class with constant strings containing
sqlcommands; the are used from different places in a program or even in
different programs.
I take the contants and must fill them with MY current values
(parameters); i cannot easily modify the command text.

rviray wrote:
> please excuse my ignorance...in this scenario what is using a
> SQLParameter buying you? I mean, you are building the CommandText via a
> string? right?