Walkthrough: Editing an Access Database with ADO.NET
Visual Studio Team
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)
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.
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.
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
1. Open Microsoft Access and create a blank database named Pets.mdb in the new folder C:/Pets.
2. Create a new table in Designer view.
3. Add an AutoNumber field named ID and two Text fields named
Figure 2 Designing the data table in Access
4. Switch to datasheet view and save the table as
5. Add a few entries to the table.
Figure 3 Adding test data
6. 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
When the DataGrid.DataBind method executes, database records are moved from the database to the DataGrid, which displays them one record per row.
1. Open Visual Studio .NET.
2. Create a new Visual C# ASP.NET Web application at http://localhost/Pets.
3. Rename the file WebForm1.aspx to PetForm.aspx.
4. From the Solution Explorer, right-click the Pets project root and select Add/New Folder. Name the folder Pets. Leave the folder selected.
5. 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.
6. From the toolbox, drag a DataGrid to the Web form, renaming it to
7. Switch to code view by double-clicking the form.
8. Add this line to the
10. Insert this code into the Page_Load method:
15. Add the ReadRecords method to the PetForm class just after the Page_Load method:
45. 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
46. Close the application, and then close Visual Studio.
Once your project is debugged, you can set Generate Debugging Information to
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.
1. From the File Explorer, find the new Pets folder, normally located at C:/Inetpub/wwwroot/Pets/Pets.
2. Right-click the Pets folder, and select Properties.
3. Select the Security tab, and click the Add button.
4. Add the object name <YOURMACHINE>/ASPNET where <YOURMACHINE> is the name of your machine. Click OK to return to the Security tab.
5. Select the ASP.NET account, and add Write permission. This account is named aspnet_wp account, ASP.NET MACHINE Account, or something similar.
6. From the File Explorer, right-click the file Pets.mdb, and select Properties.
7. Select the Security tab, and click the Advanced button.
8. 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.
1. From Visual Studio Designer view, select the DataGrid. If you don't have the Property window open, open it from the View menu.
2. At the very bottom of the Properties window you will see two links: Auto Format and Property Builder. Select Property Builder.
3. Select Columns view.
4. Uncheck "Create columns automatically at run time."
5. 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.
6. 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."
7. Repeat the last step to add another Bound Column, giving it the Header text "Pet Type" and the Data Field "PetType."
8. Click OK to return to Designer view. The DataGrid will reflect the changes.
9. At the very bottom of the Properties window, select Auto Format.
10. Select a format, such as "Colorful 1."
11. Click OK to return to Designer view. The DataGrid will reflect the changes.
Figure 5 The DataGrid with the Edit links added
12. 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.
1. 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.
2. Insert the following code into the two event handlers:
13. Press F5 to launch the Web application.
14. 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
15. 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.
1. From Visual Studio Designer view, select the DataGrid. In the Properties window, set the DataKeyField property to ID.
2. Click the Events tab in the Properties view (the lightning bolt). Double-click UpdateCommand to create the datagrid_UpdateCommand event handler.
3. Insert the following code into the event handler:
21. Add the ExecuteNonQuery method to the PetForm class:
46. Press F5 to launch the Web application.
47. Click the Edit link to the left of the second row.
48. Change the text in the PetType textbox from "dog" to "dawg."
49. 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.
1. From Visual Studio Designer view, drag a button from the toolbar to the form and name it
Figure 8 Adding an Add button to the DataGrid
2. Double-click the button and insert this code in the btnAddPet_Click method:
10. Press F5 to launch the Web application.
11. 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.
1. From Visual Studio Designer view, select the DataGrid.
2. At the very bottom of the Properties window, select Property Builder.
3. Select Columns view.
4. 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.
5. 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.
6. Insert the following code into the event handler:
15. Press F5 to launch the Web application.
16. Click the Delete button to the right of the last row. The row is deleted from the database.
For more information, see the following topics: