I wanted to bulkcopy an excel file into an sql table. But it's giving me this error when I try to send to sql:
System.InvalidOperationException: 'The specified ColumnName' TransDate ' does not match any column of data source. '
The code I'm using to bulkcopy is as follows:
string Conecçã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 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
var sql = "SELECT * FROM [" + comboBox1.Text + "$]";
MessageBox.Show(sql);
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();
OleDbDataReader dr = oledbcmd.ExecuteReader();
using (SqlConnection con = new SqlConnection(ssqlconnectionstring))
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
bulkCopy.DestinationTableName = ssqltable;
bulkCopy.ColumnMappings.Add(comboBox2.Text, "TransDate");
con.Open();
bulkCopy.WriteToServer(dr);
con.Close();
try
{
bulkCopy.WriteToServer(dr);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
MessageBox.Show("");
}
}
}
oledbconn.Close();
button2.Enabled = true;
oledbconn.Close();
}
This is my database:
If you need anything else or code just ask. Thank you.