Roofy Roofy - 1 year ago 136
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;
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

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
        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

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