为什么设置OLEDBConnection.Connection会引发异常(HRESULT:0x800A03EC)? [英] Why setting OLEDBConnection.Connection throws Exception (HRESULT: 0x800A03EC)?

问题描述

我正在使用Excel Interop命名空间来修改Excel Workbook连接中的连接字符串.

I am working with Excel Interop namespace to modify connection string in the Excel Workbook connections.

为什么当我尝试设置连接属性时( MSDN OLEDBConnection .Connection )在分配的行上抛出错误?

Why when I am trying to set the connection property (MSDN OLEDBConnection.Connection) throws an error on the line of the assignment?

HRESULT的异常:0x800A03EC

Exception from HRESULT: 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();

但是，如下所示调用Replace方法是有效的.我发现这是一种解决方法，不确定在这种情况下替换"为何起作用.

However, calling Replace method as shown below is working. I have found this as workaround, not being sure 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();

实际上，这是我可以更改连接字符串的唯一方法，因此询问为什么set可能引发错误的原因是什么.

This in fact is the only way I could get the connection string changed, therefore asking what is the reason behind why set could be throwing the error.

推荐答案

问题在于连接字符串似乎具有特殊的前缀(OLEDB;)，该前缀在分配时已检查，因此可以通过以下方式解决:

Issue was that connection string seem to have special prefix (OLEDB;) which is checked upon the assignment so it can be solved by this:

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

前缀在连接字符串检索时显示出来，因此是个线索.

Prefix showed its face upon connection string retrieval, thus the clue.

尽管出于可测试性的考虑，建议使用OpenXML.

Though recommend OpenXML due to ease of testability.

这篇关于为什么设置OLEDBConnection.Connection会引发异常(HRESULT:0x800A03EC)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！