Click here to Skip to main content
14,448,369 members

Connecting a MS Access (.mdb) Database to a MVC3 Web Application

Member 9648718 asked:

Open original thread
Hi,

I am writing a app that must have the ability to :

- GUI should be seen on a web interface (Thus I'm using C# Web Application)
- Be able to select .mdb files
- Add rows
- Delete rows
- Edit rows
- Save everything back to the .mdb file.

I have googled for ages.

I have written a similar application in a normal C# windows form application, I used OLEdb to do everything and it worked fine, it wasn't restricted to only one .mdb file.

In the C# Windows Form app I used Gridview control.
As the Web Application doesn't have Gridview, I used a datagrid.

Here is the code is have so far :
    public partial class _Default : System.Web.UI.Page
    {

        string databasePath = "____________.mdb";
        DataTable userTable = new DataTable();
        OleDbDataAdapter adapter;
        OleDbConnection connection;
        OleDbCommand command;
        OleDbCommandBuilder builder;
        DataSet ds;
        DataSet tempDs;
        public string DatabaseName = null;
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=";



        protected void Page_Load(object sender, EventArgs e)
        {
            tbDatabasePath.Text = databasePath;
        }

        protected void btnLoadData_Click(object sender, EventArgs e)
        {
            ReadRecords();
        }

        protected void btnGetTables_Click(object sender, EventArgs e)
        {
            try
            {
                ddTables.Items.Clear();
                ddTables.Text = null;
                getTables();

            }
            catch (Exception ex)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Could not retrieve the Tables');", true);
            }
        }


        private void getTables()
        {

            string tempstring = "";
            string getTConnection = connectionString + databasePath; ;

            OleDbConnection myConnection = new OleDbConnection(getTConnection);

            myConnection.Open();

            DataTable datT = myConnection.GetSchema("Tables");

            for (int i = 0; i < datT.Rows.Count; i++)
            {
                tempstring = datT.Rows[i][2].ToString();

                if (tempstring.Contains("MSys"))
                {
                }
                else
                {
                    ddTables.Items.Add(datT.Rows[i][2].ToString());
                }
            }
            myConnection.Close();
        }

        private void ReadRecords()
        {
            datagrid.DataSource = null;

            OleDbDataReader reader = null;

            try
            {
                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + databasePath);
                OleDbCommand cmd = new OleDbCommand("Select * FROM " + ddTables.Text, connection);
                adapter = new OleDbDataAdapter(cmd);
                builder = new OleDbCommandBuilder(adapter);
                ds = new DataSet("MainDataSet");
                tempDs = new DataSet("TempDataSet");

                connection.Open();
                adapter.Fill(tempDs);
                tempDs.Clear();
                tempDs.Dispose();
                adapter.Fill(ds, ddTables.Text);
                userTable = ds.Tables[ddTables.Text];

            }
            catch
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Could not load the data');", true);
            }
            finally
            {
                if (reader != null) reader.Close();
                //if (connection != null) connection.Close();
                datagrid.ShowFooter = true;
            }
            CreateTempTable(0, 10);
            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('DONE');", true);

        }

        private void ReadRecords2()
        {
           // datagrid.DataSource = null;
          
            OleDbDataReader reader = null;

            try
            {
                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + databasePath);
                OleDbCommand cmd = new OleDbCommand("Select * FROM " + ddTables.Text, connection);
                adapter = new OleDbDataAdapter(cmd);
                builder = new OleDbCommandBuilder(adapter);
                ds = new DataSet("MainDataSet");
                tempDs = new DataSet("TempDataSet");

                connection.Open();
                adapter.Fill(tempDs);
                tempDs.Clear();
                tempDs.Dispose();
                adapter.Fill(ds, ddTables.Text);
                userTable = ds.Tables[ddTables.Text];

            }
            catch
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Could not load the data');", true);
            }
            finally
            {
                if (reader != null) reader.Close();
                //if (connection != null) connection.Close();
            }
            CreateTempTable(0, 10);
            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('DONE');", true);

        }

        private void CreateTempTable(int startRecord, int noOfRecords)
        {
            try
            {
                userTable.Rows.Clear();
                adapter.Fill(ds, "");
                userTable = ds.Tables[""];
            }
            catch (Exception ex)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Could not load the data in  CreateTempTable');", true);
            }


            datagrid.DataSource = userTable.DefaultView;
            datagrid.DataBind();

        }

        protected void datagrid_RowEditing(object sender, GridViewEditEventArgs e)
        {
            datagrid.EditIndex = e.NewEditIndex;
            ReadRecords();
          
        }

        protected void datagrid_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void datagrid_RowUpdated(object sender, GridViewUpdatedEventArgs e)
        {
            adapter.Update(userTable);
        }

        protected void datagrid_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            
            adapter.Update(userTable);
            ReadRecords();
        }

        protected void datagrid_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {

           datagrid.EditIndex = -1;
           ReadRecords();

        }

        protected void datagrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int rowToDelete = e.RowIndex;
            datagrid.DeleteRow(rowToDelete);
            adapter.Update(userTable);
            ReadRecords();

        }
    }
}

It can :

- get the .mdb file
- get the tables in the file and display it
- read the data out of the selected table and display it in the gridview
- add the edit delete buttons

Now when I click the edit button, it makes the selected row editable.
But when I click the update button I get "Object reference not set to an instance of an object."
The code worked in the Forms application..

Is there any way the adapter.Update(userTable); will be able to work the same it worked in the forms application?

Can anybody please help me see my problem. If I can only get it to save the changes back to the .mdb file I would be extremely glad.
Tags: C#, Web, OleDb, Access

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100