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()".
Loading
Wim SturkenboomPosted Nov 21, 2014, 7:11 AM
I find it difficult to read your code (not your fault) but the below probably causes the issue because I can't find a close/dispose at the end
oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savelocation + ";Extended Properties=Excel 8.0");
...
...
So you keep your file open and next try to do other things with it.
Change it to use a 'using' block
using (oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savelocation + ";Extended Properties=Excel 8.0"))
{
// do the processing here
}
Note that you might have to reboot your system to get rid of any 'in use' markings for files.
Riddhi ValechaPosted Nov 21, 2014, 5:41 AM
I had already referred the link before posting the issue.
The location is correct. The file is saved in the specified location.
This solution also gives the same error.
Sibeesh VenuPosted Nov 21, 2014, 5:15 AM
Can you please check the below link?
http://stackoverflow.com/questions/14985201/the-microsoft-jet-database-engine-cannot-open-the-file-it-is-already-opened-exc
http://forums.asp.net/post/5536574.aspx