'Keyword not supported: 'provider'. Opening SqlConnection

I don't know why this error, I tried everything. I want to connect my webForm to the Database .accdb and when I use using(){} I got this error "Keyword not supported: 'provider" Here is the code:

web.config

<connectionStrings>
    <add name="ConnectionString"
    connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Manuel_2\Documents\Login.accdb"     
    providerName="System.Data.OleDb" />
</connectionStrings>

WebForm1

private static string conDB =        
            ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;    

protected void Page_Load(object sender, EventArgs e)
{    
   using (SqlConnection con = new SqlConnection(connDB))  //here is the error
   {
       // .....                            
   }              
}
c++sharpasp.net


Solution 1:[1]

I know this is somewhat an old thread and already answered but i'm adding my solution for future reference

I have SQL server 11.0 database, and i encountered the error when i was trying to work with it in SharePoint app, I haven't tried the other proposed answers, but i simply just deleted the "Provider" portion (and reordered) , so my connection string which looked like this:

Provider=SQLOLEDB.1;Password=DBPassword;Persist Security Info=True;User ID=sa;Initial Catalog=DBName;Data Source=DBServer

Now looks like this:

Data Source=DBServer;Initial Catalog=DBName;Persist Security Info=True;User ID=sa;Password=DBPassword;

And it worked just fine

Solution 2:[2]

You should use System.Data.OleDb.OleDbConnection.

Solution 3:[3]

I found the error in question here (and similar) while working in Visual Studio and passing the Connection Manager name as a parameter to the Script Task to determine its connection string. Using the ConnectionString method brings a connection string with more elements (or value/pairs) than expected (including provider). The expected connection string, in my case, required only Data Source, Initial Catalog, and Integrated Security.

There are two options I found to solve this issue. The first, which didn't really work for me but hope it works for you, was to do the following:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(Dts.Connections[connMgrName].ConnectionString);
string connectionString = builder.DataSource + ";" + builder.InitialCatalog + ";" + builder.IntegratedSecurity;

I hope the above works for you because you solve the issue in a couple lines of code. However, the option that worked for me was to recreate my connection string by only selecting the value/pairs that the database needed:

string connectionString = Dts.Connections[connMgrName].ConnectionString;    //  ConnectionString will contain unsupported keywords like 'provider'
connectionString = connectionString.Trim(';'); //  Remove the trailing semicolon so that when we perform the split in the following line, there are no index errors.
var connStrDictionary = connectionString.Split(';').Select(x => x.Split('=')).ToDictionary(x => x[0], x => x[1]);     //   Here we get each value-pair from connection string by splitting by ';', then splitting each element by '=' and adding the pair to a Dictionary.
try
{
    connectionString = "Data Source=" + connStrDictionary["Data Source"] + ";Initial Catalog=" + connStrDictionary["Initial Catalog"] + ";Integrated Security=" + connStrDictionary["Integrated Security"]; //  Build the actual connection string to be used.
}
catch(KeyNotFoundException)
{
    Console.WriteLine("\t\tNot able to build the connection string due to invalid keyword used. Existing keywords and their values:");
    foreach( KeyValuePair<string, string> kvp in connStrDictionary)
    {
        Console.WriteLine("\t\t\tKey = '{0}', Value = '{1}'", kvp.Key, kvp.Value);
    }
}

Hope it helps. Good luck!

Solution 4:[4]

OK, I know this is pretty old, and I know that the answer was right in front of my face the whole time, but I'd like to emphasize that the one parameter that was messing me up because it was missing wasproviderName="System.Data.OleDb". Just in case anyone else looking at this is as obtuse as I am.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Aleksey Mynkov
Solution 3 Marcos
Solution 4 Bill Norman

Related Questions