AccessDataSource Control in ASP.NET 3.5

In this article, I will describe how to use AccessDataSource control in ASP.NET 3.5


Designed to work with Microsoft Access. It uses OleDb data provider internally. OleDb data provider classes are defined in the System.Data.OleDb namespace. The AccessDataSource class is a data source control that works with Microsoft Access databases. Like its base class, SqlDataSource, the AccessDataSource control uses SQL queries to perform data retrieval.

One of the unique characteristics of the AccessDataSource control is that you do not set the ConnectionString property. All you need to do is set the location of the Access (.mdb) file in the DataFile property and the AccessDataSource takes care of the underlying connection to the database. You should place Access databases in the App_Data directory of the Web site and reference them by a relative path (for example, ~/App_Data/Test.mdb). This location offers additional security for data files, because they are not served if they are requested directly by the client Web browser. You bind data-bound controls to an AccessDataSource using the DataSourceID property of the data-bound control.

The following are the features of the AccessDataSource control.

  • Sorting - Set the DataSourceMode property to the DataSet value.
  • Filtering - Set the FilterExpression property to a filtering expression used to filter the data when the Select method is called.
  • Paging - The AccessDataSource does not support direct paging operations on an Access database. A data-bound control, such as the GridView, can page over the items returned by the AccessDataSource, if the DataSourceMode property is set to the DataSet value.
  • Updating - Set the UpdateCommand property to a SQL statement used to update data. This statement is typically parameterized.
  • Deleting - Set the DeleteCommand property to a SQL statement used to delete data. This statement is typically parameterized.
  • Inserting - Set the InsertCommand property to a SQL statement used to insert data. This statement is typically parameterized.
  • Caching - Set the DataSourceMode property to the DataSet value, the EnableCaching property to true, and the CacheDuration and CacheExpirationPolicy properties according to the caching behavior you want for your cached data. 

<asp:AccessDataSource ID="AccessDataSource1" runat="server"

        DataSourceMode="DataSet"

            DataFile="~/App_Data/Test.mdb"

            SelectCommand="SELECT [ID], [FirstName], [State], [Country], [City], [LastName] FROM [Employers]">

        </asp:AccessDataSource>

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"

            AutoGenerateColumns="False" BackColor="LightGoldenrodYellow" BorderColor="Tan"

            BorderWidth="1px" CellPadding="2" DataKeyNames="ID"

            DataSourceID="AccessDataSource1" ForeColor="Black" GridLines="None">

            <Columns>

                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"

                    ReadOnly="True" SortExpression="ID" />

                <asp:BoundField DataField="FirstName" HeaderText="FirstName"

                    SortExpression="FirstName" />

                <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />

                <asp:BoundField DataField="Country" HeaderText="Country"

                    SortExpression="Country" />

                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

                <asp:BoundField DataField="LastName" HeaderText="LastName"

                    SortExpression="LastName" />

            </Columns>

            <FooterStyle BackColor="Tan" />

            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"

                HorizontalAlign="Center" />

            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

            <HeaderStyle BackColor="Tan" Font-Bold="True" />

            <AlternatingRowStyle BackColor="PaleGoldenrod" />

        </asp:GridView>

Let's discuss by diagram now:

First of all drag and drop AccessDataSource control from toolbox.

Figure 1.

And now configure AccessDataSource.

Figure 2.

After that Configure Data Source.

Figure 3.

And now select data by query from database.

Figure 4.

If you want then you can test your query.

Figure 5.

Result looks like this.

Figure 6.

Rest all the information in attached application. Database is inside of App_Data folder. If you want then attach it and work it.