0

I have an issue with app. developed several years ago. It is an add-in for MS Excel 2010 that performs call to the SQL server database and forms a table in the Worksheet.I had to amend it to make it work with Azure database (connection strings), but it turned out the problem is a bit deeper than that. When I try to run an app, it gives HRESULT: 0x800A03EC exception, and I can't figure why. MS Excel 2010 is installed. The code that gives an error:

public void insertData(string startDate, string endDate, string startTime, string endTime, string tickerSymbol,
        string RegId, string events, string userId, string PO, bool includePO, string markets)
    {

        try
        {

            //
            if (Globals.ExclAddIn.recordCount < 1000000)
            {
                string szSql = string.Format(@"EXEC sp_Get_Excel_TOQ_Report_Data_Mkt {0}, {1}, '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', {10}",
                startDate, endDate, startTime, endTime, tickerSymbol, RegId, events, userId, PO, markets, includeJPO);

                Workbook w = this.Application.ActiveWorkbook;
                Worksheet ws;
                if (w != null)
                {
                        ws = (Worksheet)w.Sheets.Add(missing,missing,missing,missing);
                        ws.Name = DateTime.Now.ToString("(RPT) yyyyMMdd HHmmss");
                    //}
                }
                else
                {
                    w = this.Application.Workbooks.Add(missing);
                    ws = (Worksheet) w.Worksheets.get_Item(1);
                }


                Range unfreezeRow = (Range)ws.Rows[10];
                unfreezeRow.Application.ActiveWindow.FreezePanes = false;

                ws.Rows.Delete();
                object misValue = System.Reflection.Missing.Value;
                Range range = ws.get_Range("A1:A9", misValue);
                MessageBox.Show(range.EntireRow.Address);

                //Check if the issue is in connection
                SqlConnection conn = new SqlConnection(selectedConnection);
                conn.Open();
                MessageBox.Show("Connection opened...");

                QueryTables xlQryTables = ws.QueryTables;
                IEnumerable<QueryTable> qt = xlQryTables.Cast<QueryTable>();
                foreach (QueryTable q in qt)
                {
                    q.Delete();
                }

                QueryTable xlQueryTable = (QueryTable)xlQryTables.Add(selectedConnection, range, szSql);
                xlQueryTable.RefreshStyle = XlCellInsertionMode.xlOverwriteCells;
                xlQueryTable.AfterRefresh += new RefreshEvents_AfterRefreshEventHandler(xlQueryTable_AfterRefresh);
                xlQueryTable.CommandType = XlCmdType.xlCmdSql;
                xlQueryTable.Refresh(true);

                assignReportHeader(ws);


            }
        }
        catch (Exception e)
        { 
            throw new Exception(e.Message);
        }
    }

The place I'm getting an error in is:

QueryTable xlQueryTable = (QueryTable)xlQryTables.Add(selectedConnection, range, szSql);

The connection string works... I can see the range is picked as well. Please help.

klashar
  • 2,519
  • 2
  • 28
  • 38
John Bull
  • 933
  • 1
  • 12
  • 20
  • 1
    Possible duplicate of [HRESULT: 0x800A03EC on Worksheet.range](http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range) – Smartis has left SO again Feb 07 '17 at 16:20
  • It's not a duplicate, I have explored all related topics before asking. The issue is definitely not in using a backwards compatible sheet. – John Bull Feb 07 '17 at 16:25

1 Answers1

0

I can't put the comment and becouse of that i will ask in this way. Can you write your explicitly connection string. I think the problem is in connection string, maybe you forgot some semicolon or something like that.

IR_IR
  • 184
  • 1
  • 1
  • 11
  • I pickup the connection string from xml file: Password=testpwd123;User ID=testusr@devdb001;Initial Catalog=DEV;Data Source=tcp:devdb001.database.windows.net; I can see the connection is open. – John Bull Feb 07 '17 at 16:55
  • but still, can you check the legality of connection? – IR_IR Feb 07 '17 at 17:01
  • what do you mean by that? I implemented this chunk of code to verify: //Check if the issue is in connection SqlConnection conn = new SqlConnection(selectedConnection); conn.Open(); MessageBox.Show("Connection opened..."); It works, connection opens. – John Bull Feb 07 '17 at 17:19
  • No, my reference is not for sql connection string my references going from your part of qestion: The place I'm getting an error in is: QueryTable xlQueryTable = (QueryTable)xlQryTables.Add(**selectedConnection**, range, szSql); – IR_IR Feb 07 '17 at 17:28
  • I don't know did you see this, but anyway i will put link and read first answer. [link](http://stackoverflow.com/questions/891394/excel-error-hresult-0x800a03ec-while-trying-to-get-range-with-cells-name) – IR_IR Feb 07 '17 at 17:36
  • I can't say for sure what causes that fatal error. Runtime gives an exception once it reach this line of code. – John Bull Feb 07 '17 at 17:37
  • Holy cow. It seems the problem is in connection string. By some reason I can connect to the local db with this string: Excel.QueryTable xlQueryTable = (Excel.QueryTable)xlQryTables.Add(String.Format(@"OLEDB;Provider=SQLOLEDB;Trusted_Connection=yes;Initial Catalog=ReportServer;Data Source=."), range, szSql); but, of course, it doesn't work for Azure – John Bull Feb 07 '17 at 18:09
  • Didi you think to use ListObjects, because when you use excel 2007 or higher you need to. Look this [link](https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm) – IR_IR Feb 08 '17 at 08:51
  • There is something strange in this line of code : `string szSql = string.Format(@"EXEC sp_Get_Excel_TOQ_Report_Data_Mkt {0}, {1}, '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', {10}", startDate, endDate, startTime, endTime, tickerSymbol, RegId, events, userId, PO, markets, includeJPO); ` You have 10 type of string variables and 1 type of bool and **first two** you do not pass like string? Is it because date type, or? – IR_IR Feb 08 '17 at 09:29