While inserting the data from excel to database it throwing the above error
Here is my code:
void insertDB()
{
string FileName = lblFileName.Text;
string Extension = Path.GetExtension(FileName);
string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
string conStr = "";
switch (Extension)
{
case ".xls":
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
break;
case ".xlsx":
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
break;
}
try
{
using (var context = new LQTransAgentSeaFreightRateDataContext())
{
string sql = string.Format("Select * FROM [{0}]" , ddlSheets.SelectedValue);
using (var myConnection = new OleDbConnection(conStr))
using (var myCommand = new OleDbCommand(sql, myConnection))
{
myConnection.Open();
var myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
context.TB_TransAgentSeaFreightRates.InsertOnSubmit(new TB_TransAgentSeaFreightRate()
{
tASF_VCPOD = myReader.GetString(0).ToString(),
tASF_VCPOL = myReader.GetString(1).ToString(),
tASF_VCForwarder = myReader.GetString(2).ToString(),
tASF_VCForwarderReference = myReader.GetString(3).ToString(),
tASF_VCShippingLine = myReader.GetString(4).ToString(),
tASF_VCContainerType = myReader.GetString(5).ToString(),
tASF_VCContainerSize = myReader.GetString(6).ToString(),
tASF_DTEValidFrom = Convert.ToDateTime(myReader.GetString(7)),
tASF_DTEValidTo = Convert.ToDateTime(myReader.GetString(8)),
tASF_NUBasicRate = mobjGenlib.ConvertLong(myReader.GetString(9)),
tASF_NUPAF = mobjGenlib.ConvertLong(myReader.GetString(10)),
tASF_NUCAF = mobjGenlib.ConvertLong(myReader.GetString(11)),
tASF_NUPSS = mobjGenlib.ConvertLong(myReader.GetString(12)),
tASF_NUTotalAmount = mobjGenlib.ConvertLong(myReader.GetString(13)),
tASF_NUFreeDays = mobjGenlib.ConvertLong(myReader.GetString(14)),
tASF_VCCreditDays = myReader.GetString(15).ToString(),
tASF_VCNITDeposit = myReader.GetString(16).ToString(),
tASF_NUIsActive = 1,
tASF_mCMP_NUUniqueId = mobjGenlib.ConvertLong(TXTCompanyID.Text)
});
}
}
context.SubmitChanges();
}
}
catch (Exception ex)
{
lblMessage.ForeColor = System.Drawing.Color.White;
lblMessage.Text = ex.Message;
}
}
I don't have an idea to how to make it to work fine...if any one suggest me it would be very helpful.
Thanks in adavance.