CODEWITHSUNDEEP
c#ms-access
UTSHO
UTSHO

Reputation: 113

Searching between two datetimepicker in access database

Code:

private void button2_Click(object sender, System.EventArgs e)
        {

           OleDbDataAdapter dbc = new OleDbDataAdapter("SELECT ReceiptID,ID,Name,Paid,Due FROM lastpays where [Dateofpayment] >= "+ dateTimePicker1.Value.Date + " AND [Dateofpayment] < " + dateTimePicker2.Value.Date + "", con);

            DataTable data = new DataTable();
            dbc.Fill(data);
            dataGridView1.DataSource = data;
        }

Error:

System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression '[Dateofpayment] >= 11-01-2020 12:00:00 AM AND [Dateofpayment] < 11-01-2020 12:00:00 AM'.'

Upvotes: 0

Views: 64

Answers (2)

Gustav
Gustav

Reputation: 55841

Even though parameters are preferred, the questioneer deserves an answer why the code is failing.

The date values must be converted to valid string expressions:

OleDbDataAdapter dbc = new OleDbDataAdapter("SELECT ReceiptID,ID,Name,Paid,Due FROM lastpays where [Dateofpayment] >= #"+ dateTimePicker1.Value.Date.ToString("yyyy'/'MM'/'dd") + "# AND [Dateofpayment] < #" + dateTimePicker2.Value.Date.ToString("yyyy'/'MM'/'dd") + "#", con);

Upvotes: 0

Dai
Dai

Reputation: 155250

  • MS Access (JET Red) requires date literals in SQL to be in the form #MM/dd/yyyy#.
  • Your code however inserts the default string representation of a DateTime value using CurrentCulture, without using any delimiters.
    • Because you're directly concatenating String values with DateTime values, which invokes DateTime.ToString() which is CurrentCulture-sensitive.
  • Use parameters to avoid this issue entirely, and to prevent SQL injection.
  • Also, you're using the same dateTimePicker instance for both values - I think you mean to use dateTimePicker1 and dateTimePicker2 - though you should rename them to minDatePicker and maxDatePicker to be clear what their purpose is.
const String sql = @"SELECT ReceiptID,ID,Name,Paid,Due FROM lastpays where [Dateofpayment] >= ? AND [Dateofpayment] < ?";

OleDbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;

OleDbParameter pFrom = cmd.CreateParameter();
pFrom.OleDbType = OleDbType.Date;
pFrom.Value     = dateTimePicker1.Value.Date;
cmd.Parameters.Add( pFrom );

OleDbParameter pTo = cmd.CreateParameter();
pTo.OleDbType = OleDbType.Date;
pTo.Value     = dateTimePicker2.Value.Date;
cmd.Parameters.Add( pTo );

OleDbDataAdapter da = new OleDbDataAdapter( selectCommand: cmd );

DataTable data = new DataTable();
da.Fill( data );
dataGridView1.DataSource = data;

Upvotes: 2

Related Questions