Use ADO. Net to manipulate the ACCESS database

Document directory

• Create a database
• Show database records
• Configure Database
• Create a DataGrid edit Column
• Edit DataGrid
• Update DataGrid
• Add record to DataGrid
• Delete row from DataGrid

Released on: 8/8/2003 | updated on: 6/7/2004

Martin Tracy
Visual Studio team
Microsoft Corporation

Applicable to: Microsoft ADO. net
Microsoft ASP. NET
Microsoft Visual C #. net
Microsoft Visual Studio. NET

Summary: This walkthrough shows how to use a simple Visual C # embedded code web application to add, delete, and edit records in the Microsoft Access database through ADO. net.

Download the sample code adonetaccessdb.exe ). (130kb)

Content on this page

	Introduction
	Requirements
	Drill
	Reference

Introduction

The Jet Database Engine is provided by Microsoft Internet Information Service (IIS) for Microsoft Access databases. This database engine can usually replace Microsoft SQL Server and is useful for low-capacity web applications. The Jet Database Engine is oledb compatible. classes in the Microsoft. NET Framework system. Data. oledb namespace support this engine.

The main objects you will use in this walkthrough are oledbconnection, oledbcommand, oledbdatareader, and DataGrid server controls.

Figure1 ADO. netData Access Pipeline

The connection between oledbconnection object control and the Jet Database Engine. Oledbcommand contains Microsoft SQL statements that tell the database engine what operations to take. Oledbdatareader is a fast, read-only, forward-only database cursor that can read records selected by SQL statements. The DataGrid displays records and triggers events when adding, deleting, or editing records. These events can be used to create new SQL statements used to change the database and display Result Records.

Back to Top

Requirements

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

•	Windows XP (SP1) and all patches
•	Microsoft Internet Information Service (IIS)
•	Microsoft Access 2002
•	Visual Studio. NET

Before you try the drill, make sure that you can create a simple Visual C # ASP. NET web application.

Back to Top

Drill

In this walkthrough, you will use Microsoft Access to create a database and use ADO. Net to create, add, edit, and delete records.

Create a database

•	Open Microsoft Access and create an empty database named pets. mdb in the new folder c:/pets.
•	Create a new table in the designer view.
•	Add an automatic Id field and two text fields named petname and pettype. Set the ID as the primary key and accept all default settings. Figure 2 design a data table in access
•	Switch to the data table view and save the tablePettable.
•	Add several items to the table. Figure 3 add Test Data
•	Save the table and disableAccess.

Show database records

The oledbconnection object contains the connection string connecting the JET database engine to the pets. md database. When constructing a connection string, the location of the pets. MDB File relative to the application root directory is provided. The relative path is changed to the physical path through the server. mappath method. This allows the web application to be used by the Jet Database Engine while publishing the web application to a remote server.

The oledbcommand object contains the SQL statement select * From pettable, which can select all records in pettable. The oledbcommand. executereader method can create an oledbdatareader object to read these records. The DataGrid connects to the data reader through its DataGrid. datasource attribute.

When the DataGrid. databind method is executed, the database records are moved from the database to the DataGrid, and one record is displayed in each row.

•	Open Visual Studio. NET.
•	Create a New Visual C # ASP. NET web application in http: // localhost/pets.
•	Rename the file webform1.aspx to petform. aspx.
•	In Solution Explorer, right-click the pets project root directory and select Add/new folder (Add/new folder ). Name the folder pets. To keep the selected folder.
•	Right-click the pets project and add the existing item C:/pets. mdb to the pets folder. You must select all files from the files of Type drop-down list to view the pets. MDB file in the existing item browser window.
•	In the toolbox, drag the DataGrid to the web form and rename it as the DataGrid.
•	Double-click the form to switch to the Code view.
•	Add this row to the using statement using system. Data. oledb at the beginning of webform1.aspx. CS;
•	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 following 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 start the web application in the debugger. The database content should be displayed in the browser. Figure 4 databases displayed in the browser
•	Close the application, and then closeVisual Studio.

Note:: The above Code contains the comments of the catch statement. By default, Visual Studio creates a C # web application project, and its generate debugging information attribute is set to true. This will create a pets. PDB file in the bin directory. The default ASP. NET error handler will use this information to create the error details page, which displays the source line of the error, stack trace, and other error information.

After debugging the project, you can set generate debugging information to false without building pets. PDB. In this case, you can delete the comment of the catch statement and replace it with your own error handler.

Configure Database

By default, ASP. NET users do not have the permission to write records to the database or create a locked file (. LDB) in the folder containing the database. You must grant these permissions to ASP. NET users. You can use either of the following methods to grant permissions:

•	Add ASP. NET users to the Administrator group.
•	Enable Simulation for applications in the web. config file.
•	Add ASP. NET write permissions to database files and folders containing the database.

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

•	In File explorer, find the new pets folder, which is usually located in C:/inetpub/wwwroot/pets.
•	Right-click the pets folder and select Properties ).
•	Select the Security tab and click Add.
•	Add the object name <yourmachine>/ASPnet, where <yourmachine> is your computer name. Click OK to return to the Security tab.
•	Select an ASP. NET account and add the write permission. This account is named aspnet_wp account, ASP. NET machine account, or similar name.
•	In File explorer, right-click the file pets. MDB and select Properties ).
•	Select the Security tab and click the Advanced button.
•	Select inherit from parent the permissions entries that apply to child objects (inherited from the parent permission project that can be applied to sub-objects ). Click OK to accept the change.

For more information about ASP. NET security, see authentication in ASP. NET:. Net security guidance ).

Create a DataGrid edit Column

You can use the Visual Studio property builder to add columns to the DataGrid. The property generator contains format options. You can add colors and styles to the DataGrid.

•	In the designer view of Visual Studio, select the DataGrid. If the property window is not opened, open it from the View menu.
•	At the bottom of the Properties window, two links are displayed: auto format and property builder ). Select Property builder ).
•	Select columns (column) view.
•	Deselect create columns automatically at run time (automatically create columns during runtime ).
•	In the available columns list, expand the button column. Select edit, update, and cancel options. Click ">" to add it to the selected columns list.
•	In the available columns list, select bound column (Bind Column ). Click ">" to add it to the selected columns list. Set the header text to "pet name" and the data field to "petname ".
•	Repeat the previous step to add other binding columns, set the header text to "Pet type", and set the data field to "pettype ".
•	Click OK to return to the designer view. The DataGrid reflects the changes.
•	At the bottom of the Properties window, select Auto format ).
•	Select a format, such as "colorful 1 ".
•	Click OK to return to the designer view. The DataGrid reflects the changes. Figure 5 DataGrid with the editing link added
•	Press F5 to start the web application in the debugger. The database content should be displayed in the browser. Figure 6 editing links and data in the browser

Edit DataGrid

You can select a row to edit the DataGrid. edititemindex attribute. When a row is selected for editing, a text box is displayed in each cell. The text in each text box is set to the value of the corresponding field in the data record.

You must connect the edit link to an event handler. The event handler can select a row that contains the link to be edited. You should also connect the cancel (UNDO) Link (not visible) to an event handler, which can restore the DataGrid row without changing the corresponding records.

•	Select the DataGrid In the designer view of Visual Studio and click Properties) In the window (lightning graphics), click the events tab. Double-click cancelcommand to create 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 start the web application.
•	Click the edit link on the left of the second line. The DataGrid displays a row and the values of the petname and pettype fields in the text box. The Edit link is changed to the update and cancel links. Figure 7 edit data in the browser (You have clicked the edit [edit] link)
•	ClickCancel(Canceled) return to the defaultDataGridDisplay.

Update DataGrid

When you select a row for editing, the update (update) and cancel (cancel) links are displayed. After you enter a new value for the fields in the database record using the text box, you must move these changes back to the database.

You must connect the update link to an event handler, which can read each text box and update fields in the corresponding record. DataGrid. datakeyfield uses the primary key field of the database table to associate each row with its corresponding records. You can generate an SQL statement to update the record that uses the new field value, and then call oledbcommand. executenonquery to execute the update.

•	In the designer view of Visual Studio, select the DataGrid. In the Properties window, set the datakeyfield attribute to ID.
•	Click the events tab in properties view (lightning graphics. 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 start the web application.
•	Click the edit link on the left of the second line.
•	Change the text in the pettype text box from "dog" to "dog ".
•	Click the update link to view the updated DataGrid.

Add record to DataGrid

The DataGrid does not support "add" columns, but you can click the event handler to add records to the database. You can create an SQL statement to add records, and then call oledbcommand. executenonquery to update the database.

•	In the designer view of Visual Studio, drag a button from the toolbar to the form and name it btnaddpet. Change the text to "add Pet ". Figure 8 Add a button to the DataGrid
•	Double-click this button and insert this code into 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 start the web application.
•	Click Add. A new row will be added to the database. Figure 9 New Lines added

Delete row from DataGrid

You can use the property generator to add a delete column to the DataGrid. You must connect the delete link to the event handler that can delete the corresponding records in the database. You can use the DataGrid. datakeyfield attribute to associate the row to be deleted with the corresponding record. You can create an SQL statement to delete records, and then call oledbcommand. executenonquery to update the database.

•	In the designer view of Visual Studio, select the DataGrid.
•	At the bottom of the Properties window, Select Property builder ).
•	Select columns (column) view.
•	In the available columns list, expand the button column. Select the delete option. Click ">" to add it to the selected columns list. Click OK to return Designer view.
•	Select the DataGrid, and click the events tab in the Properties window (lightning graphics. Double-click 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]; sql = "DELETE FROM PetTable WHERE ID=" + ID; ExecuteNonQuery(sql); ReadRecords();}
•	Press F5 to start the web application.
•	Click Delete on the right of the last row. This row will be deleted from the database.