Error inserting data from an excel file into sql

2

I have a problem when I insert data from the excel file into the database because in a field I am inserting it says that it is not of type time and does not let insert, but in the Excel file the text is written is only hours or is time type. Here's a print from the excel field I'm talking about:

Hereistheconfigurationoftheexceltable:

ThisisthecodeIusetowritethedatainthesqltable:

stringConecçãoDB=ConfigurationManager.ConnectionStrings["ConecçaoDB"].ConnectionString;
            string Table = ConfigurationManager.AppSettings["table"];

            string ssqltable = Table;

            string ssqlconnectionstring = ConecçãoDB;
            string sclearsql = "delete from " + ssqltable;


            var connectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filename+";Extended Properties='Excel 8.0;HDR=YES'";
            // var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;IMEX=0;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\"";
            var sql = "SELECT * FROM [" + comboBox1.Text + "$]";
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);

            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();
            MessageBox.Show(sql);
            OleDbConnection oledbconn = new OleDbConnection(connectionString);
            OleDbCommand oledbcmd = new OleDbCommand(sql, oledbconn);
            oledbconn.Open();
            using (OleDbDataReader dr = oledbcmd.ExecuteReader())
            {
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring))
                {
                    bulkcopy.DestinationTableName = ssqltable;
                    bulkcopy.ColumnMappings.Add(comboBox2.Text, "TransDate");
                    bulkcopy.ColumnMappings.Add(comboBox3.Text, "TransTime");
                    bulkcopy.ColumnMappings.Add(comboBox4.Text, "CardNo");
                    bulkcopy.ColumnMappings.Add(comboBox5.Text, "VoucherNo");
                    bulkcopy.ColumnMappings.Add(comboBox6.Text, "Quantity");
                    bulkcopy.ColumnMappings.Add(comboBox7.Text, "TransactionValue");

                    bulkcopy.WriteToServer(dr);
                }
            }
            oledbconn.Close();
            button2.Enabled = true;
            oledbconn.Close();

This is the error it gives me:

  

InvalidCastException: Invalid cast of 'System.DateTime' in   'System.TimeSpan'.

    
asked by anonymous 02.11.2017 / 17:35

1 answer

0

If the Excel column contains Date , or Time , or DateTime , regardless of the format, ACE.OLEDB will always map it to a DateTime type.

Then the conversion error is occurring because the TransTime column in SQL Server is Time and Excel DateTime .

To fix the problem, you can try one of these alternatives:

Option 1 : Change the column TransTime of SQL Server to DateTime

Option 2 : try changing the query to:

        SELECT cstr(TransDate) as TransDate, TransTime, ...  FROM [Plan1$]

Obs : I did not have to test this second option because I do not have SQL Server installed on my machine but I believe it will work. The cstr(TransDate) function will Cast time to string and SQL Server will automatically cast string to time .

    
07.11.2017 / 03:05