pclarke7
asked on
C# ODBC connection to an excel spreadsheet
Hello,
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 [22018] [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 ?
regards
Pat
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 [22018] [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 ?
regards
Pat
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your input. I will definitely check out Excel DataReader.
However thanks to your comments I found the problem. The Spreadsheet had one of the rows used in the SQL as alpha when it should have been numeric. Once I changed this it worked.
regards
Pat