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'.