Microsoft.NET

……………………………………………….Expertise in .NET Technologies

AccessDataSource Control Overview

Posted by Ravi Varma Thumati on April 29, 2009

The topics in this section describe how to work with the AccessDataSource control in order to connect to a Microsoft Access database.

Retrieving Data Using the AccessDataSource Web Server Control

 

The AccessDataSource control enables you to retrieve data from a Microsoft Access database (.mdb file). You can then display the data in data-bound controls, such as the GridView, FormView, and DetailsView controls.

The AccessDataSource control inherits the SqlDataSource class and replaces the ConnectionString property with a DataFile property to make it more convenient to connect to a Microsoft Access database. The AccessDataSource control uses the System.Data.OleDb provider to connect to Access databases using the Microsoft.Jet.OLEDB.4.0 OLE DB provider.

Connecting to a Database using the AccessDataSource Control

The AccessDataSource control connects to the Microsoft Access database file (.mdb file) identified in the DataFile property. You can set the DataFile property to a universal naming convention (UNC) path that points to an Access database file. The following example demonstrates how you can use a root-relative path to identify an Access database that is located in the App_Data folder of the current Web application.

  <asp:AccessDataSource
    id="AccessDataSource1"
    DataFile="~/App_Data/Northwind.mdb"
    runat="server"
    SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees">
  </asp:AccessDataSource>

If you are storing an Access database file with your Web application, it is recommended that you store it in the App_Data folder to keep the database private. ASP.NET does not allow files in the App_Data folder to be returned if requested directly. The ASP.NET process identity must be granted read and write file permissions to the Access databases stored in the App_Data folder.

The AccessDataSource control sets the ProviderName property of the base SqlDataSource class to the System.Data.OleDb provider and connects using the Microsoft.Jet.OLEDB.4.0 OLE DB provider. You cannot set the ProviderName or ConnectionString properties of the AccessDataSource control.

Selecting Data Using the AccessDataSource Control

You can specify an SQL query for the AccessDataSource control to execute by setting its SelectCommand property as shown in the following code example.

Visual Basic

 <form id="form1" runat="server">
  <asp:AccessDataSource
    id="EmployeesAccessDataSource"
    DataFile="~/App_Data/Northwind.mdb"
    runat="server"
    SelectCommand="SELECT EmployeeID, FirstName, LastName FROM Employees" />
   <asp:GridView
    id="EmployeesGridView"
    runat="server"
    AutoGenerateColumns="True"
    DataSourceid="EmployeesAccessDataSource" />
 C#
 <form id="form1" runat="server">
  <asp:AccessDataSource
    id="EmployeesAccessDataSource"
    DataFile="~/App_Data/Northwind.mdb"
    runat="server"
    SelectCommand="SELECT EmployeeID, FirstName, LastName FROM Employees" />
   <asp:GridView
    id="EmployeesGridView"
    runat="server"
    AutoGenerateColumns="True"
    DataSourceid="EmployeesAccessDataSource" />
 You can return results from a Microsoft Access query by setting the SelectCommandType property of your SelectCommand to StoredProcedure as shown in the following code example. 

Visual Basic

 <asp:AccessDataSource
  id="InvoiceAccessDataSource"
  DataFile="~/App_Data/Northwind.mdb"
  runat="server"
  SelectCommand="[Employee Sales By Country]"
  SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:Parameter Name="Beginning Date" Type="DateTime" defaultValue="1/1/1997" />
    <asp:Parameter Name="Ending Date" Type="DateTime" defaultValue="1/31/1997" />
  </SelectParameters>
</asp:AccessDataSource>
 <asp:GridView
  id="InvoiceGridView"
  runat="server"
  AutoGenerateColumns="True"
  DataSourceid="InvoiceAccessDataSource" />
 C#
 <asp:AccessDataSource
  id="InvoiceAccessDataSource"
  DataFile="~/App_Data/Northwind.mdb"
  runat="server"
  SelectCommand="[Employee Sales By Country]"
  SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:Parameter Name="Beginning Date" Type="DateTime" defaultValue="1/1/1997" />
    <asp:Parameter Name="Ending Date" Type="DateTime" defaultValue="1/31/1997" />
  </SelectParameters>
</asp:AccessDataSource>
<asp:GridView
  id="InvoiceGridView"
  runat="server"
  AutoGenerateColumns="True"
  DataSourceid="InvoiceAccessDataSource" />

Using Parameters with the AccessDataSource Control

You can use parameterized queries for commands and supply parameters at run time. Additionally, you can specify parameters at run time when calling a Microsoft Access query. (To call a Microsoft Access query, you set the command type property for the command to StoredProcedure).

Because the AccessDataSource control extends the SqlDataSource class and uses the System.Data.OleDb provider, you specify parameter placeholders using the “?” placeholder character. The System.Data.OleDb provider does not support named parameters; instead, the parameter values are applied in the order they are specified in the parameters collection. You must ensure that the order of the parameters in the parameters collection matches the order of the parameter placeholders in your SQL statement or Microsoft Access query.

How to: Connect to an Access Database Using the AccessDataSource Control

You can connect the AccessDataSource control to a Microsoft Access database and then display or edit data using controls such as the GridView control.

To use the AccessDataSource control to connect to an Access database

1.      Open the page from which you want to connect to an Access database.

2.      Switch to Design view.

3.      From the Data group on the Toolbox, drag an AccessDataSource control onto your page.

4.      If the Smart Tag panel is not displayed, right-click the control and click Show Smart Tag.

5.      In the AccessDataSource Tasks list, click Configure Data Source.

The Configure Data Source wizard is displayed.

6.      In the Choose a Database pane, type or select the path to your Microsoft Access database, which will have .mdb as the file name extension

Note:

We recommend that you store your Access database in the Web site’s App_Data folder to ensure that the Web server will not return the .mdb file in response to a Web request. In addition, for local Web sites, Visual Web Developer ensures that the Web server has permissions to read and write files in the App_Data folder. For Web sites on other computers, you will need to set those permissions manually.

7.      Click Next.

8.      In the Configure the Select Statement pane, if you want to use the wizard to create a SQL query, click Specify columns from a table or view and use the options in the pane to configure your query.

9.      If you want to use the query builder or write your Select query, click Specify a custom SQL statement or stored procedure, click Next, and then write your SQL statement. Click the UPDATE, INSERT, or DELETE tabs to create update statements.

10.  Click Next.

11.  In the Test Query pane, click Test Query to determine whether the query returns the results you want.

12.  Click Finish.

You can now bind data controls such as the GridView control to the AccessDataSource control.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: