Click here to Skip to main content
15,171,576 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
While inserting the data from excel to database it throwing the above error

Here is my code:

C#
void insertDB()
       {
           string FileName = lblFileName.Text;
           string Extension = Path.GetExtension(FileName);
           string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
           string conStr = "";
           switch (Extension)
           {
               case ".xls": //Excel 97-03
                   conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                   break;
               case ".xlsx": //Excel 07
                   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.
Posted
Comments
   
On which line?
[no name] 24-Mar-14 8:56am
   
Getting error in the following lines but dont know in which field the error was

{
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)
});

tASF_NUIsActive = 1, may be this is a problem if tASF_NUIsActive is bool field try tASF_NUIsActive = true,
   
Comments
[no name] 24-Mar-14 8:11am
   
ASF_NUIsActive = 1 is not a bool field its an numeric field
Try to put the breakpoint to find out which line is causing the problem. Possible problem generator are Convert.ToDateTime and mobjGenlib.ConvertLong.
   
Comments
[no name] 24-Mar-14 8:12am
   
near date only getting error.but dont know how to resolve it :( please help me.
ArunRajendra 24-Mar-14 8:14am
   
You get the error if the date format is not proper. Try to convert to proper format.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900