Walkthrough: Creating a Web Page to Display Access Database Data
Using the Microsoft Visual Web Developer Web development tool, you can create Web pages that work with data from a variety of sources, including databases, XML files, and business objects. This walkthrough shows you how to work with data in a Microsoft Access database (.mdb file).
During this walkthrough, you will learn how to do the following:
Configure permissions for .mdb files.
Connect to the database that has an AccessDataSource control.
Display Access data.
Access databases do not have the same capacity and are not as scalable as other types of databases, such as Microsoft SQL Server. Generally, if you are creating a Web site that will support only light traffic or a limited number of users, an Access database is sufficient. However, if the Web site will support more throughput or a larger number of users, you should consider using SQL Server or another database that is suited for production Web sites.
In order to complete this walkthrough, you will need the following:
The Northwind.mdb file that contains the Access version of the sample Northwind database.
Alternatively, you can use another Access .mdb file and adjust the steps in the walkthrough to match the tables that you are using.
Microsoft Data Access Components (MDAC) version 2.7 or later.
If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you using Microsoft Windows 2000, you might to have to upgrade the MDAC version that is already installed on the computer. To download the current MDAC version, see the Data Access and Storage Developer Center.
Optionally, Microsoft Internet Information Services (IIS) installed locally on the computer.
This lets you test that the Web site has adequate user rights to use an Access database in a production environment.
Creating the Web Site and Page
If you have already created a Web site in Visual Web Developer by completing Walkthrough: Creating a Basic Web Page in Visual Web Developer, you can use that Web site and go to the next section. Otherwise, create a new Web site and page by following these steps.
To create a file system Web site
Open Visual Web Developer.
On the File menu, click New Web Site.
The New Web Site dialog box appears.
Under Visual Studio installed templates, click ASP.NET Web Site.
In the Web location list, select HTTP.
The Choose Location page appears.
Click Local IIS**,** and then click Default Web Site.
Click the create new Web application icon (), and then name the new Web application AccessSample.
The New Web Site dialog box appears with the location of your new Web application in the right-most Location box.
In the Language list, click the programming language that you prefer to work in.
The programming language that you choose will be the default for the Web site, but you can set the programming language for each page individually.
Visual Web Developer creates the folder and a new page named Default.aspx. The Web site is displayed in Solution Explorer.
Configuring Permissions for an Access Database
An important aspect of working with an Access .mdb file is to correctly configure permissions. When a Web application uses an Access database, the application must have Read permission to the .mdb file so the application can access the data. Additionally, the application must have Write permission to the folder that contains the .mdb file. Write permission is required because Access creates an additional file that has the extension .ldb in which it maintains information about database locks for concurrent users. The .ldb file is created at run time.
By default, ASP.NET Web applications run in the context of a local machine account named ASPNET (for Windows 2000 and Windows XP) or in the context of the account NETWORK SERVICE (for Windows Server 2003). For example, for Windows 2000 or Windows XP Professional, if the Web server is named ABCServer, ASP.NET applications on the ABCServer computer run in the context of the local account ABCServer\ASPNET. Therefore, to use an Access database in an ASP.NET Web application, you must configure the folder that contains the Access database to have both Read and Write permissions for the local ASPNET user account.
When you create a Web site in Visual Web Developer, Visual Web Developer creates a folder named App_Data below the current root folder. The folder is designed to be a store for application data, including Access databases. The App_Data folder is also used by ASP.NET to store databases that the system maintains, such as the database for membership and roles. When Visual Web Developer creates the App_Data folder, it grants Read and Write permissions for the folder to the ASPNET or NETWORK SERVICE user account.
As a security measure, files in the App_data folder are not served by the Web server. Do not store any Web pages in the App_Data folder, because users will see an error if they request a page from that folder.
In this part of the walkthrough, you will examine the permissions of the App_Data folder to make sure that it will work correctly when the application runs.
To set permissions in the App_Data folder
In Windows Explorer, move to the root folder for the Web site.
The default location for your new Web site is c:\inetpub\wwwroot\AccessSample.
If the App_Data folder does not already exist, create one.
By default, Visual Web Developer creates the folder when you create a new Web site.
Right-click the App_Data folder, click Properties, and then click the Security tab.
Under Group or user names, look for either of these user accounts:
If the computer is running Windows XP Professional or Windows 2000, look for computer\ASPNET.
If the computer is running Windows Server 2003, look for NETWORK SERVICE.
Verify that the account has Read and Write permissions for the App_Data folder.
If the account has the appropriate permissions, skip the next three steps.
If the account does not have the correct permissions, click Add, and then do the following:
For Windows 2000 or Windows XP, type computer\ASPNET, and then click Check Names.
If the name does not resolve, verify that you are using the correct computer name.
For Windows Server 2003, check to determine whether the account NETWORK SERVICE is already listed. If it is, continue to the next step. Otherwise, in the Enter the object names to select box, type NETWORK SERVICE, and then click Check Names.
Confirm that the account name is selected, and then under Allow, select the Read and Write check boxes.
Using Access Data on an ASP.NET Web Page
You can now use your Access database in a Web page. You will use an AccessDataSource control.
To add an AccessDataSource control to the page
In Visual Web Developer, in Solution Explorer, right-click the App_Data folder, and then click Add Existing Item.
Locate the Northwind.mdb file (or other Access .mdb file) that you want to use for this walkthrough.
In the App_Data folder, click the .mdb file, and then click Add.
The .mdb file is added to your application.
Open the Default.aspx page and switch to Design view.
From the Data group in the Toolbox, drag an AccessDataSource control onto the page.
- If the Access Data Source Tasks shortcut menu does not appear, right-click the AccessDataSource control, and then click Show Smart Tag.
On the Access Data Source Tasks shortcut menu, click Configure Data Source.
The Configure Data Source - <DataSourceName> wizard appears.
On the Choose a database page, in the Microsoft Access Data file box, type ~/App_Data/Northwind.mdb.
- Alternatively, click Browse, and then in the Select Microsoft Access Database dialog box, move to the Northwind.mdb file in the App_Data folder.
The Configure Select Statement page appears.
Click Specify columns from a table or view.
In the Name list, click Categories.
Select the CategoryID, CategoryName, and Description check boxes.
The Test Query page appears.
- Optionally, click Test Query to test your query.
From the Data group in the Toolbox, drag a GridView control onto the page.
- If the GridView Tasks shortcut menu does not appear, right-click the GridView control, and then click Show Smart Tag.
On the GridView Tasks menu, in the Choose Data Source box, click AccessDataSource1.
Testing the Page
You can now run the page.
To test the page
Press CTRL+F5 to run the page.
The GridView control displays all data rows from the Categories table.
This walkthrough describes the basic steps that are required to work with Access data in an ASP.NET Web page. The ASP.NET data binding model lets you work with data from different sources in the same way. For example, you can do the following:
Use controls to filter the data that the page displays.
For detailed information, see Walkthrough: Basic Data Access in Web Pages.
Update, insert, or delete data from an Access database.
For detailed information, see Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control.
Create pages to display Access data from tables that have a master-detail relationship.
For detailed information, see Walkthrough: Creating Master/Detail Web Pages in Visual Studio.