Click here to Skip to main content
11,634,561 members (73,057 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel query OleDb
I am reading an excel spreadsheet in C# using OleDb connection. I get an error (See below) if any of the column headers in the excel is "Domain".

The error message is (raw)
System.Data.OleDb.OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, Strin
g srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at ExcelReader.Program.Main(String[] args) in C:\Users\Eser\Development\ExcelReader\ExcelReader\Program.cs:line 32
More message that I gathered from the Exception handling:

Message: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Native: -533136361
Source: IErrorInfo.GetSource failed with E_FAIL(0x80004005).
SQL: 3000
------------------------------------------------------------------
The c# statements are (not working!!!) and the excel column header contains "Domain":

     OleDbCommand objCmdSelect = new OleDbCommand("SELECT DISTINCT * FROM [Sheet1$A1:F1228] WHERE Domain = 'Finance' AND Discipline = 'Taxation'", objConn);
     OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
     objAdapter1.SelectCommand = objCmdSelect;
     DataSet ds1 = new DataSet();
     objAdapter1.Fill(ds1);

------------------------------------------------------------------
The c# statements are (working!!!) and the excel column header now contains "Domaine" or anything else (anything other than Domain):

     OleDbCommand objCmdSelect = new OleDbCommand("SELECT DISTINCT * FROM [Sheet1$A1:F1228] WHERE Domaine = 'Finance' AND Discipline = 'Taxation'", objConn);
     OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
     objAdapter1.SelectCommand = objCmdSelect;
     DataSet ds1 = new DataSet();
     objAdapter1.Fill(ds1);

Any suggestion/idea why this header keyword not being accepted by the query?

Eser

[edit]code blocks added[/edit]
Posted 11-Nov-12 4:04am
Edited 11-Nov-12 4:36am
Nelek71.8K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,

I suspected that Domain is reserved word. Hence I searched on google a bit and found that it is indeed a reserved word.

http://support.microsoft.com/kb/321266[^]

So what you need to do is use square bracket around as shown below.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT DISTINCT * FROM [Sheet1$A1:F1228] WHERE [Domain] = 'Finance' AND Discipline = 'Taxation'", objConn);

Note the square bracket around word domain.

This should solve the problem.

Hope this helps, If it does, mark the answer and/or upvote.
Milind
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Thanks Milind. Your solution solved the issue.
  Permalink  
Comments
Milind Thakkar at 13-Nov-12 4:21am
   
Your welcome. Glad it helped. Please mark the solution as answer/upvote.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 8,736
1 Sergey Alexandrovich Kryukov 8,359
2 Mika Wendelius 6,845
3 F-ES Sitecore 2,354
4 Suvendu Shekhar Giri 2,205


Advertise | Privacy | Mobile
Web03 | 2.8.150728.1 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100