Recipe 17.3 Retrieve Access Data from an ASP.NET Application

17.3.1 Problem

Your ASP.NET web site needs to access data from one of your Access databases. How do you retrieve Access data using ADO.NET?

17.3.2 Solution

Follow these steps to create an ASP.NET page, AltRock.aspx, which displays a list of alternative rock albums from the 17-03.MDB database using a DataGrid control:

Start Visual Studio .NET.
Create a new Visual Basic .NET ASP.NET Web Application project.
Under location, enter "http://localhost/Music" and click OK.
Delete the initial WebForm1.aspx file from the project.
Select Project Add Web Form... to add a new web form page to the project named AltRock.aspx.
With the Web Forms toolbox tab visible, drag a DataGrid control to the page.
Using the Property sheet, change the ID of the new DataGrid control to dgrAltRock.
Right-click on the DataGrid control and select Auto Format... from the popup menu. Select a format of your liking and click OK.
Select View Code to jump to the code editor.
Add the following code to the very top of the page (above the Class statement) to import the System.Data.OleDb namespace:
```
Imports System.Data.OleDb
```

Add the following code to the Page_Load event handler to establish a connection to the 17-03.MDB database:

        ' You will need to edit the Data Source value to correspond
        ' to the location of the 17-03.mdb database on your system.
        Dim cnx As OleDbConnection = _
         New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=D:\Books\AccessCookBook2003\ch17\17-03.mdb")
        cnx.Open( )

As noted by the comment in the code you will need to edit the path to the 17-03.MDB database to match where the database is located on your system.

Add the following code to retrieve the rows returned by the database's qryAlternativeAlbums query as a OleDbDataReader:

        ' Constuct a OleDbCommand to execute the query
        Dim cmdAltRock As OleDbCommand = _
         New OleDbCommand("qryAlternativeAlbums", cnx)

        ' Odd as it may seem, you need to set the CommandType
        ' to CommandType.StoredProcedure.
        cmdAltRock.CommandType = CommandType.StoredProcedure

        ' Run the query and place the rows in an OleDbDataReader.
        Dim drAltRock As OleDbDataReader
        drAltRock = cmdAltRock.ExecuteReader

Add the following code to bind the drAltRock OleDbDataReader to the dgrAltRock DataGrid control on the page:

        ' Bind the OleDbDataReader to the DataGrid
        dgrAltRock.DataSource = drAltRock
        dgrAltRock.DataBind( )

Save the page and preview it in your browser by right-clicking on the file name (AltRock.aspx) in the Solution Explorer window and selecting View in Browser from the popup menu. The resulting page should look similar to the one shown in Figure 17-5.

Figure 17-5. The data behind this DataGrid was retrieved from the 17-03.MDB database using the .NET OleDb provider

17.3.3 Discussion

Probably the trickiest part of retrieving data from an Access database using ADO.NET is in creating the connection string. The basic syntax of the connection string is as follows:

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=path_to_database"

If you are using a workgroup-secured database, you will need to add User Id and Password items to the connection string:

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=path_to_database;" & _
        "User Id=user_name;Password=password;"

If the database is password-protected, you will need to use the following connection string:

        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=path_to_database;" & _
        "Jet OLEDB:Database Password=database_password;"

If the database is stored in an Access 97 database, you should specify the Jet 3.51 provider (Microsoft.Jet.OLEDB.3.51) instead of the Jet 4.0 provider.

The steps for constructing a Windows Forms-based application that accesses an Access database are fairly similar.

This example binds the DataGrid to an OleDbDataReader object. You can also bind a DataGrid to a DataSet object. It's more efficient to use an OleDbDataReader; however, its usage is more limited. For example, if you wished to enable paging for the DataGrid, you would have to use a DataSet instead.

17.3.4 See Also

The following link provides a walkthrough for working with Access data from ADO.NET: Walkthrough: Editing an Access Database with ADO.NET (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/adon_wtaccessdb.asp).

Another helpful article on ADO.NET is Unlocking Microsoft Access Data with ADO.NET (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office12062001.asp).

The following article discusses how to create a pageable DataGrid using a DataSet: (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/aspnet-pageablesortable.asp).

If you're having trouble creating ADO.NET connection strings, check out Able Consulting's Connection Strings page (http://www.able-consulting.com/ADO_Conn.htm).