CODEWITHSUNDEEP
c#sqlms-access
Shahid Iqbal
Shahid Iqbal

Reputation: 2135

Data type mismatch in criteria expression ms-access c#

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?

Upvotes: 1

Views: 104

Answers (2)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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;

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

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.

Upvotes: 2

Related Questions