Walkthrough: Editing an Access Database with ADO.NET

Article
08/15/2008

Martin Tracy
Visual Studio Team
Microsoft Corporation

June 2003

Applies to:
   Microsoft® ADO.NET
   Microsoft® ASP.NET
   Microsoft® Visual C#® .NET
   Microsoft® Visual Studio® .NET

Summary: This walkthrough demonstrates how to add, delete, and edit records in a Microsoft® Access database with ADO.NET using a simple Visual C#® code-behind Web application. (11 printed pages)

Download the sample code ADONETAccessDB.exe. (130KB)

Introduction
Requirements
Walkthrough
References

Introduction

The Jet database engine is provided by Microsoft® Internet Information Services (IIS) for Microsoft Access databases. This database engine is a popular alternative to Microsoft SQL Server and is useful for low-volume Web applications. The Jet database engine is OLEDB-compliant and is supported by classes in the Microsoft .NET Framework System.Data.OleDb namespace.

The main objects you will use in this walkthrough are the OleDbConnection, OleDbCommand, and OleDbDataReader objects, and the DataGrid server control.

Figure 1 The ADO.NET data access pipeline

The OleDbConnection object handles the connection to the Jet database engine. The OleDbCommand contains the Microsoft SQL statement that tells the database engine what to do. The OleDbDataReader is a fast read-only forward-only database cursor that reads records selected by the SQL statement. The DataGrid displays the records and fires events when you add, delete, or edit a record. These events create new SQL statements that alter the database and display the resulting records.

Requirements

Before you start, you must have the following software installed on your computer:

Windows® XP with SP1 and all hotfixes
Microsoft Internet Information Services (IIS)
Microsoft Access 2002
Visual Studio .NET

Be sure you can create a simple Visual C# ASP.NET Web application before attempting the walkthrough.

Walkthrough

In this walkthrough, you will create a database using Microsoft Access and will create, add, edit, and delete records using ADO.NET.

To create the database

Open Microsoft Access and create a blank database named Pets.mdb in the new folder C:\Pets.
Create a new table in Designer view.
Add an AutoNumber field named ID and two Text fields named PetName and PetType. Make ID the primary key and accept all defaults.

Figure 2 Designing the data table in Access
Switch to datasheet view and save the table as PetTable.
Add a few entries to the table.

Figure 3 Adding test data
Save the table and close Access.

To display the database records

The OleDbConnection object holds the connection string that connects the Jet database engine to the Pets.mdb database. When constructing the connection string, the location of the Pets.mdb file is given relative to the root of the application. The relative path is changed to a physical path by the Server.MapPath method. This lets you publish the Web application to a remote server while still making it available to the Jet database engine.

The OleDbCommand object holds the SQL statement SELECT * FROM PetTable, which selects all the records in PetTable. The OleDbCommand.ExecuteReader method creates an OleDbDataReader object to read these records. The DataGrid is connected to the data reader through its DataGrid.DataSource property.

When the DataGrid.DataBind method executes, database records are moved from the database to the DataGrid, which displays them one record per row.

Open Visual Studio .NET.
Create a new Visual C# ASP.NET Web application at https://localhost/Pets.
Rename the file WebForm1.aspx to PetForm.aspx.
From the Solution Explorer, right-click the Pets project root and select Add/New Folder. Name the folder Pets. Leave the folder selected.
Right-click the Pets project and add the existing item C:\Pets\Pets.mdb to the Pets folder. You will have to select All Files in the Files of Type dropdown list to see the Pets.mdb file in the Existing Item browser window.
From the toolbox, drag a DataGrid to the Web form, renaming it to datagrid.
Switch to code view by double-clicking the form.
Add this line to the using statements at the beginning of WebForm1.aspx.cs
```
using System.Data.OleDb;
```

Insert this code into the Page_Load method:

    private void Page_Load(object sender, System.EventArgs e)
    {
         if (!IsPostBack)  ReadRecords();
    }

Add the ReadRecords method to the PetForm class just after the Page_Load method:

private void ReadRecords()
{
    OleDbConnection conn = null;
    OleDbDataReader reader = null;
    try
    {
        conn = new OleDbConnection(
            "Provider=Microsoft.Jet.OLEDB.4.0; " + 
            "Data Source=" + Server.MapPath("Pets/Pets.mdb"));
        conn.Open();

        OleDbCommand cmd = 
            new OleDbCommand("Select * FROM PetTable", conn);
        reader = cmd.ExecuteReader();

        datagrid.DataSource = reader;
        datagrid.DataBind();
}
//        catch (Exception e)
//        {
//            Response.Write(e.Message);
//            Response.End();
//        }
    finally
    {
        if (reader != null)  reader.Close();
        if (conn != null)  conn.Close();
    }
}

Press F5 to launch the Web application under the debugger. The contents of the database should appear in the browser.

Figure 4 The database as seen in the browser
Close the application, and then close Visual Studio.

Note The catch statement is commented out in the code above. By default, Visual Studio creates a C# Web application project with the Generate Debugging Information property set to true. This creates a Pets.pdb file in the bin directory. The default ASP.NET error handler uses this information to create a detailed error page, which shows the source line where the error occurred, as well as a stack trace and other error information.

Once your project is debugged, you can set Generate Debugging Information to false, and Pets.pdb will no longer be built. At this point, you can uncomment the catch statement and substitute your own error handler.

To configure the database

The ASP.NET user, by default, does not have permission to write a record to a database or create a locking file (.ldb) in the folder containing the database. You must give the ASP.NET user these permissions. Normally, this is done in one of three ways:

You can add the ASP.NET user to the Administrators group.
You can enable impersonation for the application in the web.config file.
You can add ASP.NET write permission to both the database file and the folder that contains it.

In this walkthrough you will use the third (and safest) method to grant write permission.

From the File Explorer, find the new Pets folder, normally located at C:\Inetpub\wwwroot\Pets\Pets.
Right-click the Pets folder, and select Properties.
Select the Security tab, and click the Add button.
Add the object name <YOURMACHINE>\ASPNET where <YOURMACHINE> is the name of your machine. Click OK to return to the Security tab.
Select the ASP.NET account, and add Write permission. This account is named aspnet_wp account, ASP.NET MACHINE Account, or something similar.
From the File Explorer, right-click the file Pets.mdb, and select Properties.
Select the Security tab, and click the Advanced button.
Check "Inherit from parent the permissions entries that apply to child objects". Click OK to accept the change.

For more information on ASP.NET security issues, see Authentication in ASP.NET: .NET Security Guidance.

To create the DataGrid edit column

You can use the Visual Studio Property Builder to add columns to a DataGrid. The Property Builder has a choice of formats that can add color and style to the DataGrid.

From Visual Studio Designer view, select the DataGrid. If you don't have the Property window open, open it from the View menu.
At the very bottom of the Properties window you will see two links: Auto Format and Property Builder. Select Property Builder.
Select Columns view.
Uncheck "Create columns automatically at run time."
In the Available Columns list, expand the Button Column. Select the Edit, Update, Cancel option. Click the ">" button to add it to the Selected Columns list.
In the Available Columns list, select Bound Column. Click the ">" button to add it to the Selected Columns list. Give it the Header text "Pet Name" and the Data Field "PetName."
Repeat the last step to add another Bound Column, giving it the Header text "Pet Type" and the Data Field "PetType."
Click OK to return to Designer view. The DataGrid will reflect the changes.
At the very bottom of the Properties window, select Auto Format.
Select a format, such as "Colorful 1."
Click OK to return to Designer view. The DataGrid will reflect the changes.

Figure 5 The DataGrid with the Edit links added
Press F5 to launch the Web application under the debugger. The contents of the database should appear in the browser.

Figure 6 Edit links and data in the browser

To edit the DataGrid

The DataGrid.EditItemIndex property selects a row for editing. When a row is selected for editing, textboxes appear in each cell. The text in each textbox is set to the value of the corresponding field in the data record.

You must connect the Edit link to an event handler that selects the row containing the link for editing. You should also connect the Cancel link (not yet visible) to an event handler that restores the DataGrid row without changing the corresponding record.

From Visual Studio Designer view, select the DataGrid, and click the Events tab in the Properties window (the lightning bolt). Double-click CancelCommand to create the datagrid_CancelCommand event handler. Return to the Events tab and double-click EditCommand to create the datagrid_EditCommand event handler.

Insert the following code into the two event handlers:

private void datagrid_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    datagrid.EditItemIndex = -1;
    ReadRecords();    
}
private void datagrid_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    datagrid.EditItemIndex = e.Item.ItemIndex;
    ReadRecords();
}

Press F5 to launch the Web application.
Click the Edit link to the left of the second row. The DataGrid displays the row with the values for the fields PetName and PetType in textboxes. The Edit link changes to the Update and Cancel links.

Figure 7 Editing data in the browser, having clicked the Edit link
Click Cancel to return to the default DataGrid display.

To update the DataGrid

When a row is selected for editing, the Update and Cancel links appear. Once you have used the textboxes to enter new values for the fields in a database record, you must move these changes back to the database.

You must connect the Update link to an event handler that reads each textbox and updates the fields in the corresponding record. The DataGrid.DataKeyField uses the key field of the database table to associate each row with its corresponding record. You build a SQL statement to update the record with the new field values, then call OleDbCommand.ExecuteNonQuery to perform the update.

From Visual Studio Designer view, select the DataGrid. In the Properties window, set the DataKeyField property to ID.
Click the Events tab in the Properties view (the lightning bolt). Double-click UpdateCommand to create the datagrid_UpdateCommand event handler.

Insert the following code into the event handler:

private void datagrid_UpdateCommand(object source, 
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    int ID = (int) datagrid.DataKeys[(int) e.Item.ItemIndex];
    
    string name = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
    string type = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
    
    string sql = 
        "UPDATE PetTable SET PetName=\"" + name + 
        "\", PetType=\"" + type + "\"" +
        " WHERE ID=" + ID;
    ExecuteNonQuery(sql);
    
    datagrid.EditItemIndex = -1;
    ReadRecords();
}

Add the ExecuteNonQuery method to the PetForm class:

private void ExecuteNonQuery(string sql)
{
    OleDbConnection conn = null;
    try
    {
        conn = new OleDbConnection(
            "Provider=Microsoft.Jet.OLEDB.4.0; " + 
            "Data Source=" + Server.MapPath("Pets/Pets.mdb"));
        conn.Open();

        OleDbCommand cmd = 
            new OleDbCommand(sql, conn);
        cmd.ExecuteNonQuery();
    }
//  catch (Exception e)
//  {
//      Response.Write(e.Message);
//      Response.End();
//  }
    finally
    {
        if (conn != null)  conn.Close();
    }
}

Press F5 to launch the Web application.
Click the Edit link to the left of the second row.
Change the text in the PetType textbox from "dog" to "dawg."
Click the Update link to see the updated DataGrid.

To add a record to the DataGrid

The DataGrid doesn't support an Add column, but you can use a button click event handler to add a record to the database. Create a SQL statement to add the record, then call OleDbCommand.ExecuteNonQuery to update the database.

From Visual Studio Designer view, drag a button from the toolbar to the form and name it btnAddPet. Change its text to "Add Pet."

Figure 8 Adding an Add button to the DataGrid

Double-click the button and insert this code in the btnAddPet_Click method:

private void btnAddPet_Click(object sender, System.EventArgs e)
{
    string sql = "INSERT INTO PetTable (PetName, PetType)"
        + " VALUES (\"new\", \"new\")";
    ExecuteNonQuery(sql);
    ReadRecords();
}

Press F5 to launch the Web application.
Click the Add button. A new row is added to the database.

Figure 9 The new row added

To delete a row from the DataGrid

You can use the Property Builder to add a Delete column to the DataGrid. You must connect the Delete link to an event handler that deletes the corresponding record in the database. You can use the DataGrid.DataKeyField property to associate the row to be deleted with its corresponding record. Create a SQL statement to delete the record, then call OleDbCommand.ExecuteNonQuery to update the database.

From Visual Studio Designer view, select the DataGrid.
At the very bottom of the Properties window, select Property Builder.
Select Columns view.
In the Available Columns list, expand the Button column. Select the Delete option. Click the ">" button to add it to the Selected Columns list. Click OK to return to Designer view.
Select the DataGrid, and click the Events tab in the Properties window (the lightning bolt). Double-click the DeleteCommand to create the datagrid_DeleteCommand event handler.

Insert the following code into the event handler:

private void datagrid_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
    int ID = (int) datagrid.DataKeys[(int) e.Item.ItemIndex];

    string sql = "DELETE FROM PetTable WHERE ID=" + ID;
    ExecuteNonQuery(sql);
    ReadRecords();
}

Press F5 to launch the Web application.
Click the Delete button to the right of the last row. The row is deleted from the database.

References

For more information, see the following topics:

Walkthrough: Editing an Access Database with ADO.NET

Contents

Introduction

Requirements

Walkthrough

References

Additional resources