excel columns selection query in C# winform

Question

I am trying to match a date from a column in excel sheet whose column type is DateTime. using following query

DateTime dtNew = Convert.ToDateTime("7/16/2010");
OleDbDataAdapter da = new OleDbDataAdapter("Select * FROM [" + SheetName + "$] where [Hand off date] = '" + dtNew + "'", conn);

I am getting

Datatype mismatch in criterion expression'.

please help on this.

Answer 1

Since you're using the JET db provider (I'm assuming here), which means your connection string looks something like:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\SOTest3.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

Your dates need to be surrounded with '#' signs (excluding single quotes). That should work.

Here's an example that works for me:

static void Main(string[] args)
{
    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\SOTest3.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
    DbProviderFactory factory =
      DbProviderFactories.GetFactory("System.Data.OleDb");

    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;
        using (DbCommand command = connection.CreateCommand())
        {
            connection.Open();  //open the connection 
            DateTime dtNew = Convert.ToDateTime("7/21/2010");
            DbDataAdapter da = factory.CreateDataAdapter();
            da.SelectCommand = command;
            da.SelectCommand.Connection = connection;
            da.SelectCommand.CommandText = "SELECT * FROM [Sheet1$] WHERE [Hand Off Date] = #" + dtNew.ToString("yyyy-MM-dd") + "#";
            DataTable dtDate = new DataTable();
            da.Fill(dtDate);
            Console.WriteLine(dtDate.Rows.Count);
            Console.ReadLine();                    
        }
    }
}

Hope this helps!!