469,347 Members | 18,544 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

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

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.

Oct 25 '07 #1
4 7355

"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

Oct 25 '07 #2
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
Oct 26 '07 #3
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.
Oct 26 '07 #4
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
Oct 26 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by karthik | last post: by
9 posts views Thread by Emanuele Aina | last post: by
3 posts views Thread by delirman49 | last post: by
reply views Thread by Atos | last post: by
3 posts views Thread by =?ISO-8859-1?Q?Andreas_M=FCller?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.