Riddhi Valecha

Riddhi Valecha

  • 632
  • 3.2k
  • 333.1k

Mircosoft Database Engine Error - URGENT

Nov 21 2014 4:51 AM
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()".

Answers (3)