“Data type mismatch in criteria expression” with an OleDbCommand in subquery

Question

I am getting a "Data type mismatch in criteria expression" with an OleDbCommand where I have a subquery. The actual database is Access although I am not sure it gets as far as presenting the query to the database.

DateTime EventDate;
string HT,AT;
int HS,AS;
OleDbCommand cmd

cmd.CommandText = @"INSERT INTO TheTable (EventDate,HT,HS,[AS],[AT]) 
    SELECT top 1 @EventDate,@HT,@HS,@AS,@AT FROM TheTable 
WHERE NOT EXISTS (SELECT 1 FROM TheTable WHERE HT=@HT2)";

cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@EventDate", EventDate);
cmd.Parameters.AddWithValue("@HT", HT);
cmd.Parameters.AddWithValue("@HS", HS);
cmd.Parameters.AddWithValue("@AS", AS);
cmd.Parameters.AddWithValue("@AT", AT);
cmd.Parameters.AddWithValue("@HT2", HT);

if I remove the subquery it works fine. I am sure all my variables are of the right type and are valid values. If I take away the subquery it works fine.

Is there something about subqueries that makes parameters not work with OleDbCommand?

UPDATE: tried the setting the date type and it made no difference

cmd.Parameters.Add("@EventDate", OleDbType.Date); 
cmd.Parameters["@EventDate"].Value = EventDate;

Use cmd.Parameters.Add(name, type).Value = value instead of AddWithValue. in particular when you try to pass a date to ms-access engine — Steve
Try omitting milliseconds part of EventDate using cmd.Parameters.AddWithValue("@EventDate", DateTime(EventDate.Year, EventDate.Month, EventDate.Day, EventDate.Hour, EventDate.Minute, EventDate.Second));. From my knowledge, your problem seems come from how OleDbType treats DateTime parameter. — Tetsuya Yamamoto

Rob Sedgwick Rob Sedgwick · Accepted Answer · 2017-01-23T22:26:13

The answer is that the subquery is evaluated first. Anyone who has used OleDbCommand soon finds out that the name of the parameters does not matter it's the order, which has to match the order of the parameters in the sql. However, in this case, I guess because the subquery is evaluated first then its parameters have to be supplied first even though they come last in the sql statement!

cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HT2", HT);
cmd.Parameters.AddWithValue("@EventDate", EventDate);
cmd.Parameters.AddWithValue("@HT", HT);
cmd.Parameters.AddWithValue("@HS", HS);
cmd.Parameters.AddWithValue("@AS", AS);
cmd.Parameters.AddWithValue("@AT", AT);

“Data type mismatch in criteria expression” with an OleDbCommand in subquery

1 Answers