Link to home
Start Free TrialLog in
Avatar of pclarke7
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
SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pclarke7
pclarke7

ASKER

Hi Guys,
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