IN operator in OLEDB

Question

I am using an OLEDB connection to read data from excel files. I am having problems using the IN operator in a Select query. Below is my request,

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 get empty results, but if I give only one value, I get the result. Please, help.

Answer 1

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

very different :

 where someval in (123,456,789) 

The second line checks someval for 3 numeric values; the first line checks someval for a single-line value that contains numbers and commas (but these numbers and commas do not matter).

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

• dynamically write SQL to have one parameter per value, i.e. in (?,?,?,?)
• using some backend function for separation - for example, STRING_SPLIT in recent versions of SQL Server (this will be very specific for the backend); I don't know enough about Excel to tell if such a function exists.

Answer 2

This is a very common mistake.
The IN statement expects a list of values , but you supply a single value that contains a list. You must create a different parameter for each value in the EmployeeIds list.

Here is one way to do this:

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