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("@pIngredients", myList);

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

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

"jayv" wrote in message
news:805AB46E-3C41-44B0-A506-AC2AC4D7A081@microsoft.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("@pIngredients", 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/library/0wf2yk2k(VS.80).asp x

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

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("@pIngredients", 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

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

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" wrote in message
news:805AB46E-3C41-44B0-A506-AC2AC4D7A081@microsoft.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("@pIngredients", myList);
>
> the error is: System.ArgumentException: No mapping exists from object type
> System.String[] to a known managed provider native type.
>
>
>

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

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