Click here to Skip to main content
15,891,431 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,
 
Share this answer
 
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.
 
Share this answer
 
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