I'm trying to insert data from excel file into a column of sql database.
protected void Button1_Click(object sender, EventArgs e)
{
using (OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["ExcelCon"].ConnectionString))
{
con.Open();
OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
OleDbDataReader dr = com.ExecuteReader();
using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString))
{
sqlcon.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.ColumnMappings.Add("ExcelColumnHeaderName", "SqlColumnName");
bulkCopy.DestinationTableName = "tableName";
bulkCopy.WriteToServer(dr);
}
}
dr.Close();
dr.Dispose();
}
Response.Write("Upload Successfull!");
}
web config :
<connectionStrings>
<add name="ExcelCon" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\John\\Desktop\\ExcelFile.xlsx;Extended Properties=Excel 12.0"/>
<add name="Sql" connectionString="Data Source=.\\SQLEXPRESS;AttachDbFilename='c:\\users\\John\\documents\\visual studio 2010\\Projects\\WebApplication3\\WebApplication3\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True;"/>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
The problem is I'm getting this error and I don't know why:
Exception Details: System.InvalidOperationException: Instance failure.
here:sqlcon.Open();
Remove the double slashes from the connection string.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With