Why does setting OLEDBConnection.Connection throw an exception (HRESULT: 0x800A03EC)?

Question

I am working with the Excel Interop namespace to change the connection string in Excel workbook connections.

Why, when I try to set the connection property ( MSDN OLEDBConnection.Connection ), does the error appear in the target line?

HRESULT exception: 0x800A03EC

application = new Application();
Workbook wb = application.Workbooks.Open(file.FullName);
Sheets wbs = wb.Worksheets;
IEnumerable<Workbook> workbooks = application.Workbooks.Cast<Workbook>();

foreach (var connection in wb.Connections.Cast<WorkbookConnection>()
    .Where(c => c.Type == XlConnectionType.xlConnectionTypeOLEDB))
{
    connection.OLEDBConnection.Connection = "Test Connection String";
}

application.Quit();

However, calling the Replace method, as shown below, works. I found this as a workaround, not knowing why Replace works in this case.

application = new Application();
Workbook wb = application.Workbooks.Open(file.FullName);
Sheets wbs = wb.Worksheets;
IEnumerable<Workbook> workbooks = application.Workbooks.Cast<Workbook>();

foreach (var connection in wb.Connections.Cast<WorkbookConnection>()
    .Where(c => c.Type == XlConnectionType.xlConnectionTypeOLEDB))
{
    var conString = connection.OLEDBConnection.Connection.ToString();
    connection.OLEDBConnection.Connection = 
        conString.Replace("Test Connection String", "New Test Connection String");
}

application.Quit();

This is actually the only way I can change the connection string, so I ask what is the reason why set may cause an error.

Answer 1

, (OLEDB;), , :

...
connection.OLEDBConnection.Connection = "OLEDB;Test Connection String";
...

, , .

OpenXML - .

Answer 2

. . , XLS .

- . DefaultSaveFormat xlOpenXML - "", , , Excel.

COM , , , - - .

, :

http://www.hagrin.com/319/exception-hresult-0x800a03ec-excel-net-sql-and-windows-server-2008

Answer 3

I found a way to use OpenXML instead, it seems that changing the connection string through interop was not the only way.

using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(file.FullName, true))
{
    WorkbookPart workbookpart = excelDoc.WorkbookPart;
    ConnectionsPart connPart = workbookpart.ConnectionsPart;

    string spreadsheetmlNamespace = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    NameTable nt = new NameTable();
    XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
    nsManager.AddNamespace("sh", spreadsheetmlNamespace);

    XmlDocument xdoc = new XmlDocument(nt);
    xdoc.Load(connPart.GetStream());

    XmlNode oxmlNode = xdoc.SelectSingleNode("/sh:connections/sh:connection/sh:dbPr/@connection", nsManager);
    string newConnection = ReplaceInitialCatalog(oxmlNode.Value, repConfig.DbName);
    oxmlNode.Value = oxmlNode.Value.Replace(oxmlNode.Value, newConnection);

    //Truncates part with FeedData
    connPart.FeedData(connPart.GetStream());
    xdoc.Save(connPart.GetStream());
}