Hi all,
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.