OleDbConnectionStringBuilder returning value incompatible with OleDbConnection

Question

We have run into an unexpected exception thrown when opening a OleDbConnection created from the return value of the OleDbConnectionStringBuilder.ConnectionString property. Were using the OleDbConnectionStringBuilder to update any relative DataSource paths to absolute paths.

We get an System.Data.OleDb.OleDbException exception when we call OleDbConnection.Open and the message states:
"Format of the initialization string does not conform to the OLE DB specification.".

The code is:

    var oleDBbuilder = new OleDbConnectionStringBuilder(connectString);

    oleDBbuilder.DataSource = ResolveDataSourcePath(oleDBbuilder.DataSource);

    m_pOleDb = new OleDbConnection(oleDBbuilder.ConnectionString);
    m_pOleDb.Open(); // <-- Throws exception here

We have had no issue with this until we attempted to process a connect string which contained the value Mode=ReadWrite|Share Deny None;. The value returned by OleDbConnectionStringBuilder.ConnectionString property returns the property with the its value quoted. e.g:

This connection string (variable 'connectString'):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SomePath\@Some Database.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False

becomes (OleDbConnectionStringBuilder.ConnectionString):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="c:\SomePath\Some Database.mdb";Persist Security Info=False;Mode="ReadWrite|Share Deny None"

The Mode value without quotes works without error!

I have a couple of questions:

Is this by design? Why does it return an incompatible value?
How can I work around this without duplicating the job of OleDbConnectionStringBuilder?

Can you give the values for `pszConnectString` and `oleDBbuilder.Provider`? — Irshad, Jan 18 '16 at 05:27
They are included near the bottom of the question. Only the path was slightly modified, the rest was copied straight from debugger. — karmasponge, Jan 18 '16 at 05:48
I've updated question in hopes of making things clearer regarding 'pszConnectString' (now 'connectString'). — karmasponge, Jan 20 '16 at 23:47

Olli · Accepted Answer · 2020-07-27T09:27:28.013

I don't have a real answer, just a work around.

Before using oleDBbuilder.ConnectionString you can remove the quotes with a regex like this:

m_pOleDb = new OleDbConnection(Regex.Replace(oleDBbuilder.ConnectionString, @"(?<=mode=)((?<quote>""|')?(?<inner>[^;]+?)\k<quote>)", "${inner}", RegexOptions.IgnoreCase));

instead of

m_pOleDb = new OleDbConnection(oleDBbuilder.ConnectionString);

Another option, if you are responsible for the original connection string, would be to replace the spaces in "Share Deny None" with a placeholder like "Share###Deny###None" before committing it to oleDBbuilder. At the end before using oleDBbuilder.ConnectionString this replacement must be undone again.

To answer your first question ("Is this by design?") I can only speculate. To me it looks more like careless programming. Net programmers ignored the fact that ADODB.ConnectionModeEnum has a kind of flags attribute, so that multiple values can be added with Or.

Edit

First: For me it looks like a misconception that keys with spaces inside are quoted. This is only necessary for keys with leading or trailing spaces.

Second: I did some tests. This works with Provider=VFPOLEDB.1 and Provider=Microsoft.ACE.OLEDB.12.0:

Mode=ReadWrite|Share Deny None
Mode=ReadWrite|"Share Deny None"

This doesn't work:

Mode="ReadWrite|Share Deny None"
Mode="ReadWrite"|"Share Deny None"

I've accepted this answer as the second suggestion is what was eventually implemented by another developer. However I probably now think it would be simpler/easier just to remove the argument then re-add to result of calling 'ConnectString'. — karmasponge, Jul 31 '20 at 02:55
@karmasponge First: misconception by MS is what I thought about. Second: I do agree, ConnectionStringBuilder isn't a good tool to make programmers live easier. — Olli, Jul 31 '20 at 18:42

OleDbConnectionStringBuilder returning value incompatible with OleDbConnection

1 Answers1