am getting the errror of " object reference not set to an instance of an object" at this line of code:
SqlConnection MyCon = new SqlConnection(ConfigurationManager.ConnectionStrings[Excel107ConString].ConnectionString);
below is the whole code:
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.IO;
- using System.Data;
- using System.Data.OleDb;
- using System.Data.SqlClient;
- using System.Configuration;
- public partial class WebForm1 : System.Web.UI.Page
- {
- protected void Upload(object sender, EventArgs e)
- {
-
- 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":
- SqlConnection MyCon = new SqlConnection(ConfigurationManager.ConnectionStrings[Excel107ConString].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();
-
-
- dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("InvoiceNumber", typeof(string)),
- new DataColumn("AmountPaid", typeof(decimal)),
- new DataColumn("Remarks",typeof(string)) });
-
- using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
- {
- oda.Fill(dtExcelData);
- }
- excel_con.Close();
-
- string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
- using (SqlConnection con = new SqlConnection(consString))
- {
- using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
- {
-
- sqlBulkCopy.DestinationTableName = "smarttable";
-
- sqlBulkCopy.ColumnMappings.Add("InvoiceNumber", "InvoiceNumber");
- sqlBulkCopy.ColumnMappings.Add("AmountPaid", "AmountPaid");
- sqlBulkCopy.ColumnMappings.Add("Remarks", "Remarks");
- con.Open();
- sqlBulkCopy.WriteToServer(dtExcelData);
- con.Close();
- }
- }
- }
- }
-
- }