Data type mismatch in criteria expression ms-access c#

Question

I have MS Access database have table which contains below data.

    TradeDate   TradeType   TicketNo    PassengerName   
    11-Feb-19   SALE         1234       ABC
    12-Feb-19   PURCHASE     0345       XYZ

I want to get data between two dates with TradeType, below is my code from C#

public static DataTable GetSale(DateTime FromDate, DateTime ToDate, string TradeType)
{    
    DataTable dt = new DataTable();    
    OleDbConnection con = new OleDbConnection(Utility.GetConnectionString());
    OleDbCommand cmd = new OleDbCommand("SELECT A.AgencyName, T.TradeDate, T.InvoiceNo, T.TicketNo, T.PassengerName, T.FatherName, T.TicketAmount, T.RefundAmount FROM Agencies AS A, Trade AS T WHERE T.Account_ID=A.Account_ID and T.TradeType=@TradeType And T.TradeDate>=@FromDate And T.TradeDate<@ToDate", con);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@FromDate", FromDate);
    cmd.Parameters.AddWithValue("@ToDate", ToDate);
    cmd.Parameters.AddWithValue("@TradeType", TradeType);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    con.Open();
    da.Fill(dt);
    con.Close();
}

while executing my code

Data type mismatch in criteria expression.

Exception is thrown. What am I doing wrong?

What are `FromDate`, `ToDate` and `TradeType`? Did you pass a string instead of a DateTime value perhaps? Finally, please don't use two-digit years. It's the most *in*famous bug in history. — Panagiotis Kanavos, Feb 13 '19 at 08:17

score 2 · Answer 1 · answered Feb 13 '19 at 08:06

2

please use ...Parameters.Add(...) instead of AddWithValue.

Without explicitly providing the type as in command.Parameters.Add("@param", SqlDbType.Int);, it will try to implicitly convert the input to what it is expecting and it fails.

answered Feb 13 '19 at 08:06

Derviş Kayımbaşıoğlu

28,492
4
50
72

Tetsuya Yamamoto · Accepted Answer · 2019-02-13T08:17:28.450

First thing you should know is parameters in OleDbCommand processed from order of their definition, you need to define parameters in same order as they're appeared in query (OLE DB doesn't support named parameters).

Second, use Add() instead of AddWithValue() by specifying OleDbType for each parameter, you can see the list of types here.

Here is an example of correct setup:

OleDbConnection con = new OleDbConnection(Utility.GetConnectionString());
OleDbCommand cmd = new OleDbCommand(@"SELECT A.AgencyName, T.TradeDate, T.InvoiceNo, T.TicketNo, T.PassengerName, T.FatherName, T.TicketAmount, T.RefundAmount 
                                    FROM Agencies AS A, Trade AS T 
                                    WHERE T.Account_ID=A.Account_ID 
                                    and T.TradeType = @TradeType And T.TradeDate >= @FromDate And T.TradeDate < @ToDate", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@TradeType", OleDbType.VarWChar).Value = TradeType;
cmd.Parameters.Add("@FromDate", OleDbType.Date).Value = FromDate;
cmd.Parameters.Add("@ToDate", OleDbType.Date).Value = ToDate;

The parameters have to be in the same order as the table in the Access Database. — jdweng, Feb 13 '19 at 08:14

Data type mismatch in criteria expression ms-access c#

2 Answers2