Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Web MS-Access 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.
Posted 3-Sep-12 22:33pm
Edited 3-Sep-12 22:42pm
496.2K
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 450
1 OriginalGriff 403
2 Sergey Alexandrovich Kryukov 239
3 CPallini 189
4 CHill60 180
0 OriginalGriff 6,092
1 Sergey Alexandrovich Kryukov 4,972
2 Maciej Los 3,269
3 Peter Leow 3,129
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 4 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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