How to do date search through dateTimePicker?

Asked

Viewed 1,013 times

2

How to search through a dateTimePicker (with Valuechanged event) dates entered in a Data Grid View?

When I press the year I want all the records for that year to appear, then press the month and all the records for that year in that month and then press the day to show all the records for that year, that month, that day.

I can’t seem to make that happen. 

    private void dateTimePicker_pesquisar_ValueChanged(object sender, EventArgs e)
    {
        int ano = dateTimePicker_pesquisar.Value.Year;
        int mes = dateTimePicker_pesquisar.Value.Month;
        int dia = dateTimePicker_pesquisar.Value.Day;

        sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();

        sqlCommand.CommandText = ("select * from Hora WHERE data like '" + ano + "%'");

        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        DataTable dataTable = new DataTable();
        sqlDataAdapter.Fill(dataTable);
        sqlCommand.ExecuteNonQuery();
        dataGridView_Principal.DataSource = dataTable;

        SomarTotalGrid();

        sqlConnection.Close();
    }
c# sql datepicker

  • Friend as I understand it, you want to carry the year first, then you would show everything of that year, then the month to show everything of that month and so to the right day? how are you setting the search parameters? on a single datatimepiker? think Voce will have to have one for year one for month and one for day.

  • Yes @Thomas Erich Pimentel is on a date time Piker... I wanted to do everything in one so I wouldn’t get too much.

  • then, until to extract the months or days of only one, however you would have to have 3 buttons, to fetch either by the year or by month or by day. pq if not as you would define it for the system when it is to search by day, or when it is to search by month for example.

  • @Thomas Erich Pimentel I think that if there was a way to know which button was clicked on the date time Piker could do... but finding this way is also not easy

  • it is perhaps possible, hoping that someone will answer there.

1 answer

1

If you use your component dateTimePicker_pesquisar to filter the data by month and day passing one by one separately you can do as the example below. 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
            sqlConnection.ConnectionString =
            "Data Source=DESK008;" +
            "Initial Catalog=stackoverflow;" +
            "User id=sa;" +
            "Password=123456;";
        }

        SqlConnection sqlConnection = new SqlConnection();

        private void button1_Click(object sender, EventArgs e)
        {
            int ano = dateTimePicker_pesquisar.Value.Year;
            int mes = dateTimePicker_pesquisar.Value.Month;
            int dia = dateTimePicker_pesquisar.Value.Day;

            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();

            sqlCommand.CommandText = ("select * from Hora WHERE  year(data) = @ano and month(data) = @mes and day(data) = @dia ");

            // Adicione o parâmetro de entrada e definar suas propriedades .
            SqlParameter parameterAno = new SqlParameter()
            {
                ParameterName = "@ano",
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input,
                Value = ano
            };

            SqlParameter parameterMes = new SqlParameter()
            {
                ParameterName = "@mes",
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input,
                Value = mes
            };

            SqlParameter parameterDia = new SqlParameter()
            {
                ParameterName = "@dia",
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input,
                Value = dia
            };

            // Adicione o parâmetro para a coleção Parameters. 
            sqlCommand.Parameters.Add(parameterAno);
            sqlCommand.Parameters.Add(parameterMes);
            sqlCommand.Parameters.Add(parameterDia);

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            sqlCommand.ExecuteNonQuery();


            dataGridView_Principal.DataSource = dataTable;

            //SomarTotalGrid();

            sqlConnection.Close();
        }
    }
}

Or you can summarize your query in a single parameter.

private void button1_Click(object sender, EventArgs e)
{
    var data = dateTimePicker_pesquisar.Value.Date;

    sqlConnection.Open();
    SqlCommand sqlCommand = sqlConnection.CreateCommand();

    sqlCommand.CommandText = ("select * from Hora WHERE  convert(date, data) = @data ");

    // Adicione o parâmetro de entrada e definar suas propriedades .
    SqlParameter parameterdata = new SqlParameter()
    {
        ParameterName = "@data",
        SqlDbType = SqlDbType.Int,
        Direction = ParameterDirection.Input,
        Value = data
    };

    // Adicione o parâmetro para a coleção Parameters. 
    sqlCommand.Parameters.Add(parameterdata);

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
    DataTable dataTable = new DataTable();
    sqlDataAdapter.Fill(dataTable);
    sqlCommand.ExecuteNonQuery();

    dataGridView_Principal.DataSource = dataTable;

    //SomarTotalGrid();

    sqlConnection.Close();
}

Or if the user enters the year month and day in fields separately you can use the first option only changes the type of input in the form and capture in the code Behind.

Or even implementing your event picker_ValueChanged. using the Eventhandler

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
            sqlConnection.ConnectionString =
            "Data Source=DESK008;" +
            "Initial Catalog=stackoverflow;" +
            "User id=sa;" +
            "Password=123456;";

            dateTimePicker_pesquisar.ValueChanged += new EventHandler(button1_Click);
        }

        SqlConnection sqlConnection = new SqlConnection();

        private void button1_Click(object sender, EventArgs e)
        {
            var data = dateTimePicker_pesquisar.Value.Date;

            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();

            sqlCommand.CommandText = ("select * from Hora WHERE  convert(date, data) = @data ");

            // Adicione o parâmetro de entrada e definar suas propriedades .
            SqlParameter parameterdata = new SqlParameter()
            {
                ParameterName = "@data",
                SqlDbType = SqlDbType.Date,
                Direction = ParameterDirection.Input,
                Value = data
            };

            // Adicione o parâmetro para a coleção Parameters. 
            sqlCommand.Parameters.Add(parameterdata);

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            sqlCommand.ExecuteNonQuery();

            dataGridView_Principal.DataSource = dataTable;

            //SomarTotalGrid();

            sqlConnection.Close();
        }
    }
}

  • I think the best option for me would be the first, but I can’t get you to search separately. For example today is day 07-09-2016 which means that the "int year = 2016, int month = 09 and int day = 07".

  • You do not need to spend the year month and day separately. Because your bank date is date type. And when you change the date on your component it passes the full date it would only need to buy as in the last example

  • I think the best option for me would be the first, but I can’t get you to search separately. For example today is day 07-09-2016 which means that the "int year = 2016, int month = 09 and int day = 07". For example, I have two dates '2015-09-07' and another '2015-05-04' and when I hit dateTimePiker and select 2015 I wanted it to appear all of 2015, but what happens is that only one record will appear '2015-09-07' because I have a ""select * from Time WHERE year(date) = @year-(2015) and Month(date) = @mes-(09) and day(date) = @dia-(07) "... @GOKU SSJ4

  • You won’t be able to do it that way, because there’s no way you know what was changed in your dateTimePicker control, unless you create a variable with the previous value when you trigger the event, after that you would have to separate your select into a string and go concatenating according to the variables you are changed.

  • The only time I got it was by keeping it in variables and doing checks, but it still gave some flaws! Thanks for your help @GOKU SSJ4, gave me a lot of useful information.

  • Always use Sqlparameter to pass parameters in your query

  • @Diogosousa, You would have to replace your control dateTimePicker in fields Year , month and day to be informed separately there would be possible you make the select this way.

Show 2 more comments

Browser other questions tagged c# sql datepicker

You are not signed in. Login or sign up in order to post.