locked
How to compare date datatype in MS Access in C#

    Question

  • Am using Date in database . Tex box to get the date, In Ms access Short date is used.

    I need to get the date and compare the date in ms access with query the

                string selectSQL = "Select EntryNo,CName,Dateofpay,Mode,Chequeno,Amount From Entry where Dateofpay = #'" +textbox1.text+ "'# ";

    Its not working.

    When i display the database values in grid view it shows like 02/12/2011 12:00:00 even though i changed date format as short date . but in Ms access it shows like 02/12/2011


    Rajsekar

    Tuesday, June 05, 2012 6:23 PM

Answers

  • Two options:

    1. Quick but bad: Use DateTime.Parse and DateTime.ToString to format the date correctly:

          using (var con = new OleDbConnection(connectionString)) {
              string selectSQL = "Select EntryNo,CName,Dateofpay,Mode,Chequeno,Amount " +
                                  "From Entry where Dateofpay = #" + DateTime.Parse(textBox1.Text).ToString("MM/dd/yyyy") + "# ";
              DataTable dt = new DataTable();
              using (var adp = new OleDbDataAdapter(selectSQL, con)) { adp.Fill(dt); }
              dataGridView1.DataSource = dt;
          }
    2. Quick and good: Use an actual parameter! Note the question mark in the update SQL. Also, use a DateTimePicker so the input value is sure to be an actual date. Use the Date property of the DateTime class to get just the date part:

          using (var con = new OleDbConnection(connectionString)) {
              string selectSQL = "Select EntryNo, CName, Dateofpay, Mode, Chequeno, Amount " +
                                 "From Entry where Dateofpay = ?";
              DataTable dt = new DataTable();
              DateTime dateOfPay = dateTimePicker1.Value;
              using (var adp = new OleDbDataAdapter(selectSQL, con)) {
                  adp.SelectCommand.Parameters.Add(new OleDbParameter("@Dateofpay", dateOfPay.Date));
                  adp.Fill(dt);
              }
              dataGridView1.DataSource = dt;
          }

    jmh

    • Proposed as answer by Pantelis44999 Tuesday, June 05, 2012 9:53 PM
    • Marked as answer by Rajsekar Wednesday, June 06, 2012 8:52 AM
    Tuesday, June 05, 2012 6:49 PM

All replies

  • Two options:

    1. Quick but bad: Use DateTime.Parse and DateTime.ToString to format the date correctly:

          using (var con = new OleDbConnection(connectionString)) {
              string selectSQL = "Select EntryNo,CName,Dateofpay,Mode,Chequeno,Amount " +
                                  "From Entry where Dateofpay = #" + DateTime.Parse(textBox1.Text).ToString("MM/dd/yyyy") + "# ";
              DataTable dt = new DataTable();
              using (var adp = new OleDbDataAdapter(selectSQL, con)) { adp.Fill(dt); }
              dataGridView1.DataSource = dt;
          }
    2. Quick and good: Use an actual parameter! Note the question mark in the update SQL. Also, use a DateTimePicker so the input value is sure to be an actual date. Use the Date property of the DateTime class to get just the date part:

          using (var con = new OleDbConnection(connectionString)) {
              string selectSQL = "Select EntryNo, CName, Dateofpay, Mode, Chequeno, Amount " +
                                 "From Entry where Dateofpay = ?";
              DataTable dt = new DataTable();
              DateTime dateOfPay = dateTimePicker1.Value;
              using (var adp = new OleDbDataAdapter(selectSQL, con)) {
                  adp.SelectCommand.Parameters.Add(new OleDbParameter("@Dateofpay", dateOfPay.Date));
                  adp.Fill(dt);
              }
              dataGridView1.DataSource = dt;
          }

    jmh

    • Proposed as answer by Pantelis44999 Tuesday, June 05, 2012 9:53 PM
    • Marked as answer by Rajsekar Wednesday, June 06, 2012 8:52 AM
    Tuesday, June 05, 2012 6:49 PM
  • Two options:

    1. Quick but bad: Use DateTime.Parse and DateTime.ToString to format the date correctly:

          using (var con = new OleDbConnection(connectionString)) {
              string selectSQL = "Select EntryNo,CName,Dateofpay,Mode,Chequeno,Amount " +
                                  "From Entry where Dateofpay = #" + DateTime.Parse(textBox1.Text).ToString("MM/dd/yyyy") + "# ";
              DataTable dt = new DataTable();
              using (var adp = new OleDbDataAdapter(selectSQL, con)) { adp.Fill(dt); }
              dataGridView1.DataSource = dt;
          }
    2. Quick and good: Use an actual parameter! Note the question mark in the update SQL. Also, use a DateTimePicker so the input value is sure to be an actual date. Use the Date property of the DateTime class to get just the date part:

          using (var con = new OleDbConnection(connectionString)) {
              string selectSQL = "Select EntryNo, CName, Dateofpay, Mode, Chequeno, Amount " +
                                 "From Entry where Dateofpay = ?";
              DataTable dt = new DataTable();
              DateTime dateOfPay = dateTimePicker1.Value;
              using (var adp = new OleDbDataAdapter(selectSQL, con)) {
                  adp.SelectCommand.Parameters.Add(new OleDbParameter("@Dateofpay", dateOfPay.Date));
                  adp.Fill(dt);
              }
              dataGridView1.DataSource = dt;
          }

    jmh

    I proposed this as an answer, however Option 2 is the only way to go. DateTime should never be converted to string except for display purposes. Never for comparing or anything else... if it can be avoided.
    Tuesday, June 05, 2012 9:54 PM