Mircosoft Database Engine Error - URGENT
Hi...
I am getting an error saying -
The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
My requirement -
Upload an excel sheet using file upload control and update records in the sql server from asp.net C# (Front - end)
I am pasting my code-
Button_Click event
{
if (fu_UploadFile.HasFile == true)
{
dbf = new DatabaseFunctions();
string Extension = Path.GetExtension(fu_UploadFile.PostedFile.FileName);
if (Extension == ".xls" || Extension == ".xlsx")
{
lbl_errmsg.Text = "";
System.Threading.Thread.Sleep(5000);
Get_Sheets();
//CompareRows(dbf.GetSerialNumbers(), Import_To_Grid(Session["FilePath"].ToString(), Session["FileExtension"].ToString(), "YES"));
UpdateInsertTable(Import_To_Grid(Session["FilePath"].ToString(), Session["FileExtension"].ToString(), "YES"));
}
}
------------
#region Get_Sheets
private void Get_Sheets()
{
OleDbConnection oconn = null;
System.Data.DataTable dt = null;
try
{
string FilePath = string.Empty;
string FileName = string.Empty;
string savelocation = string.Empty;
if (fu_UploadFile.HasFile)
{
FileName = Path.GetFileName(fu_UploadFile.PostedFile.FileName);
// Get File extension
string Extension = Path.GetExtension(fu_UploadFile.PostedFile.FileName);
//string FolderPath = "LotEntryMaster/Files";
//AssetCodeFiles
string strFilename = fu_UploadFile.FileName;
string strDate = DateTime.Now.ToString("ddMMMyyyyhhmmsstt");
// savelocation = CWF.GetConfigSetting("UploadFile") + "\\AssetCodeFiles\\" + strDate + "_" + strFilename;
savelocation = CWF.GetConfigSetting("UploadFile") + "\\AssetCodeFiles\\" + strDate + "_" + strFilename;
//string ss = Request.PhysicalApplicationPath + FolderPath;
//FilePath = Server.MapPath(FolderPath + FileName);
//FilePath = ss;
ViewState["FilePath"] = Session["FilePath"] = savelocation;
ViewState["FileName"] = Session["FileName"] = FileName;
ViewState["FileExtension"] = Session["FileExtension"] = Extension;
//File save
fu_UploadFile.SaveAs(savelocation);
}
oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savelocation + ";Extended Properties=Excel 8.0");
oconn.Open();
dt = null;
// Get all tables include in that work sheet
dt = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
}
String[] sheet = new String[dt.Rows.Count];
int i = 0;
//Read All sheet names and store in one string Builder
foreach (DataRow dr in dt.Rows)
{
sheet[i] = dr["TABLE_NAME"].ToString();
i++;
}
string[] a = sheet;
int j = 0;
// Assign all sheet names to DropDownList
if (a != null && a.Length > 0)
{
// ddl_excelsheets.Visible = true;
//lblsheet.Visible = true;
for (j = 0; j < a.Length; j++)
{
ddl_excelsheets.Items.Add(a[j]);
}
//Default selected value for DropDown
ddl_excelsheets.Items.Insert(0, "Select Excel Sheet");
//ddl_excelsheets.DataBind();
}
}
catch (Exception err) { lbl_errmsg.Text = err.Message.ToString(); }
}
#endregion
------------------
#region UpdateOrInsertTable
private void UpdateInsertTable(System.Data.DataTable table)
{
try
{
Int32 tab1 = 0;
dbf = new DatabaseFunctions();
tab1 = table.Rows.Count;
if (tab1 > 0)
{
Controls_En(false);
// lbl_errmsg.Text = "Please wait....";
//Import_To_Grid(Session["FilePath"].ToString(), Session["FileExtension"].ToString(), "YES").Rows
foreach (DataRow dr in table.Rows)
{
if (dr[0].ToString().Length > 0)
{
if (dbf.IsDataUpdated_tbl_AssetDetails(dr[0].ToString(), dr[1].ToString(), "N") == false)
{
totalupdate++;
}
//else { lbl_errmsg.Text = "Error in updation."; }
if (dbf.IsDataUpdated_tbl_AssetDetails(dr[0].ToString(), dr[1].ToString(), "N") == true)
{
if (dbf.IsDataInserted_AssetDetails_Temp(dr[0].ToString(), dr[1].ToString(), "N") == false)
{
totalinsert++;
}
else
{ }
}
}
}
lbl_totalUpdate.Text = "Records Updated.";
}
else { lbl_errmsg.Text = "No records in the excel sheet."; }
}
catch (Exception err) { err.Message.ToString(); }
}
#endregion
-----------
#region GetDataInDataTable
private System.Data.DataTable Import_To_Grid(string FilePath, string Extension, string isHDR)
{
try
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
case ".csv":
conStr = ConfigurationManager.ConnectionStrings["dbAssetMgtConnectionString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
cmdExcel.Connection = connExcel;
// connExcel.Open();
if (connExcel.State != ConnectionState.Open)
{
connExcel.Open();
}
//Get the name of First Sheet
System.Data.DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
cmdExcel.CommandText = "SELECT * From [Sheet1$]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
if (connExcel.State == ConnectionState.Open)
{
connExcel.Close();
}
return dt;
}
catch (Exception err) { err.Message.ToString(); return null; }
}
#endregion
--------------
Error occours here -
In Import_To_Grid() method at line ' "connExcel.Open()".