Roofy Roofy - 3 years ago 230
SQL Question

Access parametrized IN() query

I am trying to run this query:

SELECT * FROM Tabela1 WHERE Pole1 IN (@parameter)

When the window with "choose value" appears, I put: "10, 50" and I receive 0 rows (should be 2).

When I put only "10", or only "50" it works and returns 1 row for each query.

I am using Access 2013 - what am I doing wrong?

using (DbConnection connection = new T())
connection.ConnectionString = query.DatabaseConnection.ConnectionString;
using (DbCommand command = connection.CreateCommand())
command.CommandText = query.QuerySql.Sql;
command.CommandType = CommandType.Text;
command.CommandTimeout = query.QuerySql.CommandTimeout;

if (query.QuerySql.Parameters != null)
foreach (var parameter in query.QuerySql.Parameters)
var commandParameter = command.CreateParameter();
commandParameter.ParameterName = $"@{parameter.Name}";
commandParameter.Value = parameter.Value;

I create the query like that:

QuerySql sql = new QuerySql("SELECT * FROM Tabela1 WHERE Pole1 IN(@parameter)", new List<ISqlParameter>()
new SqlMultiNumberParameter("parameter", new List<string>() { "10", "50" }, "Test parameter")

the parameter.Value returns string like that: "10, 50"

Best regards


Answer Source

You cannot use a single parameter to express a list of values to be passed to an IN clause. There are some ORM (like Dapper for example) that allows you to pass a list of values and build for you the correct IN clause.

If you want to do the same thing you need something like this method

public OleDbCommand GetPoles(List<int> polesID)
    // Base text of the query
    string cmdText = @"SELECT * FROM TABLE1 WHERE Pole1 IN(";

    // where we store the 'name' of the parameters. (OleDb doesn't care)
    List<string> inClause = new List<string>();

    // where we store the parameters and their values
    List<OleDbParameter> parameters = new List<OleDbParameter>();
    foreach(int id in polesID)
        // Add a placeholder for the parameter

        // Build the parameter and store it away
        OleDbParameter p = new OleDbParameter("p" + id.ToString(), OleDbType.Integer);
        p.Value = id;

    OleDbCommand cmd = new OleDbCommand();

    // Build the command text: IN(?,?,?). A ? placeholder for each parameter
    cmd.CommandText = cmdText + string.Join(",", inClause.ToArray()) + ")";

    // pass all the parameters to the command and return it
    return cmd;

Now you just need to set the connection and you can execute the command

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download