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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
jayv said:
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.
Click to expand...

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).aspx
 
jayv said:
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.
Click to expand...

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

Andrew
 
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.
 
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
 
Back
Top