Edit Access Database via ADO.NET
Release Date: 8/8/2003
| Update Date: 6/7/2004
Martin Tracvisual Studio Teammicrosoft Corporation
Suitable for: Microsoft® ADO.NETMICROSOFT® ASP.NETMICROSOFT® Visual C # ® .NetMiSoft® Visual Studio® .NET
Summary: This exercise illustrates how to use a simple Visual C # ® code web application to add, delete, and edit records in the Microsoft® Access database via ADO.NET.
Download Sample Code AdonetAtaCcessDb.exe. (130KB)
This page
Introduction Requirements Reference
Introduction
The Jet database engine is provided by Microsoft® Internet Information Services (IIS) for Microsoft Access Database. This database engine typically replaces Microsoft SQL Server and is very useful for low-capacity web applications. The Jet database engine is OLEDB-compatible, Microsoft .NET Framework System.Data.OleDB namespaces support this engine.
In this walkthrough, you will use the main objects for OLEDBCONNECTION, OLEDBCOMMAND, and OLEDBDataReader objects, and the DataGrid server control.
Figure 1 ADO.NET Data Access Pipeline
OLEDBConnection object controls the connection to the Jet database engine. The OLEDBCommand contains a Microsoft SQL statement that is informing the database engine. OLEDBDataReader is a quick, read-only, forward database cursor, which can read records selected by the SQL statement. DataGrid can display records and trigger events when adding, deleting, or editing recording. These events can create a new SQL statement for changing the database and display the result record.
Back to top
Claim
Before you start, you must install the following software in your computer:
• Windows® XP (SP1) and All Patches • Microsoft Internet Information Services (IIS) • Microsoft Access 2002 • Visual Studio .NET
Before trying the drill, make sure you can create a simple Visual C # ASP.NET web application.
Back to top
drill
In this walkthrough, you will create a database using Microsoft Access and create, add, edit, and delete records using ADO.NET.
Create a database
• Open Microsoft Access, create an empty database called Pets.mdb in the new folder C: / PETs. • Create a new table in the Designer view. • Add a automatic number field named ID and two text fields called 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 table as PETTABLE. • Add a number of items to the table. Figure 3 Add Test Data • Save Table and Turn off Access.
Display database record
The OLEDBConnection object contains a connection string that connects the Jet database engine to the PETS.md database. When constructing a connection string, a Pets.mdb file location relative to the application root directory will be provided. The relative path is changed to the physical path via the server.mappath method. This makes the web application can still be used for Jet database engines while publishing a web application to a remote server.
The OLEDBCOMMAND object contains SQL statement select * from PETTABLE, which can select all records in PETTABLE. OLEDBCOMMAND.EXECUTEREADER methods can create an OLEDBDataReader object to read these records. DataGrid is connected to the data reader via its DataGrid.DataSource property. When performing a DataGrid.Database, database records are moved from the database to the DataGrid, and a record is displayed per line.
• Open Visual Studio .NET. • Create a new Visual C # ASP.NET web application at http: // localhost / pets. • Rename the file Webform1.aspx as PETForm.aspx. • In the Solution Explorer (Solution Explorer), right-click the PETS project root directory and select Add / New Folder. Name the folder PETS. Keep the folder in the selected state. • Right-click the PETS project and add the existing item C: /PETS/PETS.MDB to the PETS folder. You must select All Files in the Files Of Type drop-down list to see the Pets.mdb file in the Existing Item window. • In the Toolbox, drag the DataGrid to the web form and rename it to DataGrid. • Switch to the code view by double-click the form. • Add this row to using statement using system.data.Data.OLDB at WebForm1.aspx.cs; • Insert this code into page_load method: Private void Page_load (Object Sender, System.EventArgs E)
{
IF (! ispostback) ReadRecords ();
}
• Add ReadRecords method to PETFORM classes followed immediately after 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"));
Cn.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 the F5 key to start the web application in the debugger. The content of the database should be displayed in the browser. Figure 4 Database displayed in the browser • Close the application and close Visual Studio. Note: Comments containing the CATCH statement in the above code. By default, Visual Studio will create a C # web application project, and its generate debugging information 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 an error detail page that shows the source line that has occurred and stack tracking and other error messages.
After debugging the project, you can set the generate debugging information to false and no longer build Pets.pdb. At this point, you can delete the annotation of the CATCH statement and replace it with your own error handler.
Configuring a database
By default, the ASP.NET users do not write to the database or to create a lock file (.ldb) permission to create a lock file (.ldb) in a folder containing the database. You must grant these privileges for ASP.NET users. Use one of the following three ways to grant permission:
• Add ASP.NET users to the administrators group. • Enable simulation for applications in the web.config file. • Add ASP.NET write permissions to database files and folders that contain this database.
In this drill, you will grant write permissions using the third (most secure) method.
• In File Explorer (File Explorer), find the newly built PETS folder, which is usually located in C: / INETPUB / WWWROOT / PETS / PETS. • Right-click on the PETS folder and select Properties. • Select the Security tab and click the Add button. • Add object name
For more information on ASP.NET security issues, see Authentication In ASP.NET: .NET Security Guidance (English).
Create a DataGrid Editorial Column
You can add columns to DataGrid using the Visual Studio property generator. The property generator contains a format option to add color and styles to DataGrid.
• Select DataGrid in the Visual Studio's Designer (Designer) view. If the Property window is not open, open it from the View menu. • Two links in the Properties window: Auto Format and Property Builder. Select Property Builder. • Select a columns view. • Uncheck CREATE COLUMNS Automatically At Run Time (automatically created a column when running). • Expand the Button column in the Available Column list. Select Edit, Update, Cancel option. Click the ">" button to add it to the Selected Column list. • In the Available Column list, select Bound Column. Click the ">" button to add it to the Selected Column list. Set the header text to "Pet Name" and set the DATA field to "PetName". • Repeat the previous step to add additional binding columns, set the header text to "Pet Type" and set the DATA field to "PETTYPE". • Click OK to return to the Designer view. DataGrid will reflect the changes made. • Select Auto Format at the bottom of the Properties window. • Select a format, such as "Colorful 1". • Click OK to return to the Designer view. DataGrid will reflect the changes made. Figure 5 adds a DataGrid of editing links • Press the F5 key to start the web application in the debugger. The content of the database should be displayed in the browser. Figure 6 Editing links and data editing DataGrid in the browser
DataGrid.editItemIndex properties can be selected to edit. When you select a row, you will display the text box 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, which can select the row that contains the link to edit. You should also connect the Cancel link (not yet visible) to an event handler, which can restore the DataGrid line without changing the record.
• Select DataGrid in the Visual Studio's Designer and click the Events tab in the Properties window (Lightning graph). Double-click CancelCommand to create a DataGrid_cancelcommand event handler. Return to the Events tab and double-click EditCommand to create a DataGrid_EditCommand event handler. • Insert the following code into both event handles: private void dataGrid_cancelcommand (Object Source, _System.Web.ui.WebControls.DataGridCommandEventEventEventEventArgs E) {
DataGrid.editItemIndex = -1;
ReadRecords ();
}
Private void DataGrid_EditCommand (Object Source, _System.Web.ui.WebControls.DataGridCommandeventArgs E)
{
DataGrid.editItemIndex = E.Item.ItemIndex;
ReadRecords ();
}
• Press the F5 key to launch the web application. • Click the EDIT link on the left of the second line. DataGrid will display a row and display the value of the PetName and PETTYPE fields in the text box. The Edit link will change to Update and Cancel link. Figure 7 Edit data in your browser (click Edit [Edit] link) • Click Cancel to return to the default DataGrid display.
Update DataGrid
When you select a row, you will display the Update and Cancel link. After entering the new value for the fields in the database record, you must transfer these changes back to the database.
You must connect the Update link to an event handler, which can read each text box and update the fields in the corresponding record. DataGrid.DataKeyField uses the primary key field of the database table to associate each line with its corresponding record. You can generate SQL statements to update records using new field values, then call OLEDBCommand.executenonQuery to perform updates.
• Select DataGrid in the Visual Studio's Designer (Designer) view. In the Properties window, set the DATAKEYFIELD attribute to ID. • Click the Events tab in the Properties view (Lightning Graphics). Double-click UpdateCommand to create a 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]).
String Type = ((TextBox) E.Item.cells [2] .controls [0]).
String SQL =
"Update Pettable Set PetName = /" " Name
"/", PETTYPE = / "" type "/" "
"Where id =" id;
ExecutenonQuery (SQL);
DataGrid.editItemIndex = -1;
ReadRecords ();
}
• Add an ExecuteonQuery method to 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"));
Cn.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 the F5 key to launch the web application. • Click the EDIT link on the left of the second line. • Change the text in the PETTYPE text box to "Dog". • Click the Update link to see the updated DataGrid.
Add records to DataGrid
DataGrid does not support the "Add" column, but you can use the button to click the event handler to add a record to the database. You can create a SQL statement to add a record, then call OLEDBCommand.executeNonQuery to update the database.
• In Visual Studio's Designer (Designer) view, drag a button from the toolbar to the form and name it btnaddpet. Change its text to "Add Pet". Figure 8 Add the Add button to the DataGrid • Double-click the button and insert this code into 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 the F5 key to launch the web application. • Click the Add button. A new row will be added to the database. Figure 9 Add new line
Remove the line from DataGrid
You can use the properties generator to add a DELETE column to DataGrid. You must connect the DELETE link to the event handler that can be deleted in the database. You can use the DataGrid.DataKeyField property to be deleted. The row to be deleted is associated with its corresponding record. You can create a SQL statement to delete the record, then call OLEDBCommand.executeNonQuery to update the database. • Select DataGrid in the Visual Studio's Designer (Designer) view. • Select Property Builder at the bottom of the Properties window. • Select a columns view. • Expand the Button column in the Available Column list. Select the Delete option. Click the ">" button to add it to the Selected Column list. Click OK to return to the Designer view. • Select DataGrid and click the Events tab in the Properties window (Lightning graph). Double-click DeleteCommand to create a DataGrid_DeleteCommand event handler. • Insert the following code into the event handler: Private void DataGrid_DeleteCommand (Object Source, _ System.Web.ui.WebControls.DataGridCommandEventAndargs E)
{
INT ID = (int) DataGrid.DataKeys [(int) E.Item.ItemIndex];
SQL = "delete from pettable where id =" ID;
ExecutenonQuery (SQL);
ReadRecords ();
}
• Press the F5 key to launch the web application. • Click the DELETE button on the right side of the last row. This line will be removed from the database.
