OLE DB Excel query issue : Type mismatch in expression.

[crazyvonzipper]

Dear Daniweb members.

I am having quite a troublesome time here with OleDb.

What I am trying to achieve is to allow my users to connect to an uploaded Excel workbook, then to be able to select data from the sheets.

I have a query designer in the front end that allows them to generate the query based on what they are selectting.

The outcome query would look like this : (example) :

SELECT [A].[Date] AS 'Date' , [A].[Unique ID #] AS 'Unique ID #' , [A].[Case ID #] AS 'Case ID #' , [A].[ID number] AS 'ID number' , [A].[DC name] AS 'DC name' , [A].[Sys vendor ] AS 'System vendor' , [A].[Request type] AS 'Request type' , [A].[Fees already paid] AS 'Legal fees already paid' FROM [Credit Provid$A1:AD200] [A] , [Sheet 2$A1:E200] [B] WHERE [A].[Unique ID #] = [B].[Unique ID #]

And the way I am getting this data can be seen below:

public DataSet GetExcelDataSet(string Command)
        {
            string FilePath = GetDatabaseFilePath();
            string xlConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;MaxScanRows=0;FirstRowHasNames=1';";

            if (FilePath.EndsWith(".xlsx"))
            { xlConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;MaxScanRows=0;FirstRowHasNames=1';"; }

            DataSet ds = new DataSet();

            try
            {
                using (var connection = new OleDbConnection(xlConnection))
                {
                    using (var da = new OleDbDataAdapter(Command, connection))
                    {
                        if (connection.State != ConnectionState.Open)
                        { connection.Open(); }

                        da.Fill(ds);

                        if (connection.State != ConnectionState.Closed)
                        { connection.Close(); }
                    }
                }
            }

            // If an error occurs 
            catch (OleDbException ex)
            { Console.WriteLine("Excel: " + ex.Message); }

            return ds;
        }

When I select from two sheets OR ad a where clause to my query that basically filters on one sheet (or more if more sheets were selected), I get the error :

Type mismatch in expression.

I understand the fact that this might be caused by the fact that all values in the specified column might not be only one data type.

But doesnt IMEX=1 have an effect on this ?

Any help would be greatly apreciated.