IN query parameter

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

Guest

Hello

I'm trying to use a parameter to specify values for an IN clause, if I do this, everything works fine

sqlSelectCommand1.CommandText = "SELECT [ID] FROM [TableName] WHERE ([ColumnName] IN (@ColumnValueList)) ORDER BY [ID]"
sqlSelectCommand1.Connection = this.sqlConnection1

sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ColumnValueList", System.Data.SqlDbType.Int))
sqlSelectCommand1.Parameters["@ColumnValueList"].Value = "10"

However, if I replace that last line with the following (i.e. more than one value) I get "Input String was not in a correct format.

sqlSelectCommand1.Parameters["@ColumnValueList"].Value = "10, 20"

How can I do this

Regards

Dav
 
Dave said:
I'm trying to use a parameter to specify values for an IN clause, if
I do this, everything works fine:
Click to expand...

<snip>

I don't believe there's any way of specifying multiple parameters for
an "IN" clause in a single parameter.
 
You can't use a single parameter where it is actually a list of values.

You could try to pass a single character parameter and have a "split" like
function returns the list to feed the IN clause :
http://www.devx.com/tips/Tip/20009

Or also you could use multiple parameters (one for each value in the IN
clause).

Patrice

Dave said:
Hello,

I'm trying to use a parameter to specify values for an IN clause, if I do this, everything works fine:

sqlSelectCommand1.CommandText = "SELECT [ID] FROM [TableName] WHERE
Click to expand...
([ColumnName] IN (@ColumnValueList)) ORDER BY [ID]";
sqlSelectCommand1.Connection = this.sqlConnection1;

sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ColumnValueList",
System.Data.SqlDbType.Int));
sqlSelectCommand1.Parameters["@ColumnValueList"].Value = "10";

However, if I replace that last line with the following (i.e. more than
Click to expand...
one value) I get "Input String was not in a correct format."
sqlSelectCommand1.Parameters["@ColumnValueList"].Value = "10, 20";


How can I do this?


Regards,

Dave
Click to expand...
 
http://www.knowdotnet.com/articles/temptables.html
Dave said:
Hello,

I'm trying to use a parameter to specify values for an IN clause, if I do this, everything works fine:

sqlSelectCommand1.CommandText = "SELECT [ID] FROM [TableName] WHERE
Click to expand...
([ColumnName] IN (@ColumnValueList)) ORDER BY [ID]";
sqlSelectCommand1.Connection = this.sqlConnection1;

sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ColumnValueList",
System.Data.SqlDbType.Int));
sqlSelectCommand1.Parameters["@ColumnValueList"].Value = "10";

However, if I replace that last line with the following (i.e. more than
Click to expand...
one value) I get "Input String was not in a correct format."
sqlSelectCommand1.Parameters["@ColumnValueList"].Value = "10, 20";


How can I do this?


Regards,

Dave
Click to expand...
 
Back
Top