OleDB connection string for SQL Server in a C# program

Question

I have seen lots of answers to connect to MS Access via OleDB but there is not good answer for SQL Server. I try to connect to a SQL Server database via OleDB provider in my C# program.

This is the connection string I am providing.

Provider=SQLOLEDB;Data Source=<servername>;Initial Catalog=<dbname>;Integrated Security=SSPI

But it gives me error

‘Keyword not support ‘Provider’’

What I want to do here is connect database via OleDB in C# program.

Solution 1

This works as expected on my side. From the error message I strongly suspect that you are using the SqlConnection class instead of the OleDbConnection (Of course you need to use all the other classes provided by OleDb like OleDbCommand, OleDbDataReader etc...)

  string connStr = "Provider=SQLOLEDB;Data Source=<servername>;Initial Catalog=<dbname>;Integrated Security=SSPI";
  using(OleDbConnection cnn = new OleDbConnection(connStr))
  {
     ....
  }

Solution 2

When in doubt, use the string builder in visual studio. That way unsupported keywords can't creep into your connection strings, the following is a wonderful example on how to use it.

http://www.c-sharpcorner.com/uploadfile/suthish_nair/how-to-generate-or-find-connection-string-from-visual-studio/

Solution 3

The connection string you're using, considering the OLE DB provider, is correct. I didn't find any error in the connection string used, if you want to connect to a SQL Server data source.

Most probably, the reason of that error should be that you're not using correctly all the classes and objects required by the OLE DB provider, like OleDbCommand (that is similar to a SqlCommand but it's different), OleDbConnection, OleDbDataAdapter and so on. In a nutshell, the reason of that error should be this:

string connStr = "Provider=SQLOLEDB;Data Source=<servername>;Initial Catalog=<dbname>;Integrated Security=SSPI";
using(SqlConnection scn = new SqlConnection(connStr))
  {
     ....
  }

Indeed, using a SqlConnection object, the ConnectionString property doesn't support the keyword Provider and, executing your application, you got an error about a keyword not supported.

Have a look at this simple tutorial about the use of OLE DB provider.

Solution 4

The same Connection string is working fine at my end. I am posting my sample code which is executes successfully at my end

public   string connStr = "Provider=SQLOLEDB;Data Source=.;Initial  Catalog=<dbName>;Integrated Security=SSPI";
  public OleDbConnection con;
    protected void Page_Load(object sender, EventArgs e)
    {

        Test();
    }

    public void Test()
    {
        con = new OleDbConnection(connStr);
        con.Open();
        OleDbCommand cmd = new OleDbCommand("select * from tblApartments", con);
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
    }

Please place breakpoint and check line to line and when your breakpoint comes to con.close(); then check ds, you can see the output.

View more solutions

Comments

[user2841795]

at System.Data.ProviderBase.DbConnectionClosed.get_ServerVersio‌​n() at System.Data.OleDb.OleDbConnection.get_ServerVersion() two errors while connect.

[Surya]

Can you please place the code (or) tell me where the error is occur while connection is opening/closing (or) place error in detail