I'm running a 'LIKE' query against an Excel worksheet using ADO.NET and its proving to be case sensitive. I really want it to be case insensitive (which it should be according to all the info I can find) - does anyone have any ideas how I can achieve that?
My connect string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFile.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"
My query is: SELECT * FROM [Sheet1$] WHERE Chassis LIKE '%Foo%'
This does match Chassis entries of 'Foo', but not 'foo', 'FOO', etc.
Thanks for the link. The content seems to focus purely on SQL Server and my problem is with Excel. I did try adding a COLLATE Latin1_General_CI_AS clause to the WHERE condition but it fails with an unhelpful error (E_Failxxxx).
I'm not using Excel out of choice - its what the customer requires due to their operational setup and hosting situation. If I could move the data to a proper DB then I'd be fine, but I don't have that luxury in this case.
I didn't really want to do that as it will hit performance. I'm not sure how the Jet OLE DB driver deals with Excel files but I guess it doesn't really have that much opportunity to optimise operations like this.
If I can't find any other solution then I will fall back to this approach, but hoping that someone can come up with an alternative!
Perhaps there's an alternative method of doing this - how often will you read from the excel file? And how's the data used?
Because if the Excel file is manageable in size, there could perhaps be some merit in just pulling all the (potentially needed) data into your code layer and filter it there?
As your code layer will be better optimized at running such string manipulation/comparisons?
Update: This is wierd, but the problem disappeared when the application was deployed from a dev system onto a staging server. Haven't tried the live box yet, but it would seem that case sensitivity is determined by the server build rather than any application settings.
Thanks for the suggestions that were made, but in light of the discovery that its a system-specific issue I will consider this thread closed.
Hi - I'm not trying to be picky or ungracious because I really do value all the help I can get, but your link related to SQL Server 2000 configuration rather than the OS-level build issues which I now think are at the heart of my problems. I did try the 'collation' clauses suggested in the article but they gave syntax errors with the OLE DB driver.
The application I'm developing doesn't use SQL Server at all - in fact the live server won't even have it installed. It would be 10 times easier to code this whole piece if there was a decent DB to work with, but the customer has mandated that the data be held in Excel files on a server with no formal RDBMS installed.