How to query Data values on a C# connection to excel using Oledb?

Question

Follow below the line of code

private void button7_Click(object sender, EventArgs e)
        {
            string conex = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES;\";";
            OleDbConnection conn = new OleDbConnection(conex);

            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM  [" + textBox2.Text + "$] where DATA ="+ dateTimePicker1.Value , conn);
            DataTable dt = new DataTable();

            myDataAdapter.Fill(dt);
            dataGridView1.DataSource = dt;
}

I try to return a value by searching for Data, but only find errors. No single quotes(as in the code), returns error =

System.Data.OleDb.OleDbException: 'Erro de sintaxe (operador faltando)
na expressão de consulta 'DATA =18/12/2020 08:20:04'.'

When I put single quotes in the query around the error dateTimePicker =

System.Data.OleDb.OleDbException: 'Tipo de dados imcompatível na
expressão de critério.'

Answer 1

Your database may not understand the date format. Usually it works with the format YYYY-MM-DD.

Include the method ToString in his dateTimePicker1.Value explaining in which format the date should be written in the query.

OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM  [" + textBox2.Text + "$] where DATA ="+ dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss") , conn);