1

I've been struggling with this all day - been all over SO and Google and can't find anything to help. I have a SQL query in an XSD tableadapter that looks something like this:

SELECT column A, column B, column C
FROM Table
WHERE variable1 = @UserID
AND variable2 IN (@ListOfItems)

Variable 2 is an int32 column.

So, I want to pass a comma-separated list of values to the @ListOfItems parameter. However, whenever I try, no matter how I configure the adapter (change the provider and data types for the parameter), it just kicks up and says "Conversion failed converting the varchar value '1,2,3' to data type int.

I can see that the string I'm generating is valid - if I copy the query into a SQL editor and hard code the string, it works fine.

Is what I'm trying to do even possible with a tableadapter?

Any help is appreciated beyond measure.

Tony D
  • 55
  • 1
  • 1
  • 5
  • You might find your answer here : http://stackoverflow.com/questions/18513441/sql-parameter-with-multiple-values – SoulTrain Feb 02 '15 at 16:58

2 Answers2

0

Please take a look at Arrays and Lists in SQL Server

Sameer Alibhai
  • 3,092
  • 4
  • 36
  • 36
  • Yeah unfortunately this is not an easy solution. I can tell you at my last gig we used a CLR object @IntTable. That worked really well. But it requires you creating a new SQL server CLR type and deploying it to the server. I believe he discusses this in the writeup – Sameer Alibhai Feb 02 '15 at 17:11
-1
SqlCommand command = new SqlCommand("select Colum A,Colum B,Colum C from Table where variable1 =" + @userID + "and variable1 in ("+@listofitems+")", yourConnectionObject);

but @userID , @listofitems is varaible in ASP.NET in my example

Ega
  • 55
  • 19
  • 2
    I think this solution suffers from sql injection vulnerability. – Sameer Alibhai Feb 02 '15 at 17:04
  • This does exactly what I need. SQL Injection is not an issue as the code is generating the list of values - they don't come from a user input. Thanks everyone for the very swift and very useful replies – Tony D Feb 02 '15 at 17:11
  • Not necessarily only a SQL injection issue. Make sure you replace any quotation marks in your parameters as that will break the SQL as well – Sameer Alibhai Feb 02 '15 at 17:12
  • Thanks Sameer. My parameters are all integers so no problems there!. Good point to watch out for when using strings though. – Tony D Feb 05 '15 at 13:59