c# Select sql database data using datetimepicker set with date range

Question

I am trying to make c# program, where I have to make a database report to be previewed at the datagridview. Data will be selected using the datetimepicker. I have written the code, it works but then if the date selected is of different months. No records appear

void FilterDBbtnClick(object sender, EventArgs e)
{
        MySqlConnection conn = new MySqlConnection();
    
        conn = new MySqlConnection(cs);
        
        string data = "SELECT `Date`, `Process`, `Actual`, `Target` FROM `database` WHERE `Date` BETWEEN '"+this.fromDatePicker.Value+"' AND  '"+this.toDatePicker.Value+"' order by `Date` desc";
        

        MySqlCommand cmd = new MySqlCommand(data, conn);
        
        
        cmd.Connection.Open();
            
        try
        {
            
          MySqlDataAdapter sda = new MySqlDataAdapter();
          sda.SelectCommand = cmd;          
          DataSet dt = new DataSet();
          sda.Fill(dt);
          BindingSource bsource = new BindingSource();
          bsource.DataSource = dt;
          mondeDataTable.DataSource = dt.Tables[0];
          sda.Update(dt);
        
        }
        
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        
        
        cmd.Connection.Close();
}

please help me check my code and tell me what might be wrong or missing.

Check what is sql injection, also use debug and see why nothing happens..You should dispose your MySqlDataAdapter too. — mybirthname, Oct 13 '16 at 07:12
what is your `Date` format and `fromDatePicker.Value` format. if they are different like **'2016-10-13 00:00:00.000'** and **'2013-10-13 09:50:00.000'**, query may return 0 rows. — Badiparmagi, Oct 13 '16 at 07:12
I did also try and same thing happens, no data appears whenever I write a different month. Is there any code for selecting different months? Is between doesn't work with it? — dann11, Oct 13 '16 at 07:25
the date format is really my problem.. How will I convert the sql date/time to system.time — dann11, Oct 13 '16 at 08:12

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

Use profiler to check the query that hits the DB. I suspect it's a date formatting issue.

Maybe this question can help you with logging the queries that hit the database:

edited May 23 '17 at 12:33

Community

1
1

answered Oct 13 '16 at 07:20

basarab

46
1
6

score 0 · Answer 2 · answered Oct 13 '16 at 07:57

Hope this helps:

Select * from [Table] where StartDate between '06/13/2016' and '10/13/2016'

The above query fetches records between months 06 and 10. Make sure that string in the data variable is in the above format. Also the column type in the database is date.

Check and remove special characters, if any.

Mark this as answer if you find this useful.

Mohd Ismail Siddiqui · Answer 3 · 2016-10-13T09:28:08.123

Try this for your select query. I have changed one of your select variable because it ambiguous for the Date datatype.

void FilterDBbtnClick(object sender, EventArgs e)
{
        MySqlConnection conn = new MySqlConnection();

        conn = new MySqlConnection(cs);


        //string data = "SELECT `Date`, `Process`, `Actual`, `Target` FROM `database` WHERE `Date` BETWEEN '"+this.fromDatePicker.Value+"' AND  '"+this.toDatePicker.Value+"' order by `Date` desc";

        //Changed query for getting data from DB according to the date 
        string data = "SELECT CreatedDate, Process, Actual, Target FROM database WHERE DATE_FORMAT(CreatedDate,'%Y-%m-%d') BETWEEN '"+this.fromDatePicker.Value.ToString("yyyy-MM-dd")+"' AND  '"+this.toDatePicker.Value.ToString("yyyy-MM-dd")+"' order by CreatedDate desc";


        MySqlCommand cmd = new MySqlCommand(data, conn);


        cmd.Connection.Open();

        try
        {

        MySqlDataAdapter sda = new MySqlDataAdapter();
        sda.SelectCommand = cmd;            
        DataSet dt = new DataSet();
        sda.Fill(dt);
        BindingSource bsource = new BindingSource();
        bsource.DataSource = dt;
        mondeDataTable.DataSource = dt.Tables[0];
        sda.Update(dt);

        }

        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }


        cmd.Connection.Close();
}

thanks for the code, i tried it but still i got error of "Unable to convert MySQL date/time value to System.DateTime". hope you can still help me with this. — dann11, Oct 13 '16 at 08:35
may be this could help you http://stackoverflow.com/questions/5754822/unable-to-convert-mysql-date-time-value-to-system-datetime — Visakh V A, Oct 13 '16 at 08:43
Use "DATE_FORMAT(CreatedDate,'%Y-%m-%d')" insetad of "Convert(varchar(10),CreatedDate,120)". I have given solution according to SQL server so I have changed the code. I hope it resolve your problem. — Mohd Ismail Siddiqui, Oct 13 '16 at 09:29
@dann11: Have you tried the solution again. I have changed the code accrding to MySql . — Mohd Ismail Siddiqui, Oct 17 '16 at 12:06

c# Select sql database data using datetimepicker set with date range

3 Answers3