C# Excel Query Issue

Question

This code works properly. But when i want to change range, gives OleDBException.

OleDbCommand dates = new OleDbCommand("SELECT date FROM ["Sheet1$A1:B150"] ", baglanti);
OleDbDataReader reader= dates.ExecuteReader();
while (reader.Read())
{
    list.Add(reader["date"]);
}

Range Changed Version:

OleDbCommand dates = new OleDbCommand("SELECT date FROM ["Sheet1$A250:B350"] ", baglanti);
OleDbDataReader reader= dates.ExecuteReader();

while (reader.Read())
{
    list.Add(reader["date"]);
}

EXCEPTION IS:

System.Data.OleDb.OleDbException was unhandled by user code
ErrorCode=-2147217904
HResult=-2147217904
Message=No value is entered for one or more required parameters.
Source=Microsoft Access Database Engine
StackTrace:
konum:    System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
konum:   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
konum: System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
konum: System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
konum: System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
konum: Hasta_Bulucu.Form1.bul_DoWork(Object sender, DoWorkEventArgs e) C:\Users\Furka\documents\visual studio 2015\Projects\Hasta Bulucu\Hasta      Bulucu\Form1.cs içinde: satır 269
konum: System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
konum: System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
InnerException:

could you please add the connection string you use to initialize OledDbConnection? — andrews
– andrews, Commented Mar 3, 2017 at 9:13
@andrews OleDbConnection baglanti = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePick.FileName.ToString() + ";Mode=Read;Extended Properties='Excel 12.0 xml;HDR=YES;IMEX=1;'"); — F K
– F K, Commented Mar 3, 2017 at 9:15
@andrews when i set range A1 to R250 it includes header of the sheet but A150 to R300 not including header. So sql command cannot find column names. I need to include header information to this selection. So how to do it? — F K
– F K, Commented Mar 3, 2017 at 9:18

andrews · Accepted Answer · 2017-03-03 09:24:07Z

1

Try the following:

Set HDR=NO in the connection string like:

OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
                         filePick.FileName.ToString() + 
";Mode=Read;Extended Properties='Excel 12.0 xml;HDR=NO;IMEX=1;'");

also try setting IMEX to 0 and 2 and try different combinations with HDR=NO.

You're right when the range is not including column names you may get the error. Therefore, you need to try reading file by column indexes, not names.

answered Mar 3, 2017 at 9:24

andrews

2,1832 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

F K Over a year ago

Column indexes idea makes sense. But i stuck in sql. I can't query by columnID like A, B, C .... I tried SELECT A FROM [Sheet1$] but still throws exception (HDR=NO) @andrews

andrews Over a year ago

@FK yes, I thought about that but was not able to find any samples. What if you try to use R1C1 notation? Or just query columns by index like [1] . Also do SELECT * and open the result set in the debugger and try to see what you get in column/field names in there.

F K Over a year ago

@adrews finally done. It must be F1 ..... Fn not directly column name like A, B etc.. Thank you again.

andrews Over a year ago

@FK you're welcome! You now have enough rep for up-voting. Would appreciate if you also up vote this answer and the one we have worked on yesterday.

F K Over a year ago

you got it, enjoy :)

Collectives™ on Stack Overflow

C# Excel Query Issue

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related