Search between two dates in my database

Question

I was wondering if you could help me solve a problem that I’m having some difficulty solving. The problem is this:

I have a database with a table of values in which I store:

• date: datetime
• value: int
• equipment: int (Foreign key coming from equipment table)

What I want to do is a search in which I want the user to fill in two textboxes that receive a date in each of them and then I want to click a search button that appears a gridview with all the values of this table but only those that are in the range of dates chosen by the user in the two textboxes. I am working with ASP.NET with C# and my database is Mysql

protected void botaoPequisar_Click(object sender, EventArgs e)
        {
            try
            { 	var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["basedados"].ConnectionString);
			
                DateTime data1 = DateTime.ParseExact(txtDataInicio.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);
                DateTime data2 = DateTime.ParseExact(txtDataFim.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);
				
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT idConduta,valor_Lido,data_Leitura FROM valores_conduta WHERE data_Leitura BETWEEN " + data1.ToString("yyyy-MM-dd") + " AND " + data2.ToString("yyyy-MM-dd") + " AND idConduta=" + ddlHistorico.SelectedValue + "", conn);
                da.SelectCommand.CommandType = CommandType.Text;
                DataSet ds = new DataSet();//definir o objecto dadaset (ds)
                //preencher os dados
                da.Fill(ds);
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
            catch(Exception ex) { 

            }
        }

Answer 1

Missing quotes on date values, open + close connection, in addition, I put an auxiliary function that I did, to be cleaner the code.

protected void botaoPequisar_Click(object sender, EventArgs e)
    {
        try
        {
            DateTime data1 = DateTime.ParseExact(txtDataInicio.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);
            DateTime data2 = DateTime.ParseExact(txtDataFim.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);

            var SQL = "SELECT idConduta,valor_Lido,data_Leitura FROM valores_conduta WHERE data_Leitura BETWEEN '" + data1.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + data2.ToString("yyyy-MM-dd") + " 23:59:59' AND idConduta=" + ddlHistorico.SelectedValue + "";

            //preencher os dados
            DataTable resultado;
            using(var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["basedados"].ConnectionString))
            {
                conn.Open();
                resultado = RunSQL(SQL, conn);
                conn.Close();
            }
            GridView1.DataSource = resultado;
            GridView1.DataBind();
        }
        catch(Exception ex) { }
    }

    static public DataTable RunSQL(string sSQL, MySqlConnection MyConnection)
    {
        DataTable DT = new DataTable();
        using (MySqlDataAdapter MyDataAdapter = new MySqlDataAdapter(sSQL, MyConnection))
        {
            try
            {
                #region Executa / Preenche o DT
                //MyDataAdapter = new MySqlDataAdapter(sSQL, MyConn);
                DT.TableName = "TABELA";
                MyDataAdapter.Fill(DT);
                //'MyDataAdapter.Fill(DS)
                #endregion
            }
            catch (Exception ex)
            {
                // by Tony - 26-set-2006 
                // Retorna o SQL e o erro, para facilitar o debug do sistema.
                var newexeption = new Exception(sSQL + " " + ex.Message);
                throw newexeption;
            }
        }

        return DT;
    }

• Attention, the Datetime goes from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
  
• ALL OVER: Mysql Parameters
  

Answer 2

Complementing Tony’s response, the way his code is implemented is not good practice, but I believe it’s some kind of study or a POC !

With this, you can send the filters via Parameter !

protected void botaoPequisar_Click(object sender, EventArgs e)
{
    var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["basedados"].ConnectionString);

    try
    {
        var data1 = DateTime.ParseExact(txtDataInicio.Text + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
        var data2 = DateTime.ParseExact(txtDataFim.Text + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
        var id = int.Parse(ddlHistorico.SelectedValue);

        var query = "SELECT idConduta,valor_Lido,data_Leitura FROM valores_conduta WHERE data_Leitura BETWEEN @DataInicio AND @DataFim AND idConduta = @idConduta";

        using (var command = new MySqlCommand(query, conn))
        {
            command.Parameters.Add(new MySqlParameter("@DataInicio", data1));
            command.Parameters.Add(new MySqlParameter("@DataFim", data2));
            command.Parameters.Add(new MySqlParameter("@idConduta", id));

            conn.Open();

            using (var adapter = new MySqlDataAdapter(command))
            {
                var ds = new DataSet();

                adapter.Fill(ds);

                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
    catch (Exception ex)
    {

    }
}