A connection string is a string version of the initialization properties needed to connect to a data source and enables you to easily store connection information within your application or pass it between applications. Without a connection string, you would be required to store or pass a complex array of structures to access data.
In ADO, connection strings are retrieved and set using the ADO ConnectionString property to create a Connection object. When accessing OLE DB directly, using the IDataInitialize interface, the connection string is passed as a parameter to create an OLE DB data source object.
In some cases, it might be desirable to manually build a connection string within an application. For example, if you want to provide a custom user interface for your users, rather than using the standard data link user interface, then you would manually build the connection string. In these cases, it is important that the connection string syntax adhere to the formatting rules.
This section includes the following topics:
Basic Connection String Format
The basic format of a connection string is based on the ODBC connection string. The string contains a series of keyword/value pairs separated by semicolons. The equals sign (=) separates each keyword and its value.
Example keyword1=value; keyword2=value
The Provider Keyword
The Provider keyword identifies the OLE DB provider to be used. To specify your OLE DB provider, set the Provider keyword to the VersionIndependentProgID of the provider:
Example Provider=MSDASQL
The value can also be set to the ProgID of the provider, which may have a version attached to it:
Example Provider=MSDASQL.1
If two versions of a provider are installed on the same system, use the ProgID to specify exactly which version to use. If two versions are installed on a system and the VersionIndependentProgID is specified, the most recent version of the provider is used.
If no Provider keyword is in the string, the OLE DB Provider for ODBC (MSDASQL) is the default value. This provides backward compatibility with ODBC connection strings. The following ODBC connection string can be passed in and it will successfully connect:
Example Driver={SQL Server};Server=MyServer;db=pubs;uid=sa;pwd=MyPassword
If the Provider keyword is specified, but specifies a provider that does not exist or is invalid, the connection string returns an error:
Example Provider=;Database=MyDatabase
Specifying a Keyword
To identify a keyword used after the Provider keyword, use the Description property of the OLE DB initialization property that you want to set. Standard OLE DB properties are documented in the OLE DB Programmer's Reference under the OLE DB Initialization property group (DBPROPSET_DBINIT).
For example, the Description property of the standard OLE DB initialization property DBPROP_INIT_LOCATION is Location. Thus, to include this property in a connection string, use the keyword Location:
Example Provider=MSDASQL;Location=Pubs
For more information about OLE DB property groups, see Appendix C of the OLE DB Programmer's Reference.
For information about provider-specific initialization properties, see the documentation that came with your provider.
Keywords can contain any printable character except for the equals sign (=). All of the following examples are correct:
Example Provider=Microsoft.Jet.OLEDB.3.5.1;Jet OLE DB:System Database=c:\system.mda
The keyword is "Jet OLE DB:System Database".
Example Provider=Microsoft.Jet.OLEDB.3.5.1;Authentication;Info=Column 5
The keyword is "Authentication;Info".
If a keyword contains an equals sign (=), it must be preceded by an additional = sign to indicate that it is part of the keyword:
Example Provider=Microsoft.Jet.OLEDB.3.5.1;Verification==Security=True
The keyword is "Verification=Security".
If multiple = signs appear, each one must be preceded by an additional = sign:
Example Provider=MSDASQL;Many====One=Valid
The keyword is "Many==One".
Example Provider=MSDASQL;TooMany===VARIANT_FALSE
The keyword is "TooMany=".
Setting Keyword Values
The value of a keyword must be specified using a string format. The value depends on the property being set and the value that the OLE DB provider expects.
The data link API uses a shortened, "friendlier" version of the property values defined in the OLE DB specification. The short value of a property is derived by removing the prefix from the documented property value and replacing any underscores with spaces. For example, the OLE DB specification defines the following values for the Cache Authentication (DBPROP_AUTH_CACHE_AUTHINFO) property:
VARIANT_TRUEVARIANT_FALSEThe prefix "VARIANT_" is removed and the short values are "TRUE" and "FALSE".
Tip The short property values are case insensitive; thus the values can be made more readable by using "True" and "False".
Example Provider=MSDASQL;Location=Pubs;Cache Authentication=True
Another example is the Prompt (DBPROP_INIT_PROMPT) property for which the OLE DB specification defines the following values:
DBPROMPT_PROMPTDBPROMPT_COMPLETEDBPROMPT_COMPLETEREQUIREDDBPROMPT_NOPROMPTAgain, the prefix "DBPROMPT_" is removed and mixed case is used for the short values:
PromptCompleteCompleteRequiredNoPrompt
Example Provider=MSDASQL;Location=Pubs;Prompt=Complete
Some of the values defined for the Mode (DBPROP_INIT_MODE) property include:
DB_MODE_SHARE_DENY_READDB_MODE_SHARE_DENY_WRITEDB_MODE_SHARE_EXCLUSIVENote that the underscores after the prefix "DB_MODE_SHARE_" are replaced with spaces in the short values:
Deny ReadDeny WriteShare ExclusiveThese shortened, "friendlier" property values apply only to those properties that are documented in the OLE DB specification. Note that different OLE DB data providers may use custom properties and/or property values. The OLE DB Initialization Properties: Quick Reference lists the property description and the short property value (where applicable) for each OLE DB initialization property.
To set a numeric property value, use either a decimal, hex, or octal value.
Examples:
Provider= Microsoft.Jet.OLEDB.3.5.1; Window Handle=123
Provider= Microsoft.Jet.OLEDB.3.5.1; Window Handle=0x123
Provider= Microsoft.Jet.OLEDB.3.5.1; Window Handle=0123
To include values that contain a semicolon, single-quote character, or double-quote character, the value must be quoted. Typically, the value will be quoted using the double-quote character:
Example Provider=MSDASQL;ExtendedProperties="UID=sa;pwd=sa;Databse=MyDB"
If the value contains both a semicolon and a double-quote character, the value can be quoted using the single-quote character:
Example Provider=MSDASQL;ExtendedProperties='UID=sa;pwd=sa;Databse="My DB"'
The value for the UID keyword is UID=sa;pwd=sa;Databse="My DB".
The single-quote is also useful if the value begins with a double-quote character:
Example Provider=SQLOLEDB;DataSchema='"MyCustTable"'
The value for the DataSchema keyword is "MyCustTable".
Conversely, the double-quote can be used if the value begins with a single-quote:
Example Provider=SQLOLEDB;DataSchema="'MyOtherCustTable'"
The value is 'MyOtherCustTable'.
If the value contains both single- and double-quote characters, the quote character used to surround the value must be doubled each time it occurs within the value:
Example Provider=SQLOLEDB;NewRecordsCaption='"Company''s "new" customer"'
The value is "Company's "new" customer".
Example Provider=SQLOLEDB;NewRecordsCaption="""Company''s ""new"" customer"""
The value is "Company's "new" customer".
Quotes can also be used if the value begins or ends with spaces:
Example Provider=SQLOLEDB;Database Name=" Badly Named Database "
Any leading or trailing spaces around a keyword or value are ignored. However, spaces within a keyword or value are allowed and recognized.
Example Provider=MSDASQL;MyKeyword=My Value
The value for the MyKeyword keyword is My[space]Value.
Example Provider=MSDASQL;MyKeyword= My Value ;MyNextValue=Value
The value for the MyKeyword keyword is My[space]Value.
To include preceding or trailing spaces in the value, the value must be quoted using either the single or double quote mark.
Example Provider=MSDASQL;MyKeyword= ' My Value '
The value for the MyKeyword keyword is [space]My[space]Value[space][space].
Example Provider=MSDASQL;MyKeyword= " My Value "
The value for the MyKeyword keyword is [space][space]My[space]Value[space].
There is one exception to this behavior. If the keyword does not correspond to a standard OLE DB Initialization property—in which case, the keyword value is placed in the Extended Properties (DBPROP_INIT_PROVIDERSTRING) property—the spaces around the value will be included in the value. This is done to support backward compatibility for ODBC connection strings.
Example Driver=SQL Server;uid= MyName;pwd= MyPassword
The actual connection string generated would be:
Provider=MSDASQL;Extended Properties="Driver=SQL Server;uid= MyName;pwd= MyPassword"
The value for the uid keyword is [space]MyName.
The value for the pwd keyword is [space]MyPassword.
In this case, the underlying provider can deal with white spaces in its own manner.
For the OLE DB initialization properties specified in the OLE DB specification that can return multiple values, such as the Mode property, each value returned is separated with a pipe (|) character. The pipe character can have spaces around it or not:
Example Provider=MSDASQL;Mode=Deny Write|Deny Read
Listing Keywords Multiple Times
If a connection string contains a keyword/value pair multiple times, the last occurrence listed is used in the value set:
Example Provider=MSDASQL;Location=Pubs;Cache Authentication=True;Prompt=DBPROMPT_COMPLETE;Location=Customers
In this case, the Location property will be set to Customers.
One exception to this is the Provider keyword. If this keyword occurs multiple times in the string, the first occurrence is used:
Example Provider=MSDASQL;Location=Pubs; Provider=SQLOLEDB
In this case, MSDASQL will be used.
Setting the Window Handle Property
To set the Window Handle (DBPROP_INIT_HWND) property in a connection string, a long integer value is typically used:
Example Provider=Microsoft.Jet.OLEDB.3.5.1;Window Handle=14829
Note When the string is returned, the Window Handle keyword will not be included in the string because Window Handle is not a property that should be persisted.
Format Limitations
The connection string format has some limitations on what initialization settings it can persist and set.
COLID Properties
The connection string format does not support the ability to persist provider-specific properties that require a COLID for extra information.
Mathematical Operations
No mathematical operations will be performed on the value set. For example, setting a property to "2+3" will be set to the constant "2+3". If the property is not a string data type, it will be an illegal value. Similarly, the C technique of joining items together in a logical OR using the pipe (|) character is not allowed:
Example Provider=MSDASQL;DBType=3|5
Example Provider=MSDASQL;Connection Timeout=(34*23)/8
Each of these examples is illegal, assuming the property data types are not string. If they are string, they will be set to the constant specified.
A Note to Provider Writers
If you are writing an OLE DB data provider, your implementation is independent of any of the formatting rules for connection strings. OLE DB implements and validates these rules seamlessly for you.
See Also
Referencing a Connection String in Your Application
OLE DB Initialization Properties: Quick Reference, which is taken from Appendix C of the OLE DB Programmer's Reference.