MS Access Connection to ASP.NET

Question

I am trying to connect MS Access Database to ASP.Net to fetch data. But the below error keeps popping up. I have figured it has to do with Web.config connection but what am I doing wrong ,I still can't figure out?

Error-

strong textSystem.ArgumentException: Keyword not supported: 'provider'. at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) at System.Data.SqlClient.SqlConnection..ctor(String connectionString, SqlCredential credential) at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at Dummy.Page_Load(Object sender, EventArgs e) in C:\xyz\Dummy.aspx.cs:line 19

  <connectionStrings>
   <add name="MS_Access_DatabaseConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\temp\\Microsoft Access Database\\MS_Access_Database.mdb;" providerName="System.Data.OleDb"  />
  </connectionStrings>

protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["MS_Access_DatabaseConnectionString"].ConnectionString;

    try
    {
        SqlConnection conn = new SqlConnection(connectionString);
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        String sql = "SELECT [Bank], [Amount] FROM [BankDetails]";


        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {

                    Response.Write("" + reader.GetString(0));
                }

            }
        }
    }
    catch (Exception ex)
    {
        Response.Write(ex);
    }

Change your SqlConnection, SqlCommand, SqlDataReader To OleDbConnection, OleDbCommand, OleDbDataAdapter — Izzy
your connection string is wrong...take a look at - connectionstrings.com/access — NoviceProgrammer
BTW why are you using Access instead of eg SQL Server Express? Access is a desktop, file-based database, not meant for web sites. — Panagiotis Kanavos
The connection string wasn't an issue. I am using Access because I am still learning. — Anshu Saket

Tetsuya Yamamoto Tetsuya Yamamoto · Accepted Answer · 2018-09-10T07:47:47

Instead of using SqlConnection, you should use OleDbConnection, because SqlConnection trying to read your connection string as SQL Server connection string rather than Access database one:

try
{
    OleDbConnection conn = new OleDbConnection(connectionString);
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }

    String OleDb = "SELECT [Bank], [Amount] FROM [BankDetails]";


    using (OleDbCommand cmd = new OleDbCommand(OleDb, conn))
    {
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Response.Write("" + reader.GetString(0));
            }

        }
    }
}
catch (Exception ex)
{
    Response.Write(ex);
}

MS Access Connection to ASP.NET

2 Answers