CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    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?

    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 in advance

    Steve H

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Case sensitivity with ADO.NET and Excel

    Seems like something is wrong. Read this link
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jul 2009
    Posts
    5

    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.

    Any other ideas anyone?

    many thanks,

    Steve H

  4. #4
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    Re: Case sensitivity with ADO.NET and Excel

    I don't think you have other choice then to compare them case insensitive, meaning convert both to upper case when running your queries.

    Something like:
    SELECT * FROM [Sheet1$] WHERE UPPER(Chassis) LIKE '%FOO%'

    Note, I'm not sure if UPPER works in the SQL to Excel like that, but it would work in other flavors of SQL, but I'm sure a similar function would exists

  5. #5
    Join Date
    Jul 2009
    Posts
    5

    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!

    thanks,

    Steve H

  6. #6
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    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?

  7. #7
    Join Date
    Jul 2009
    Posts
    5

    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.

    Many thanks, Steve H

  8. #8
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Case sensitivity with ADO.NET and Excel

    That's what my link said.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  9. #9
    Join Date
    Jul 2009
    Posts
    5

    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.

    Thanks for all suggestions though!

    regards

    Steve H

  10. #10
    Join Date
    Jan 2017
    Posts
    2

    Re: Case sensitivity with ADO.NET and Excel

    Quote Originally Posted by SteveHarris View Post
    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:

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name & ";Extended Properties=Excel 12.0 Xml;"

    To this:
    cnn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    Many Thanks

  11. #11
    Join Date
    Jan 2017
    Posts
    2

    Re: Case sensitivity with ADO.NET and Excel

    Dear All,

    Sorry but, testing in another computer, with windows7, an the same problem reappear,

    The ok test in previous post was done in a laptop with XP.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured