Member Avatar for Tank50

HI

I wrote below coding part,but its generate the exception.Below I mention the code and error
Error
Data type mismatch in criteria expression.

DataSet Serach_sumbitdata=new DataSet();
            OleDbConnection conn7 = this.getconnection();
            conn7.Open();
            string str = "Select * from Customer where SubmitDate between '" + str1 + " 12:00:00 AM'" + " and '" + str2 + " 12:00:00 PM'";
            OleDbCommand command = new OleDbCommand(str, conn7);
            OleDbDataAdapter adapater = new OleDbDataAdapter(command);
            adapater.Fill(Serach_sumbitdata);

Please help me

Thanks
Tank50

  • 3 Contributors
  • 4 Replies
  • 211 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by Tank50

Dani AI

Generated

Good that ’s suggestion fixed the immediate error. The root cause is that the Access engine expects a Date/Time value, not a raw string; concatenating formatted text is fragile (locale/format differences) and can produce a “Data type mismatch.” Instead of building SQL with literals, validate the inputs as DateTime and use parameterized queries so the provider sends the correct type.

// validate/parse inputs first
DateTime startDate, endDate;
if (!DateTime.TryParse(str1, out startDate) || !DateTime.TryParse(str2, out endDate))
    throw new ArgumentException("Invalid date input");

// use positional parameters (OleDb uses ? placeholders)
string sql = "SELECT * FROM Customer WHERE SubmitDate >= ? AND SubmitDate < ?";

using (var conn = getconnection())
using (var cmd = new OleDbCommand(sql, conn))
{
    conn.Open();
    // Add parameters in the same order as the placeholders:
    cmd.Parameters.Add(new OleDbParameter { OleDbType = OleDbType.Date, Value = startDate.Date });
    // make end exclusive to include the whole end day
    cmd.Parameters.Add(new OleDbParameter { OleDbType = OleDbType.Date, Value = endDate.Date.AddDays(1) });

    var adapter = new OleDbDataAdapter(cmd);
    var ds = new DataSet();
    adapter.Fill(ds);
}

Quick troubleshooting notes:

  • If the column SubmitDate is actually Text in Access, change it to Date/Time or convert values; that also causes the mismatch.
  • OleDb parameters are positional — order matters. Name strings are ignored by the driver.
  • Prefer OleDbType.Date (avoid AddWithValue) so the provider doesn’t infer the wrong type.
  • For whole-day searches, using >= startDate.Date and < endDate.Date.AddDays(1) avoids issues with times and AM/PM adjustments.
  • Keep connections/commands in using blocks to ensure proper disposal.

Recommended Answers

All 4 Replies

Member Avatar for avirag

Well This error occurs when the server tries to process a page containing an Insert Record or Update Record server behavior, and the server behavior attempts to set the value of a Date/Time column in a Microsoft Access database to an empty string ("").

Member Avatar for Tank50

Hi

Thanks avirag for quick reply.But I didnt get u what u said above

Thanks
Tank50

Member Avatar for DdoubleD

Dates need to be enclosed with hash (#) symbol. Try:

string str = "Select * from Customer where SubmitDate between #" + str1 + " 12:00:00 AM#" + " and #" + str2 + " 12:00:00 PM#";
Member Avatar for Tank50

Hi

Thanks DdoubleD its works.

Thanks
Tank50

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.