I am uploading a excel file when the uploaded excel file is not proper format ist giveing error but after that also when i am inserting a proper excel file its given same error also
when uploading 1st time proper excel format working fine then wrong excel format error againg going for proper format also its giving error
try
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/ExcelUpload/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
SqlConnection con = new SqlConnection(conn);
string query = "Insert into ALQuestion.QuestionMaster(Question,Option1,Option2,Option3,Option4,Answer,LevelID,SkillID,CreatedOn,Status) Values('" +
ds.Tables[0].Rows[i][00].ToString() + "','" + ds.Tables[0].Rows[i][01].ToString() +
"','" + ds.Tables[0].Rows[i][02].ToString() + "','" + ds.Tables[0].Rows[i][03].ToString() + "','" + ds.Tables[0].Rows[i][04].ToString() + "','" + ds.Tables[0].Rows[i][05].ToString() + "','" + LevelId.ToString() + "','" + skillid.ToString() + "','" + now.ToString(("yyyy/MM/dd")) + "','" + "1" + "')";
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
ViewBag.UploadExcelStatus = "Excel Upload Successfully";
return Json("Excel Upload Successfully");
}
catch
{
ViewBag.UploadExcelStatus = "Please Upload a Proper Excel File";
return Json(Please Upload a Proper Excel File");
}
its coming directly to catch