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

See more:

C#

MS-Access

web

OleDb

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.