Using C# to SELECT against Excel, how do I make it case-INsensitive?

This SELECT finds Kelly as expected:

select [First Name], [Last Name], Phone from [Data$] where [First Name] like "%Kelly%"

In the Excel spreadsheet, the first name is "Kelly" with a capital "K" -- and the SELECT specifies a capital "K" also.

However, if the K in > like "%Kelly%" < is LOWER-case -- like "%kelly%" -- then the record is NOT found. The SELECT is case-sensitive.

SQL Server does not appear to have a lower() or lcase() method that I can apply to the database column (???!!!). Is that actually true? Widespread advice on the net, to append "COLLATE SQL_Latin1_General_CP1_CI_AS" to the SQL statement, produces the error "IErrorInfo.GetDescription failed 0x80004005" when ExecuteReader() is executed.

Can someone please suggest a way to make my SQL SELECT against Excel case-INsensitive?

I've pasted the code below.

(The f.vs() method returns true when passed a Valid String, i.e. one for which IsEmptyOrNull() is false.)

TIA - Hoytster

        // The user may specify the first or last names, or category, or 
        // any combination, possibly all three.

        // Build the select; [Data$] is the name of the worksheet
        string select = "select [First Name], [Last Name], Phone from [Data$] where ";
        if (f.vs(firstName))
            select += "[First Name] like \"%" + firstName + "%\" and ";
        if (f.vs(lastName))
           开发者_JS百科 select += "[Last Name] like \"%" + lastName + "%\" and ";
        if (f.vs(category))
            select += "[Category] = \"" + category + "\" and ";
        select = select.Substring(0, select.Length - 4); // Lose the terminal "and "

        // This makes the SQL case-insensitive! BUT IT CAUSES ExecuteReader() FAIL
        // select += " [COLLATE SQL_Latin1_General_CP1_CI_AS]";

        // Build the connect string, using the specified Excel address file
        string connectionString =
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            @excelAddressFile +
            ";Extended Properties=Excel 8.0;";

        // Get a DB connection from an OLE DB provider factory
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        using (DbConnection connection = factory.CreateConnection())
        {

            // Assign the connection string
            connection.ConnectionString = connectionString;

            // Create the DB command
            using (DbCommand command = connection.CreateCommand())
            {
                // Apply the select
                command.CommandText = select;

                // Retrieve the data -- THIS ExecuteReader() IS WHERE IT FAILS
                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {

[COLLATE SQL_Latin1_General_CP1_CI_AS] only works in SQL Server. From what I can tell from your question, you're not using SQL Server; you're using an OLEDB data source to query an Excel file, in which case UCASE should work:

if (f.vs(firstName))
    select += "UCase([First Name]) like \"%" + firstName.ToUpper() + "%\" and ";
if (f.vs(lastName))
    select += "UCase([Last Name]) like \"%" + lastName.ToUpper() + "%\" and ";

SQL Server does have a function called LOWER that will convert a string of characters to all lowercase