2

What am I doing wrong here?

string favorites = "210,213";

sqlCommand.CommandText = "select * from clients (nolock) 
    where Deleted = 0 and ClientID in (@favorites)";
sqlCommand.Parameters.AddWithValue("@favorites", favorites);

One other note: the "210,213" is just an example. It could be any length of numbers. That is why I am using the SQL IN operator.

Thanks.

user390480
  • 1,655
  • 4
  • 28
  • 61

2 Answers2

3

That's not how parameters work. You are actually trying to get clients where ClientID is equal to '210,213'. Well, that's not a number, so there's a failure.

Parametrized queries and IN clause are actually not trivially implemented together if your IN list size is dynamic, changing from time to time.

Read this SO question and answers: Parameterize an SQL IN clause

Generally, you'd have to dynamically assemble your SQL query, always keeping in mind issues like SQL Injection.

Further reading:

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
0

I'm quite certain you are going to need to generate the commandtext with the favorites string. There is no built in sql type for an array. You can probably protect yourself against a sql attack by making sure you generate the favorites from a list of integers.

ek_ny
  • 10,153
  • 6
  • 47
  • 60
  • It's not an array. I am using the SQL IN operator. – user390480 Aug 24 '11 at 16:09
  • understood-- but I think behind the scenes your parameter must match up to a type in sql server. Your asking SQL server to treat your parameter as an array, but sql server doesn't have a built in array. Let's say you were writing your command in T-SQL how would it translate? What would the datatype of @favorites be? A string? Try it-- it won't work. I've run into this before in T-SQL and I'm quite certain your issue is the same from the .net side. I think the answer above says this as well. – ek_ny Aug 24 '11 at 17:39