1. Displaying Access (.mdb) Database Data

Main Points

• Populating A GridView Here we use data from an Access database to fill a GridView control.
• ASP.NET Database Code Here's the server side code that calls the database and displays the information.
• GridView Result Code This is the HTML that the server side code generates.
• ^{Skip to Main Points} links return to the Main Points menu.

In implementing a master page based template across the site, this testing page or its dependencies may have been moved. Therefore, some of the references have been changed possibly including master page friendly techniques like application root relative references. They are not original, though the app root reference in the AccessDataSource tag is original. I thought it wasn't working for a second, until I realized I'd rebuilt the database for a different purpose and it no longer had any roles to pull in for this page. Once I added them back into the DB, this worked beautifully with the original code.

[chroniclemaster1, 2008/11/08]

Here we are demonstrating our first basic usage of database data. We're using an Access database to store the information. While MS SQL server is more easily and commonly used with .NET web applications, only MySQL and Access are supported by our webhost. ...Without shelling out a lot more money anyway. Since MySQL does not interface very conveniently with Microsoft technologies and with MS SQL out of the running, that left only one choice. Ummm... hurray, we're ahhhh... working with MS Access.

Populating A GridView

An Access database is file, a special kind of file that stores information in the most retrievable form possible, well, at least in theory. There are technologies like .zip files and other types of archive files that compress more data into a smaller file, but a database only compacts information in ways that still allow fast access. The database does nothing else. It serves as a kind of library or archive for information, literally a base for all your data.

When a database is plugged into an ASP.NET application, it becomes the hub of a powerful tool. The application code tells the server what to do, the database provides the server with potentially vast quantities of information with which to work. The combination produces some of the most powerful web applications running today. Database applications can handle projects as simple as storing which Girl Scout is bringing what food to a potluck, or as complicated as providing real time updates for a world wide distribution system that tracks shipments and generates work orders based on complicated sets of rules. This... This is pretty basic...

ASP.NET Database Code

This web page uses an AccessDataSource control to grab information from the Access database. Then a GridView control grabs information from the data source control and displays it in a table. There isn't much work that we have to do to create the table. These are classes pre-built in the libraries included in the .NET framework. So despite the extensive amount of work going on behind the scenes, the only code that is necessary is this...

ASP.NET takes care of all other aspects. It locates and contacts the database, needing only the DataFile attribute to tell it where the Access database is located. It requests the data, creates usable variables - a DataReader or DataSet - in order to use the information and then fills the AccessDataSource control with that information. The GridView can then identify its data source with the DataSourceID attribute, talk to the AccessDataSource, and get the information that it needs to display. Note how even the column headings are populated from the database automatically, they are not present in the web page code. While you can change this behavior, it is the default behavior for the GridView control when linked to a data source.

The database as you'll notice is located in a special .NET subfolder, App_Data. One of the advances in .NET compared to older server side technologies is that it provides certain folders like App_Data which are locked to external access. Try typing in the proper path name to the database, or easier use this link to the database. The server is hardwired to prohibit this contact and throws a Forbidden error. I have built no security, specified no settings or roles, .NET is just built this way. In fact, all database files wherever they are have nearly the same protection, but ANY file in App_Data folder is forbidden. App_Data is only available to other parts of the web application. So this webpage can request, "Please, may I have data from the database." and the data will be sent over to complete the page, but the data is locked away from users. They have only as much access to the data as you provide them with when you build the webpage.

GridView Result Code

Here is the HTML that is generated by the server side code...

First, note that the GridView is rendered as a table. In fact, note that the id attribute for the table is identical to the id we applied to the GridView control, id="gvRolesData". And as far as code goes... even for ASP.NET, this is pretty clean code. The </tr><tr> tags unite the closing tag from one table row with the opening tag of the next table row which is a bit odd. However, there is a semi-serious problem in this code.

Now this table is not being used to organize web elements, but data that is very reasonably organized into a table. Using a table here is OK from a design stand point. It's being used for what tables should be used for, unfortunately it loses a little in the execution. The purpose of moving away from tables for layout was to take advantage of CSS which was more flexible and more powerful. Take a look at the attributes that are present in this <table> tag: cellspacing, border, border-collapse. These are all setting inline styles for the tables appearance. So much for full CSS control. Take a look at the GridView code and note that we specified nothing about these items. .NET simply included them by default, and we would much rather have controlled these completely from the style sheet. As it stands, any conflicting CSS should be overridden by the inline styles which trump everything else in the CSS cascade. We are therefore most likely stuck with this styling.