Access parametrized IN() query

Question

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;
                            command.Parameters.Add(commandParameter);
                        }
                    }

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

Michael

Because a single parameter cannot represent two (or more) values — Steve, Oct 02 '17 at 18:56
Please show the code you used to build the parameter and the call to your database — Steve, Oct 02 '17 at 18:58
Possible duplicate: [SQL Server SP - Pass parameter for “IN” array list?](https://stackoverflow.com/questions/537087/sql-server-sp-pass-parameter-for-in-array-list) — Alan Larimer, Oct 02 '17 at 19:01

score 1 · Accepted Answer · answered Oct 02 '17 at 19:06

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
        inClause.Add("?");

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

    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
    cmd.Parameters.AddRange(parameters.ToArray());
    return cmd;
}

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

Access parametrized IN() query

1 Answers1