Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Start Visual Studio .NET.

2. Create a new Visual Basic .NET ASP.NET Web Application project.

3. Under location, enter
   "http://localhost/Music" and click
   OK.

4. Delete the initial WebForm1.aspx file from the project.

5. Select Project Add Web Form... to add a new web form page
   to the project named AltRock.aspx.

6. With the Web Forms toolbox tab visible, drag a DataGrid control to
   the page.

7. Using the Property sheet, change the ID of the new DataGrid control
   to dgrAltRock.

8. Right-click on the DataGrid control and select Auto Format... from
   the popup menu. Select a format of your liking and click OK.

9. Select View Code to jump to the code editor.

10. 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

11. 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.

12. 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

13. 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( )

14. 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).