IN Operator in OLEDB

Question

I am using OLEDB connection to read data from excel files. I am facing issues while using IN operator in the Select query. Below is my query,

string EmployeeIds = "'1231','1232','1233'";
SELECT [Employee Number],[Employee Name],[First In Time],[Last Out Time],[Total Work Hours] 
FROM [Sheet0$A2:J] 
WHERE  [Employee Number] IN (?);

 comm.Parameters.AddWithValue("?",EmployeeIds);

I am getting empty results but if I give only single value then I am getting result. Please help.

Tager

Answer 1

where someval in ('123,456,789')

is very different to:

where someval in (123,456,789)

The second line tests someval against 3 numeric values; the first line tests somevalagainst a single string value that happens to contain numbers and commas (but those numbers and commas are irrelevant).

You cannot do what you want without (one of):

• writing the SQL dynamically to have one parameter per value, i.e. in (?,?,?,?)
• making use of some feature of the backend to do the split - for example STRING_SPLIT in recent versions of SQL Server (this will be very backend specific); I do not know enough about Excel to advise on whether such a feature exists

Keystroke

Answer 2

This is a very common mistake.
The IN operator expect a list of values, but you are supplying a single value that happens to contain a list. You should create a different parameter for each value in the EmployeeIds list.

Here is one way to do it:

string EmployeeIds = "'1231','1232','1233'";
var values = EmployeeIds.Split(',');

using(var command = new OleDbCommand())
{
    var sql = "SELECT [Employee Number], [Employee Name], [First In Time], [Last Out Time], [Total Work Hours] "+
              "FROM [Sheet0$A2:J] "+
              "WHERE [Employee Number] IN (";

    for(int i=0; i < values.Length; i++) 
    {
        // Please note that string interpolation will work only with c# 6 or later.
        // If you are working with vs 2013 or earlier, use string.Format instead.
        sql = $"{sql} @{i},";
        command.Parameters.Add($"@{i}", OleDbType.Int).Value = values[i].Trim(new char[] {'}); // You don't need the ' anymore since you are working with parameters now...
    }

    command.CommandText = sql.TrimEnd(',') +");";
    command.Connection = con;
    using(var reader = Command.ExecuteReader())
    {
        while(reader.Read())
        {
            // do your stuff with the data
        }

    }
}

Holophrastic

Something to make it better: Use a List to store the parameters placeholders (and they should be a "?" because we don't know the underlying db) and build the final query just using string.Join instead of many string operations (with a lot of parameters it could be a performance issue), also parameters are of type OleDb.Int32 but the op uses strings. This could work or not depending on the engine. Better do the proper thing and convert them – Destructible

@Destructible you mean like your answer here? I actually thought about it but decided it would be kind of plagiarism, and though it's the sincerest form of flattery I think I better leave credit where credit is due :-) – Holophrastic

Ah That's where it was. Perhaps we should close this as duplicate then, but it is not possible. Pretty sure there are others duplicates around – Destructible

Yes, you are probably correct, however.... – Holophrastic