C# ODBC connection to an excel spreadsheet
Posted on 2013-06-30
I am using an ODBC connection to link to an excel spreadsheet in C#. the Excel driver in version 12 (xls, xlsx, xlsm, xlsb). If I use an SQL statement such as:
"Select * from [transaction_master$] "
then I can successfully iterate through the spreadsheet rows. However when I try to use a statement such as:
Select * from [transaction_master$] WHERE T2_CompanyID = " + MyCompanyNo "
Where T2_CompanyID is a column heading on the spreadsheet, then I get the following error:
ERROR  [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.
The connection string is set to:
DSN=RulesdbExcel;Data Source=C:\\C# Folder\\RulesdbExcel.xls; HDR=yes; IMEX=1; Format=xls;
Any idea what is causing this and how I might get around it ?