How do i supply a list to an SQL "IN" list via parameters?

[=?Utf-8?B?amF5dg==?=]

The best way to explain it, is to show you what i "thought" should work and
hope that you can show me a way to make it work please and thanks...

string ingredients = "pepper,salt,eggs";
strSql += " AND ingredients IN @pIngredients";
string[] myList = ingredients.Split(',');
sda.SelectCommand.Parameters.AddWithValue("@pIngre dients", myList);

the error is: System.ArgumentException: No mapping exists from object type
System.String[] to a known managed provider native type.

[Mr. Arnold]

"jayv" <ja**@discussions.microsoft.comwrote in message
news:80**********************************@microsof t.com...

The best way to explain it, is to show you what i "thought" should work
and
hope that you can show me a way to make it work please and thanks...

string ingredients = "pepper,salt,eggs";
strSql += " AND ingredients IN @pIngredients";
string[] myList = ingredients.Split(',');
sda.SelectCommand.Parameters.AddWithValue("@pIngre dients", myList);

the error is: System.ArgumentException: No mapping exists from object type
System.String[] to a known managed provider native type.

You can't pass an array to SQL Server as some kind of parameter that I know
about. A manager provider is anyone that makes the DLL/Namespace for data
access to a database, like MS SQL Server, Oracle, Sybase, etc, etc.

Mylist is a string array. The best you can do is pass it as one delimited
string into a Stored Procedure, and you do a for loop or something to
extract the delimited data in the string inside the SP.

This is about as close as I am going to get for Native Types for you.

http://msdn2.microsoft.com/en-us/lib...2k(VS.80).aspx

[Andrew Morton]

jayv wrote:

The best way to explain it, is to show you what i "thought" should
work and hope that you can show me a way to make it work please and
thanks...

string ingredients = "pepper,salt,eggs";
strSql += " AND ingredients IN @pIngredients";
string[] myList = ingredients.Split(',');
sda.SelectCommand.Parameters.AddWithValue("@pIngre dients", myList);

the error is: System.ArgumentException: No mapping exists from object
type System.String[] to a known managed provider native type.

http://www.sommarskog.se/arrays-in-sql.html

Andrew

[Stephany Young]

So ... you want the additional classe to be executed as if it were

and ingredients in ('pepper','salt','eggs')

therefore you need to build a string that looks like:

" and ingredients in ('pepper','salt','eggs')"

To get the puncuation right you can use a technique like:

string ingredients = "pepper,salt,eggs";

strSql += String.Format(" and ingredients in ('{0}')",
ingredients.Split(',').Join("','"));

Then you just have to remember to assign strSql to the CommandText property
of sda.SelectCommand.

"jayv" <ja**@discussions.microsoft.comwrote in message
news:80**********************************@microsof t.com...

The best way to explain it, is to show you what i "thought" should work
and
hope that you can show me a way to make it work please and thanks...

string ingredients = "pepper,salt,eggs";
strSql += " AND ingredients IN @pIngredients";
string[] myList = ingredients.Split(',');
sda.SelectCommand.Parameters.AddWithValue("@pIngre dients", myList);

the error is: System.ArgumentException: No mapping exists from object type
System.String[] to a known managed provider native type.

[Marc Gravell]

danger; this risks injection and doesn't allow query-plan re-use.
The blog that Andrew cited gives much better solutions to this
problem, along with other information.

Marc