CODEWITHSUNDEEP
c#
user3221836
user3221836

Reputation: 43

Query for filtering database

DateTime fromDate = dateTimePicker1.Value, toDate = dateTimePicker2.Value;

string query2 = "select * from Sales where DatePurchased >= '" + fromDate.ToShortDateString() + "' AND DatePurchased <='" + toDate.ToShortDateString() + "'";

using (OleDbConnection conn2 = new OleDbConnection(connStr))
{
    using (OleDbCommand command = new OleDbCommand(query2, conn2))
    {
        command.Connection = conn2;
        conn2.Open();
        OleDbDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            graphics.DrawString(string.Format("{0:000000}", reader.GetInt32(0)), font, new SolidBrush(Color.Black), startX, startY + offset);
            graphics.DrawString(reader.GetString(1), font, new SolidBrush(Color.Black), startX+90, startY + offset);
            graphics.DrawString(reader.GetString(2), font, new SolidBrush(Color.Black), startX + 250, startY + offset);
            graphics.DrawString(Convert.ToString(reader.GetDouble(3)), font, new SolidBrush(Color.Black), startX + 500, startY + offset);

            startY += 35;
        }
    }
}

I am geting an error here:

OleDbDataReader reader = command.ExecuteReader();

error is

"OleDbException was unhandled : Data type mismatch in criteria expression. "

I dont know what to do, my data in the database is in Date/Time. Please Help

Upvotes: 1

Views: 82

Answers (3)

Muhammad Alaa
Muhammad Alaa

Reputation: 618

Try this :

string query2 = "select * from Sales where DatePurchased >= '" + fromDate.ToString("yyyy-MM-dd") + "' AND DatePurchased <='" + toDate.ToString("yyyy-MM-dd") + "'";

it worked for me before

Upvotes: 0

Damith
Damith

Reputation: 2082

If that fixed your error try changing your query as

string query2 = "select * from Sales where DatePurchased between " + fromDate.ToShortDateString() + " AND " + toDate.ToShortDateString() + " "

If it is not working please check whether you have data between these two date ranges.

Upvotes: 0

Peter
Peter

Reputation: 27944

Your ToShortDateString() is not a sql compatible string:

fromDate.ToString("yyyyMMdd")

will give you a sql compatible date string.

string query2 = "select * from Sales where DatePurchased >= '" + fromDate.ToString("yyyyMMdd")+ "' AND DatePurchased <='" + toDate.ToString("yyyyMMdd")+ "'";

However it would be better to use paramerized query.

Upvotes: 1

Related Questions