Access 2016, VS 2017, C #

Error System.Data.OleDb.OleDbException: "Data type mismatch in the selection condition expression." Swears on OleDbDataReader tarif = cmd.ExecuteReader ();

It is necessary to display the amount of revenue for a certain period by the request, on the form 2 datetimepicker, textbox and button Please help.

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace WindowsFormsApplication2 { public partial class Form13 : Form { public Form13() { InitializeComponent(); } private void Form13_FormClosed(object sender, FormClosedEventArgs e) { Form3 form3 = new Form3(); this.Visible = false; form3.ShowDialog(); this.Close(); } private void button1_Click(object sender, EventArgs e) { string str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Museum.mdb"; OleDbConnection cn = new OleDbConnection(str); OleDbCommand cmd = new OleDbCommand("Select sum(Сумма) as Итого from Экскурсии where [Дата] between '"+ DateTime.Parse(string.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value)) + "' and '"+ DateTime.Parse(string.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value)) + "'" , cn); cn.Open(); OleDbDataReader tarif = cmd.ExecuteReader(); while (tarif.Read()) { textBox1.Text = Convert.ToString(tarif["Итого"]); } textBox1.TabStop = false; } } }

    2 answers 2

    You do something weird dates when building a query. First, you string.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) them to the formatted string string.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) Ie, from the DateTime type, you get a string object. Next, you convert the string back to the DateTime DateTime.Parse(... type DateTime.Parse(... Ie, you make two conversions that do not change the output. Finally, the resulting DateTime you concatenate with the query strings. When concatenated, the DateTime is again converted into line, but not according to the format that you specified, but according to the one that is in the regional settings of the OS, or rather, in accordance with the so-called "current locale" .

    Moreover, when working with dates in the Access-dialect of SQL, the date literal is still not simple as yyyy-MM-dd

    But it’s better to abstract all of these subtleties from the design of a date into a string using parameters.

     ... var cn = new OleDbConnection(str); string cmdtext = "Select sum(Сумма) as Итого " + "from Экскурсии " + "where [Дата] between ? and ?"; var cmd = new OleDbCommand(cmdtext, cn); cmd.Parameters.Add(new OleDbParameter("@DT1", dateTimePicker1.Value)); cmd.Parameters.Add(new OleDbParameter("@DT2", dateTimePicker2.Value)); cn.Open(); OleDbDataReader tarif = cmd.ExecuteReader(); ...
    • one
      It works, thank you very much! - Nikita Peterson

    Obviously, the problem in the query that you bullet, namely in WHERE .

    I caught it in Access if in the compared fields could be NULL. Also, such an error can take off due to a valid type mismatch => all should be reduced to a uniform type.