If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Case sensitivity with ADO.NET and Excel

Hi
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?

Re: Case sensitivity with ADO.NET and Excel

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.

Re: Case sensitivity with ADO.NET and Excel

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!

Re: Case sensitivity with ADO.NET and Excel

It will indeed be a performance hit, yes.

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?

Re: Case sensitivity with ADO.NET and Excel

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.

Re: Case sensitivity with ADO.NET and Excel

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.

Re: Case sensitivity with ADO.NET and Excel

Originally Posted by SteveHarris

Hi
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?

Dear all,
Sorry for posting in this 'classic' thread but i'm feeling the need to show my gratitude for the ideas commented above sharing this simple solution that have worked for me in a similar situation.
In my case i've found the issue solved just changing the connection string from this: