OleDb.OleDbException Data type mismatch in criteria expression

Question

I want to have a query like this but I get errors:

"Select * 
 From Products 
 Where (ProductName)  = '" + productName.Text + "' and  (Revise) = '" + a + "'";

where I have product name and revise at the same table as 2 different columns.

I have several rows with same product name but different revise dates. I am trying the pick the correct one.

con.Open();

OleDbCommand select = new OleDbCommand();
select.Connection = con;

Object revize = revizyonlar.SelectedItem;

if(revize != null)
{
    string a = revizyonlar.SelectedItem.ToString();
    System.DateTime b = Convert.ToDateTime(a);
    a = b.Date.ToShortDateString();
    a = "Select * From Products where (ProductName)  = '" + productName.Text + "' and  (Revise) = '" + a + "'";

    select.CommandText = a;
}
else
    select.CommandText = "Select * From Products Where (ProductName)  = '" + productName.Text + "'";

OleDbDataReader reader = select.ExecuteReader();

while (reader.Read())
{
}

Use SQL Parameters and this problem -- and many other more dire ones - will go away. That has been the wrong way to concoct NET SQL since its release almost 20 years ago — Ňɏssa Pøngjǣrdenlarp, Oct 02 '20 at 13:48

score 0 · Accepted Answer · answered Oct 02 '20 at 14:01

0

Date expressions are delimited by octothorpes in Access, so:

a = b.Date.ToString("yyyy'/'MM'/'dd");
a = "Select * From Products Where ProductName = '" + productName.Text + "' And Revise = #" + a + "#";

That said, do listen to Ňɏssa.

answered Oct 02 '20 at 14:01

Gustav

53,498
7
29
55

OleDb.OleDbException Data type mismatch in criteria expression

1 Answers1