Walkthrough: Editing an Access Database with ADO.NET
Martin Tracvisual Studio Teammicrosoft 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 AdonetAtaCcessDb.exe. (130KB)
Contents
IntroductionRequirementsWalkthroughRepreferences
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.
................ ..
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. 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 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
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 autonue Field named and two..................
Figure 2 Designing The Data Table in Access
4. Switch to DataSheet View and Save The Table as Pettable.
5. Add a few entries to the table.
Figure 3 Add 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 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 .
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 DataGrid.
7. Switch to code view by double-clicking the flm.
8. Add this line to the using statements at the beginning of webform1.aspx.cs
9. using system.data.oledb;
10. Insert this code inTo the page_load method:
11. Private Void Page_Load (Object Sender, System.EventArgs E)
12. {
13. if (! Ispostback) ReadRecords ();
14.}
15. Add The readRecords method to the Petform Class Just After Page_Load Method:
16. Private void readrecords () 17. {
18. OLEDBCONNECTION CONN = NULL;
19. OLEDBDataReader Reader = NULL;
20. TRY
twenty one. {
22. Conn = New OLEDBCONNECTION
23. "Provider = Microsoft.jet.Oledb.4.0;"
24. "Data Source =" Server.mappath ("Pets / Pets.mdb"));
25. CONN.OPEN ();
26.
27. OLEDBCOMMAND CMD =
28. New OLEDBCommand ("Select * from Pettable", Conn;
29. Reader = cmd.executeReader ();
30.
31. DataGrid.DataSource = Reader;
32. DataGrid.databind ();
33.}
34. // Catch (Exception E)
35. // {
36. // Response.write (E.MESSAGE);
37. // Response.end ();
38. //}
39. Finally
40. {
41. IF (Reader! = Null) Reader.Close ();
42. IF (conn! = Null) conn.close ();
43.}
44.}
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 Seeen in The Browser
46. Close The Application, And The 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 your ignal 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.
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
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". 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 proteth 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.com 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 Column Automatically At Run Time."
5. In The Available Column 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 Column List, SELECT Bound Column. Click The ">" Button to Add. Give It The Header Text "Pet Name" and the data field "petname".
7. REPEAT THETS 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:
3. Private void DataGrid_cancelcommand (Object Source, System.Web.ui.WebControls.DataGridCommandEventArgs E) 4. {
5. DataGrid.editItemIndex = -1;
6. ReadRecords ();
7.}
8. Private void DataGrid_EDitCommand (Object Source, System.Web.ui.WebControls.DataGridCommandeventArgs E)
9. {
10. DataGrid.editItemIndex = E.Item.itemindex;
11. ReadRecords ();
12.}
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 Pettype in Textboxes. The Edit Link Changes To The Update and Cancel Links.
Figure 7 Editing Data in Browser, Having Clicked The Edit Link
15. Click Cancel to return to the default datagrid display.
To Update the DataGrid
WHEN a row is self editing, the update and cancel links Appear. Once You Have Used The textboxes in a Database Record, You Must Move Sse 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:
4. Private void DataGrid_UpdateCommand (Object Source,
5. System.Web.ui.WebControls.DataGridCommandEventArgs E)
6. {
7. INT ID = (int) DataGrid.DataKeys [(int) E.Item.ItemIndex];
8.
9. String name = ((TextBox) E.Item.cells [1] .controls [0]).
10. String Type = ((TextBox) E.Item.cells [2] .controls [0]).
11.
12. String SQL =
13. "Update Pettable Set PetName = /" " Name
14. "/", PETTYPE = / "" Type "/" "
15. "where id =" ID;
16. ExecutenonQuery (SQL);
17.
18. DataGrid.editItemIndex = -1;
19. ReadRecords ();
20.}
21. Add The ExecutenonQuery Method to The Petform Class:
22. Private void ExecutenonQuery (String SQL)
twenty three. {
24. OLEDBCONNECTION CONN = NULL;
25. Try
26. {
27. conn = new OLEDBCONNECTION
28. "Provider = microsoft.jet.OleDb.4.0;" 29. "Data Source =" Server.mappath ("Pets / Pets.mdb"));
30. conn.open ();
31.
32. OLEDBCommand cmd =
33. New OLEDBCommand (SQL, CONN);
34. cmd.executenonquery ();
35.}
36. // Catch (Exception E)
37. // {
38. // Response.write (E.MESSAGE);
39. // Response.end ();
40. //}
41. Finally
42. {
43. IF (conn! = Null) conn.close ();
44.}
45.}
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 does not 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 BtnaddPet. Change Its Text To "Add Pet."
Figure 8 Adding an address button to the datagrid
2. Double-Click The Button and INSERT this code in the btnaddpet_click method:
3. Private void btnaddpet_click (object sender, system.eventargs e) 4. {
5. String SQL = "INSERT INTO PETTABLE (PETNAME, PETTYPE)"
6. "VALUES (/" new / ", /" new / ")";
7. ExecutenonQuery (SQL);
8. ReadRecords ();
9. }
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 Add 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, The 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 Column List, Expand The Button Column. Select The Delete Option. Click 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:
7. Private void DataGrid_DeleteCommand (Object Source, System.Web.ui.WebControls.DataGridCommandEventArgs E) 8. {
9. INT ID = (int) DataGrid.DataKeys [(int) E.Item.ItemIndex];
10.
11. String SQL = "delete from pettable where id =" id;
12. ExecutenonQuery (SQL);
13. ReadRecords ();
14.}
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.
References
For More Information, See the Following Topics:
Using ADO.NET
· ASP.NET QuickStart Tutorial
· In-Depth: UNLOCKING Microsoft Access Data with ADO
