Using the AccessDataSource Control in VS 2005

Microsoft Access Database is file based. Accessing it with an OleDB provider, or an ODBC provider, is the norm. Since the whole database is in one file, a file reference should be all that is necessary to connect to it, and Microsoft has that for you in VS 2005 in the form of the AccessDataSource Control.

However, if the database is protected by a username and password, this is not the method to use, because the AccessDataSource Control can only connect to databases without password protection. The upside in using this control is that it only needs to know where the *.mdb file is located, as we shall see in the course of this tutorial.

AccessDataSource, as the next picture shows, inherits directly from System.Web.UI.WebControls.SQLDataSource, its base class. This code-based tutorial shows how you may use this control to access data on a Microsoft Access Database and then bind the results to two controls, a GridView and a DropDownList.

Creating a web page with the AccessDataSource Control

Create a new web site project named AccessDS as shown in the picture, and onto the default.aspx page in design view, add an AccessDataSource control. This adds a control named AccessDataSource1 as shown. If you click on the arrow at the top right on this control, you get a drop-down showing the tasks that need to be performed. Now click on the hyperlink Configure Data Source….

 

This should open up the window shown in the next picture, where you will carry out the configuration of the datasource. Either you can enter a relative path to a database *.mdb file, or browse to one on your hard drive.

 

However, as seen in the next picture, it does not show any source, nor will it let you browse the hard drive.

Here you may have to copy the database file you want to your application folder in the application directory (C:InetpubwwwrootAccessDS App_Data). This will make the nwind.mdb file (in this tutorial) part of the application. Copying to the App_Data directory provides an additional layer of protection, as this directory will not be served.

When you again try to configure the data source you will come to the next screen, where you can now highlight the nwind.mdb file in the right pane and click on the OK button.

 

Now the configure Data Source window shows the relative path to the database, ~/App_Data/nwind.mdb as shown. Click on the Next button.

 

{mospagebreak title=Querying the database}

Against the database you can run a SQL statement (or a stored procedure), or specify the columns you need displayed by making the appropriate choice. Here a decision has been made to specify the columns, and from the drop-down the appropriate table (Employees) has been chosen with a couple of columns. The SQL statement for the chosen columns appears in the Select Statement: area. You can do more advanced filtering and ordering of your data using the buttons shown on the right. You may also return unique rows of data if you choose to do so. On the other hand you can go to the previous screen and make changes if you like.

When you click on the Next button you will be given a chance to test the query and display its output in the text area as shown.

The field (column) “Photo” was chosen in the select statement, and when the Test Query button was clicked, an error message was shown as in the next picture. It shows the message that you should not choose Memo, OLE Object, or hyperlinks for displaying in the above window. 

Click OK to this screen, hit the previous button and drop the column “Photo”  by unchecking it. Proceeding forward will display the text only result.

Now click the Finish button, which completes the configuration of the data source for the AccessDataSource control. The configuration parameters that you have chosen and the defaults can be see when you right click to display the properties, as shown in the next picture.

{mospagebreak title=Binding the query result to a GridView}

GridView is an improvement over the DataGrid in many ways. This tutorial does not cover all the nice things about GridView, but simply uses it to display the result of running the query. At this point you may add a Command button and a GridView control to the default.aspx page as shown. If you click the little black arrow at the top left of the GridView Control (pointing backwards) you may get a hint as to the tasks you need to perform for this control. If you want you may choose the data source, in which case you need not write any code. Here, no source has been defined in the design mode. Only the Auto Format… hyperlink was clicked to make GridView look pretty.

Binding data to a GridView Control

To the click event of the button marked Select employees Information, type in  the following code.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As _

System.EventArgs)
Handles Button1.Click
Dim ds As New AccessDataSource ds = AccessDataSource1 Response.Write(ds.CacheDuration & "<br>") Response.Write(ds.CacheExpirationPolicy & "<br>") Response.Write(ds.ConnectionString.ToString & "<br>") ds.DataFile = "~/App_Data/nwind.mdb" ds.DataSourceMode = SqlDataSourceMode.DataSet GridView1.DataSource = ds GridView1.DataBind() GridView1.Visible = True
End Sub

The code instantiates a new AccessDataSource control and assigns to it the configured control described earlier. The three statements write the CacheDuration, CacheDurationPolicy and the ConnectionString to the screen. These are not necessary for data binding, but written out of curiosity. However, the DataFile and DataSourceMode are necessary for the display. The binding of DataSource to GridView uses just one line of code. When you build the application and choose to display the default.aspx page by clicking on the Show employees Information button, you will see the following.

{mospagebreak title=Binding data to a drop-down list control}

Configuring the data source used by the drop-down

For this purpose we shall use the same database, but create a second AccessDataSource control, AccessDataControl2, whose configuration details are as shown in this composite picture. We will only use the LastName in the drop-down list alphabetically arranged in the ascending order as shown.

This configures the AccessDataSource with the following SELECT statement:

SELECT DISTINCT LastName FROM Employees ORDER BY LastName

Binding data to drop-down list

Clicking on the “task arrow” at the top-left of the drop-down list control brings up the tasks list for this control. Clicking on the Choose Data Source… hyperlink opens up the Choose a Data Source section of the Data Source Configuration Wizard as shown. You can click on the Select a data source drop-down to reveal the available data sources configured. Since AccessDataSource2 will be used, choose this source. The Select a data field to display in the DropDownList should only show one item, namely the “LastName.” This is the same, by default, for the drop-down’s value for the field as shown. Since the data source has been assigned at design time there is no need to write any code.

The properties of the DropDownList1 control are as shown in this picture. All these assignments are made in the previous dialogue.

The result of displaying the control by browsing the default.aspx page is as shown in the next picture (the Show Employee Information button was not clicked).

Summary

The AccessDataSource control saves a lot of trouble and reduces the labor involved in configuring ODBC or OLEDB connections. However, its use is limited to databases which are not protected. The documentation, however, says that the base class may be used in such cases. Data binding was shown through both code, and at design time. The data source configuration wizard also includes a query designer, although one was not used in the tutorial.

One thought on “Using the AccessDataSource Control in VS 2005

  1. It’s not only athletes take steroids, even programs can. AccessDataSource control takes you the very center of RAD. Writing code is almost reduced to a single line. No more connection strings that tangle here , there and everywhere. Most of what you want to do can be done at design time. The IDE even gives you a query editor.
    The article also shows how you may bind the data from this control to the Grid View control -datagrid on steroid if I may say so.
    Thanks for reading the article and please let me know your comments.

    sincerely,

    J

[gp-comments width="770" linklove="off" ]