Click here to Skip to main content
15,884,176 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am currently experiencing an error, whenever I try to upload xls datasheet into my database table. I am getting an exception error is:
Exception Details: System.InvalidCastException: Invalid cast from 'DateTime' to 'Single'.
The given value of type DateTime from the data source cannot be converted to type real of the specified target column.

This is my database table schema:
VB
[id]
    ,[tag]
    ,[decimalP]
    ,[type]
    ,[UploadDate]
    ,[name]
FROM [dummy].[dbo].[database_data]

I tried adding a 'for loop' for sqlBulkCopy property for convert datetime, but I could not get that to work either.

Here is my Upload method:
C#
protected void Upload(object sender, EventArgs e)
    {
        //Upload and save the file
        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);

        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }
        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

            string consString = ConfigurationManager.ConnectionStrings["dummyConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name

                    sqlBulkCopy.DestinationTableName = "dbo.comp2";

                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }
        }
    }


Any help would be very much appreciated.
Many thanks.
Posted
Comments
PIEBALDconsult 22-Aug-14 10:41am    
Maybe you should use SSIS.
miss786 22-Aug-14 10:44am    
thanks for your reply. I am slightly unfamiliar with the SSIS, is there a link or guide, i could use, which could give me some hints into how to approach this issue in c#, if possible.
PIEBALDconsult 22-Aug-14 10:50am    
http://msdn.microsoft.com/en-us/library/jj720568.aspx

1 solution

What database are you using? The error suggests that the UploadDate column is expecting a Single but getting a DateTime.

If you debug, what does the date field look like? You may find it is in the format 12345.98765, which is how Excel stores dates internally.
 
Share this answer
 

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



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