Kbhxh lỗi data type mismatch in criteria expression năm 2024

Question

i am saving data from CSV file to access database , My CSV file data contains numeric fields with null values and text fields contains single quotes.

trying to insert the data i am getting error:

Data type mismatch in criteria expression.

in below code text fields ACCT,SHORT,DESC1 fields are having single quote data. numeric fields PRINC,[GROSS],[STAND],[NET],[NEWNET],[AECHG],[BONUS],[ORDER],[REALGR],[ANNUITYNET],[RECORDID] fields are having null values. How to handle with this.

My code is like below.

public DataTable InserttblDetailOPSData(string sInputPath, string sInFile)

    {  
        string query = string.Empty;  
        string header = "Yes";  
        DataTable dataTable = null;  
        string filePath = string.Empty;  
        string fileName = string.Empty;  
        sInputPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\";  
        sInFile = WebConfig.GetSetting("InFileName");
        try  
        {
            filePath = sInputPath;  
            fileName = WebConfig.GetSetting("InFileName");
            query = @"SELECT * FROM [" + fileName + "]";
            using (OleDbConnection connection = new OleDbConnection((@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Text;HDR=" + header + "\"")))  
            {  
                using (OleDbCommand command = new OleDbCommand(query, connection))  
                {  
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))  
                    {  
                        dataTable = new DataTable();  
                        dataTable.Locale = CultureInfo.CurrentCulture;  
                        adapter.Fill(dataTable);
                        try  
                        {  
                            OleDbConnection DBconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + WebConfig.GetSetting("AccessDBFilePath.devdesktop"));  
                            OleDbCommand cmd = new OleDbCommand();  
                            cmd.Connection = DBconn;  
                            cmd.CommandType = CommandType.Text;  
                            DBconn.Open();  
                            for (int i = 0; i < dataTable.Rows.Count; i++)  
                            {  
                                cmd.CommandText = "INSERT INTO tblDetail ([OFFICE],[AE],[SETDATE],[ACCT],[SHORT],[BS],[SHARES],[DESC],[PRICE],[PRINC],[GROSS],[STAND],[NET],[NEWNET],[AECHG],[BONUS],[BT],[CANCEL],[TRANSCODE],[ORDER],[REALGR],[ANNUITYNET],[RECORDID],[USERCODE1],[USERCODE7])" +  
                                                 " VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(6) + "','" + dataTable.Rows[i].ItemArray.GetValue(7) + "','" + dataTable.Rows[i].ItemArray.GetValue(8) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(9) + "','" + dataTable.Rows[i].ItemArray.GetValue(10) + "','" + dataTable.Rows[i].ItemArray.GetValue(11) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(12) + "','" + dataTable.Rows[i].ItemArray.GetValue(13) + "','" + dataTable.Rows[i].ItemArray.GetValue(14) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(15) + "','" + dataTable.Rows[i].ItemArray.GetValue(16) + "','" + dataTable.Rows[i].ItemArray.GetValue(17) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(18) + "','" + dataTable.Rows[i].ItemArray.GetValue(19) + "','" + dataTable.Rows[i].ItemArray.GetValue(20) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(21) + "','" + dataTable.Rows[i].ItemArray.GetValue(23) + "','" + dataTable.Rows[i].ItemArray.GetValue(24) +  
                                                 "','" + dataTable.Rows[i].ItemArray.GetValue(25) + "')";
                                cmd.ExecuteNonQuery();  
                            }  
                            DBconn.Close();  
                        }  
                        catch (Exception ex)  
                        {  
                        }  
                    }  
                }  
            }  
            if (Directory.Exists(sInputPath))  
            {  
                Directory.Delete(sInputPath, true);  
                Directory.CreateDirectory(sInputPath);  
            }  
        }  
        catch (Exception ex)  
        {  
            throw ex;  
        }
        return dataTable;

}

What I have tried:

in below code text fields ACCT,SHORT,DESC1 fields are having single quote data. numeric fields PRINC,[GROSS],[STAND],[NET],[NEWNET],[AECHG],[BONUS],[ORDER],[REALGR],[ANNUITYNET],[RECORDID] fields are having null values. How to handle with this.

Data type mismatch in criteria expression là gì?

Khi chúng ta làm công thức với hàm BS_SQL hay gặp lỗi "Data type mismatch" là do sai kiểu giá trị trong biểu thức tính toán hoặc điều kiện sau WHERE. Lỗi xảy ra phần lớn do dữ liệu nguồn bị sai kiểu, trong đó lỗi do làm công thức Excel trả về "" hay giá trị 0 là chính.

mẹo hay Type mismatch VBA