Semicolon in Connection String

[Davor Geci]

Hello,

I have a problem, in my connection string in the password I have a semicolon and can't change the password.
Do you have any idea how to solve it?

Here is the connection string:

DRIVER={MariaDB ODBC 3.1 Driver};Port=3306;SERVER=test2.myserver.com;DATABASE=lpuxpbbo_test2;USER=lpuxpbbo_test2;PASSWORD=G;-GvE$aaAe];OPTION=11;

Thanks for any suggestions,

Davor

[wqweto]

This works for user test with password test;123

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=UCSDB;User ID=test;Password=""test;123"""

cheers,
</wqw>

[dilettante]

You can also use apostrophes as string delimiters in a connection string.

[wqweto]

You can also use apostrophes as string delimiters in a connection string.

This

cn.Open "Provider=SQLOLEDB;Data Source=UCSDB;User ID=test;Password='test;123'"

bombs out with

Format of the initialization string does not conform to the OLE DB specification.

so it turns out single quotes cannot be used to escape semicolons inside values because single quotes can be used to escape double quotes inside value only.

Edit: Reading again the quoted reply I'm not sure what "string delimiters" actually means.

cheers,
</wqw>

[dilettante]

You must have something else going on. I use apostrophe delimiters in connection strings all the time. This is especially common in Jet 4.0's Extended Properties strings which typically contain semicolons.

[wqweto]

You must have something else going on.

As OP stated what's going on -- there is a value with a semicolon which breaks OLEDB connect string parsing if not quoted as far as I understand the original problem.

I just confirmed that such value cannot be quoted with single quotes. When value contains semicolon it *must* be quoted with double quotes. Other values (not containing semicolon) might be able to be quoted with single quotes.

Another caveat is that ODBC vs OLEDB vs .Net connect string quoting differs. ODBC allows braces ({ and }) to be used for quoting too it seems reading the docs (not tested).

cheers,
</wqw>

[dilettante]

Nonsense.

I use connection strings like that all the time without incident. OLEDB handles them just fine:

Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Text;CSVDelimited=True;Hdr=True';Data Source='.'

ODBC isn't involved in ADO connections, it is being invoked indirectly via OLEDB through the ODBC shim Provider MSDASQL, which is often left implied and thus defaulted.

[wqweto]

This one

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Text;CSVDelimited=True;Hdr=True';Data Source='test;123'"

. . . and this one

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Text;CSVDelimited=True;Hdr=True';Data Source='.';Password='test;123'"

. . . bomb out with "Format of the initialization string does not conform to the OLE DB specification."

I'll let you figure out on yourself how to pass a folder or a password with a semicolon in the connect string. (Hint: single quotes don't work.)

cheers,
</wqw>