specified cast is not valid. linq

Question

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": //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),
                               tASF_VCPOL = myReader.GetString(1),
                               tASF_VCForwarder = myReader.GetString(2),
                               tASF_VCForwarderReference = myReader.GetString(3),
                               tASF_VCShippingLine = myReader.GetString(4),
                               tASF_VCContainerType = myReader.GetString(5),
                               tASF_VCContainerSize = myReader.GetString(6),
                               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),
                               tASF_VCNITDeposit = myReader.GetString(16),
                               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.

Check if your Convert methods are not handling null values correctly. — shree.pat18
It throws exception once it reaches context.TB_TransAgentSeaFreightRates.InsertOnSubmit(new TB_TransAgentSeaFreightRate() {................} — Appdev

tdbeckett tdbeckett · Accepted Answer · 2014-03-22T07:22:22

The error was caused by improper unboxing. (Ref: http://msdn.microsoft.com/en-us/library/b95fkada(v=vs.80).aspx)

Response Limitations: I don't know the property types for TB_TransAgentSeaFreightRate.

Assumption: The types of the data being assigned to the properties matches the types of
the properties.

There are 2 ways, that I see, of handling this from a debugging standpoint

Given the way your are doing this, you won't be able to see the exact failure. You could temporarily move the initialization of the members out of the new statement.
Alternatively, you could debug through the code repetitively each time commenting out 1 line from the assignments until you have found the offending line.

I am discounting Convert.ToDateTime as the source of the problem. It throws a FormatException for and invalid DateTime. Ref. http://msdn.microsoft.com/en-us/library/xhz1w05e(v=vs.110).aspx

My guess is that it is occurring on one of the mobjGenlib.ConvertLong. You're going to have to debug through the code to find the line in question. Also keep in mind that this error is happening because of the data. You will need to account for this type of error, and deal with it in the code.

specified cast is not valid. linq

1 Answers