0

This is a general question. I even understand the error. But nothing helps me so far. Why am I getting System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.' error.

private void DataWrite()
{
   connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + MyDataBase;
   connection = new OleDbConnection(connectionString);
   connection.Open();
   OleDbCommand cmd = new OleDbCommand();
   int testValue = 1;
   string values = "INSERT INTO DataTable (ID,Date,Measurement)\r\nVALUES ('" + testValue.ToString() + "','" + DateTime.Now + "','" + testValue.ToString() + "')";
   cmd = new OleDbCommand(values, connection);
   cmd.ExecuteNonQuery();
   connection.Close();
}

Access Data types are ID is Short Text and Date is Date/Time and Measurement is Short Text

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
master_yoda
  • 463
  • 3
  • 11

3 Answers3

1

Let's do this properly, with parameters in the sql:

private void DatenInDatenbankSchreibenKalibrierung()
{
   connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + MyDataBase;
   connection = new OleDbConnection(connectionString);
   connection.Open();
   OleDbCommand cmd = new OleDbCommand();
   int testValue = 1;
   string values = "INSERT INTO DataTable (ID,[Date],Measurement) VALUES (?,?,?)";
   cmd = new OleDbCommand(values, connection);
   cmd.Parameters.AddWithValue("p1", testValue);
   cmd.Parameters.AddWithValue("p2", DateTime.Now);
   cmd.Parameters.AddWithValue("p3", 1234);
   cmd.ExecuteNonQuery();
   connection.Close();
}

Why we always use parameters

Now, for side info, also take a read of Can we stop using AddWithValue already - it doesn't matter so much for an insert, and AddWithValue is convenient to demo parameterization in an SO post, but there are improvements for production system SELECTs (not using access would also be an improvement) because if AddWithValue guesses the type wrong it could have performance or data precision implications

Always use parameters when writing SQLs. If you ever think you can't, because maybe you have the values in an array and you have to concatenate the values in to the sql using a loop (for example); DON'T concat the values in. It's perfectly possibly to concat parameter placeholders in and add a parameter for every value in the array. Never concat values, always concat parameter placeholders

If you want to make your database life easier but still write SQL, take a look at Dapper

With dapper your code would look more like:

using(var x = new OleDbConnection(...)){
  x.Execute("INSERT INTO DataTable (ID,[Date],Measurement) VALUES (?id?,?dt?,?ms?)",
    new { id = 1, dt = DateTime.Now, ms = 1234 }
  );
}

Yup, that's it! Dapper will take care of all the connection opening, parameter adding, type guessing etc, all you need to do is execute the query on the connection and give it the sql and an anonymous object with properties whose names match the parameternames you put in the sql

Disclaimer: I've never used dapper to do this on Access/ole, I'm just parroting the words of Dapper's author

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Last time I posted an answer with addwithvalue on I got a lecture and a bunch of downvotes about how it was the work of the devil... – BugFinder Nov 20 '19 at 09:47
  • 1
    You're not the only one. If people stopped to think that an insert is hitting a single record and hence the impact of a single wrong type conversion is negligible/a far cry from a duff type guessing meaning an index on millions of records can't be used they might not have lectured. I've specifically raised the point in the question so as to give a holistic and balanced view, hopefully it will be read and considered! :) – Caius Jard Nov 20 '19 at 09:53
0

Even though parameters are preferred, your direct question can be answered:

Not all values are text.

Here, you seem to have numeric and date values only. So, use proper SQL syntax for the different data types, and Date is a reserved word:

string values = "INSERT INTO DataTable (ID, [Date], Measurement)\r\nVALUES (" + testNumericId.ToString() + ", #" + DateTime.Now.ToString("yyyy'/'MM'/'dd") + "#, " + testNumericValue.ToString(System.Globalization.CultureInfo.InvariantCulture) + ")";

Numeric decimal values must be converted to text using dot as the decimal separator.

Gustav
  • 53,498
  • 7
  • 29
  • 55
-4

I think that the problem came from date casting. Generally, I fix the format in my SQL statement like that:

string values = "INSERT INTO DataTable (ID,Date,Measurement)\r\nVALUES ('" + testValue.ToString() + "','" + DateTime.Now.ToString("yyyyMMddhhmmssfff") + "','" + testValue.ToString() + "')";

Or I prefer the string interpolation:

string values = $"INSERT INTO DataTable (ID,Date,Measurement)\r\nVALUES ('{testValue}','{DateTime.Now:yyyyMMddhhmmssfff}','{testValue}')";
Phozen
  • 1