Search between two dates in my database

Question

Search between two dates in my database

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I wanted to know if you could help me solve a problem that I'm having trouble solving. The problem is this:

I have a database with a table of values that I keep:

date : dateTime
value : int
equipment : int (foreign key that comes from the 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 button to search for a gridview with all the values in that table but only those that are in the range of dates chosen by the user in the two textboxes. I'm 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) { 

            }
        }

mysql c# asp.net

asked by anonymous 19.11.2014 / 12:54

2 answers

Delete images sent by ckeditor Rendering WebForm in ASP.Net MVC

score 1 · Answer 1

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, DateTime runs from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TODO: MySQL Parameters

score 0 · Answer 2

Complementing Tony's answer, how your 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)
    {

    }
}