The connection for viewing your linked Microsoft Excel worksheet was lost

See more:

C#

MS-Excel

OleDb

Hello Guys,

I am getting an error The connection for viewing your linked Microsoft Excel worksheet was lost while fetching data from Excel file.

Workflow
I am having an Excel Sheet called New_All.xlsx which is always open and the values in that excel file are be updated after every 2 minutes. As in my below code, I import all the values in my system after every 2 min.

Code works fine once I ran it while testing for 2-5 min but If I put into live environment, I got the below error and it stopped working:

An exception of type 'System.Data.OldEb.OleDBException' occurred in System.Data.Dll but was not handled in User code.

Addition Information:The connection for viewing your linked Microsoft Excel worksheet was lost.

As I am stuck on this badly, any help will be appreciated.

Please find the below code of my page:

string excelfilepath = "E:\\MainExcel\\Personal\\New_All.xlsx";
      public static string path = @"E:\\MainExcel\\Personal\\New_All.xlsx";
      public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

      protected void Page_Load(object sender, EventArgs e)
      {
          if (!IsPostBack)
          {
              importdatafromexcel(excelfilepath);
          }
      }

      public void importdatafromexcel(string excelfilepath)
      {
          //Connection for SQL
          Connection con = new Connection();

          //create our connection strings
          string sexcelconnectionstring = connStr;
          OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
          oledbconn.Open();
          DataTable dtsheet = new DataTable();
          //All sheets' names in excel file
          dtsheet = oledbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          //declare variables - edit these based on your particular situation

          if (dtsheet == null)
          {
              return;
          }

          //Creat an array to store the name of Sheets.
          String[] excelSheets = new String[dtsheet.Rows.Count];
          int i = 0;

          // Add the sheet name to the string array.
          foreach (DataRow row in dtsheet.Rows)
          {
              excelSheets[i] = row["TABLE_NAME"].ToString().Trim(new Char[] { '\'' });
              i++;
          }

          for (int j = 0; j < excelSheets.Length; j++)
          {
              // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
              string myexceldataquery = "Select * from [" + excelSheets[j] + "]";

              OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
              DataTable dt = new DataTable();
              OleDbDataAdapter da = new OleDbDataAdapter(oledbcmd);
               da.Fill(dt);

              int ids = 0;
              string s_symbol = "", s_expiry_date = "", s_min_lot_qty = "", s_best_buy_qty = "", s_best_buy_rate = "", s_best_sell_rate = "", s_best_sell_qty = "", s_last_traded_price = "", s_last_trade_qty = "", s_percentage_change = "", s_ATP = "", s_low_price = "", s_high_price = "", s_open_price = "", s_time_date;
              DateTime timedate;
              timedate = DateTime.Now;
              int row;
              for (row = 0; row < dt.Rows.Count; row++)
              {

                  s_symbol = dt.Rows[row][0].ToString();
                  s_expiry_date = dt.Rows[row][1].ToString();
              }
        }
     }