CODEWITHSUNDEEP
c#asp.netms-access
Anshu Saket
Anshu Saket

Reputation: 21

MS Access Connection to ASP.NET

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);
    }

Upvotes: 0

Views: 1355

Answers (2)

user10340688
user10340688

Reputation:

  1. Add using System.Data.OleDb.OleDbConnection; to the top of your file, and remove the using System.Data.SqlConnection;

  2. Change Your Code to.

    using (OleDbConnection con = new OleDbConnection(connDB)) //here is the error { }

Upvotes: 0

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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);
}

Upvotes: 1

Related Questions