Unable to access the Microsoft Access database using asp.net and C#

Question

This is code i wrote to add some text to accordion pane on a button click:

protected void Button1_Click1(object sender, EventArgs e)
    {
        //Use a string variable to hold the ConnectionString.
        string connectString = "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source=D:\\C#Samples\\StudentDetails\\WebRole1\\App_Data\\Students1.accdb";
        System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection();
        cn.ConnectionString = connectString;
        //Create an OleDbConnection object, and then pass in the ConnectionString to the constructor.
        //OleDbConnection cn = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString);
        try
        {
            //Open the connection.
            cn.Open();
        }
        catch (Exception ex)
        {
            AccordionPane1.Controls.Add(new LiteralControl("Open Error"));
        }

            string selectString = "SELECT * FROM BasicInfo";

            //Create an OleDbCommand object.
            //Notice that this line passes in the SQL statement and the OleDbConnection object
            OleDbCommand cmd = new OleDbCommand(selectString, cn);

            //Send the CommandText to the connection, and then build an OleDbDataReader.
            //Note: The OleDbDataReader is forward-only.

            try
            {
                OleDbDataReader reader=null;
                try
                {
                    reader = cmd.ExecuteReader();
                }
                catch (Exception es)
                {
                    AccordionPane1.Controls.Add(new LiteralControl(" datareader"));
                }
                string s = "s";
                reader.Read();
                s = reader["S_No"].ToString();

                AccordionPane1.Controls.Add(new LiteralControl(s));
                //Close the reader and the related connection.
                reader.Close();
                cn.Close();
            }
            catch (Exception ex)
            {
                AccordionPane1.Controls.Add(new LiteralControl(" Read Error"));
            }
    }

I have my access 2007 database in the folder i specified in the connectString. When im viewing in the browser, on the button click i am getting all the three exceptions: enter image description here

What might be the problem in opening the database? Do i need to make any other changes?

You sir are swallowing the exceptions and that's why you can't debug and expect us to play the guessing game. — banging
– banging, Commented Aug 17, 2012 at 14:21

Fionnuala · Accepted Answer · 2012-08-17 14:28:23Z

2

Change

Provider=Microsoft.Jet.OLEDB.4.0;

to

Provider=Microsoft.ACE.OLEDB.12.0

Provider=Microsoft.ACE.OLEDB.12.0;"
            + "Data Source=D:\\C#Samples\\StudentDetails\\WebRole1\\App_Data\\Students1.accdb

Hopefully it will solve the issue.

edited Aug 17, 2012 at 14:28

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

answered Aug 17, 2012 at 14:22

MMK

3,7311 gold badge26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fionnuala Over a year ago

The above connection string is correct for the .accdb format (2007/2010), which is what the OP is using.

Pranay Rana · Accepted Answer · 2012-08-17 14:22:53Z

1

you connection string might be cause of isssue

string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\abc.mdb;";

OleDbConnection MyConn = new OleDbConnection(ConnStr);

this For access 2007 also check the path of database is cocrect.

answered Aug 17, 2012 at 14:22

Pranay Rana

177k37 gold badges244 silver badges266 bronze badges

Comments

KV Prajapati · Accepted Answer · 2012-08-17 14:32:59Z

1

You can use |DataDirectory| instead of real path and you have to change the Provider=Microsoft.ACE.OLEDB.12.0 (as suggested by @MMK)

 string connectString = @"Microsoft.ACE.OLEDB.12.0;
       Data Source=|DataDirectory|\Students1.accdb;Persist Security Info=False;";

and always use using block which dispose IDisposable objects properly.

using(OleDbConnection cn=new OleDbConnection())
{
 using(OleDbCommand cmd=new OleDbCommand())
 {
  cn.ConnectionString=connectionString;
  cmd.CommandText=selectString;
  cmd.Connection=cn;
  ...
 }
}

edited Aug 17, 2012 at 14:32

answered Aug 17, 2012 at 14:24

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

2 Comments

Pramod Over a year ago

Thank you. But i didn't get the reason why should we use "using". Can you please explain me

KV Prajapati Over a year ago

@Pramod - Read - What is the C# Using block and why should I use it?

Collectives™ on Stack Overflow

Unable to access the Microsoft Access database using asp.net and C#

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related