Why an InvalidOperationException for OleDbConnection / C#?

Question

string connectionString =
  "Provider=SQLOLEDB.1;" +
  "Integrated Security=SSPI;" +
  "Persist Security Info=False;" +
  "Trusted_Connection=False;" +
  "Data Source=XXX-LAPTOP\\SQLEXPRESS;" +
  "Initial Catalog=C:\\...\\SqlServerDbExample.mdf";

  OleDbConnection oleConnection = new OleDbConnection(connectionString);

The oleConnection.ServerVersion throws an exception of type System.InvalidOperationException.

I have viewed the SQL Server Management Studio (SSMS): ... {database name} -> Properties ... Security -> Windows Authentication mode (radio button), Failed logins only (radio button) ... Permissions -> View Server Permissions -> {computer name} -> Effective (tab) -> everything is listed

When does it throw the `Exception`? – Sybren Dec 26 '14 at 13:34 — Sybren, Dec 26 '14 at 13:34

Steve · Accepted Answer · 2014-12-26T13:51:41.653

0

Initial Catalog wants the logical name of the database:

"Initial Catalog=MyDatabaseExample;"

otherwise, if you wish to attach an MDF file to your existing instance of SQLExpress you need to use AttachDBFilename

"AttachDBFilename=C:\\...\\SqlServerDbExample.mdf";

A very detailed list of key\value pairs used in a connection string for SQL Server could be found in the docs about SqlConnection.ConnectionString albeit I am not sure if everything there is applicable to an OleDbConnection.

I don't know if there is some kind of requirement to use OleDb, but if nothing prevents it I suggest to use directly the SqlClient classes instead of OleDb ones.

edited Dec 26 '14 at 13:51

answered Dec 26 '14 at 13:34

Steve

213,761
22
232
286

I tried this ... same error message. ....... I have written OleDbConnection code for Access. I plan to write use this code for other connections like Oracle and SQL Server. Summaries of the differences are in the following links. http://stackoverflow.com/questions/7130332/difference-between-sql-connection-and-oledb-connection' https://social.msdn.microsoft.com/forums/vstudio/en-US/fadb2742-d75a-49fb-a839-b2d4a9183998/oledbconnection-vs-sqlconnection – ttom Dec 27 '14 at 13:50

score 0 · Answer 2 · answered Dec 28 '14 at 15:15

Steve is correct. This works:

string connectionString =
  "Provider=SQLOLEDB.1" + ";" +
  "Integrated Security=SSPI" + ";" +
  "Persist Security Info=False" + ";" +
  "Trusted_Connection=False" + ";" +
  "Data Source=CTE-LAPTOP\\SQLEXPRESS" + ";" +
  "Initial Catalog=SqlServerDbExample" + ";" + //Database name
  "AttachDBFilename=C:\\...\\SqlServerDbExample.mdf" + ""; //File name

OleDbConnection oleConnection = new OleDbConnection(connectionString);

Why an InvalidOperationException for OleDbConnection / C#?

2 Answers2