importing csv file occur data type mismatch in criteria expression to access

Question

I am importing csv file from C# windows to access database. When I import CSV, then occur data type mismatch in criteria expression. Thanks for answering.

Here is my CSV file:

2,Each Product less 2 dollars,minus,0,2,2014/7/3,2014/7/31,0

Here is my table:

DiscountID(Int),Dis_Description(Text),Dis_Type(Text),Dis_Quantity(Int)
Dis_Value(Int),Start_Date(DateTime),EndTime(DateTime),Dis_Price(Int)

Here is Code:

cmd.CommandText = "UPDATE " + database + " SET  [Dis_Description] = '" + dtCSV.Rows[i].ItemArray.GetValue(1).ToString().Trim() + "'," + "[Dis_Type] = '" + dtCSV.Rows[i].ItemArray.GetValue(2).ToString().Trim() + "'," + "[Dis_Quantity] = '" + dtCSV.Rows[i].ItemArray.GetValue(3).ToString().Trim() + "'," + "[Dis_Value] = '" + dtCSV.Rows[i].ItemArray.GetValue(4).ToString().Trim() + "'," + "[Start_Date] = '" + dtCSV.Rows[i].ItemArray.GetValue(5).ToString().Trim() + "'," + "[Expiry_Date] = '" + dtCSV.Rows[i].ItemArray.GetValue(6).ToString().Trim() + "'," + "[Dis_Price] = '" + dtCSV.Rows[i].ItemArray.GetValue(7).ToString().Trim()+ "' WHERE DiscountID = " + dtCSV.Rows[i].ItemArray.GetValue(0).ToString().Trim() + "'";
cmd.ExecuteNonQuery();

UPDATE CODE: It also occur data type mismatch in criteria expression to access.

    OleDbCommand cmd = new OleDbCommand("UPDATE Discounts SET [Dis_Description]=?,[Dis_Type]=?,[Dis_Quantity]=?,[Dis_Value]=?,[Start_Date]=?,[Expiry_Date]=?,[Dis_Value]=? WHERE [DiscountID] = ?;
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(1).ToString().Trim());
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(2).ToString().Trim());
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(3).ToString().Trim());
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(4).ToString().Trim());
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(5).ToString().Trim());
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(6).ToString().Trim());
    cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(7).ToString().Trim());
cmd.Parameters.AddWithValue("?", dtCSV.Rows[i].ItemArray.GetValue(0).ToString().Trim());
    cmd.ExecuteNonQuery();

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders

Johnny Bones Johnny Bones · Accepted Answer · 2014-07-07T16:31:10

I think this is your problem:

WHERE DiscountID = " + dtCSV.Rows[i].ItemArray.GetValue(0).ToString().Trim() + "'";

It maybe should read:

WHERE DiscountID = '" + dtCSV.Rows[i].ItemArray.GetValue(0).ToString().Trim() + "'";

Looks like a single quote got left out.

Also, why all the joins between field names? i.e.

+ "'," + "[Dis_Quantity] = '" +

can be

+ "', [Dis_Quantity] = '" +

etc...

importing csv file occur data type mismatch in criteria expression to access

1 Answers