Patrice Paré

In-Depth look at the GridView control bound to an AccessDataSource

Recently, I worked on a small home project. This project objective was to manage of a condominium. Since the condominium holds a few numbers of co-owners and the data access weight would be small, I chose to build a database with Microsoft Access 2007. After inserting some data into the database, I soon realized I had to build a web site which would stand on the database for data persistency because sooner or later, I would like to manage, in a decentralized manner, the condominium and where I would do a minimum of support. Letting people with an Access database is not the best choice and developing Access forms is not what I consider a funny hobby. However, what I consider funny, though, is to build a web site and watch it live.

So, I decided to build a web site with an Access database. To make the process even funnier, I also decided to use the GridView and the AccessDataSource controls. The objectives were to maximize my productivity, minimized the code writing and build something simple quickly. The GridView control is a great tool for data presentation in a tabular layout. It also allows to quickly visualize or edit data. The choice is ideal for expenses, revenues, suppliers and contracts data presentation.

Specifications

I chose to work with VS 2008 SP1, .NET 3.5 SP1, Microsoft Access 2007. The language I selected is C#.

Adding a database

After starting a new project in C#, I added my Microsoft Access 2007 database into the App_Data folder. I want to specify the AccessDataSource control does not work with a secured database with a username and a password because you can not specify a ConnectionString. The work around is to use a SqlDataSource control.

Here is the schema of the Access database used in the first part of this blog entry:

Design et Databinding

After adding the database, I dragged and dropped on the Default.aspx form, from the toolbox, a GridView control and an AccessDataSource control.

A new fonctionality of VS 2008 is to work with the designer and the ASP.NET code in the same window:

Let’s configure the AccessDataSource control with the designer.

When choosing the Demo.ACCDB database in the App_Data folder, we get an error from the designer. It seems the designer cannot get the database schema:

Since the database as a .ACCDB extension, you need to save the database into another format: .MDB. After renaming the database, there is no other action to undergo. The database is fully recognized as you can see in the Server Explorer window. You are ready to work with it.

We can now configure the connection to the database with the AccessDataSource designer. To complete the design and databinding section, you need to associate the GridView DataSourceId property with the AccessDataSource ID property.

Data presentation

The data presentation in a GridView using an AccessDataSource control is a child play. All you need to do is to create a SelectCommand inside the AccessDataSource control:

When assigning the DataSourceId of the GridView with the AccessDataSource ID property using the designer, the wizard adds for you every column identified in the SelectCommand.

What is left is to delete the OwnerID, which is the primary key in the Owners Table and to add the DataKeyNames property to the GridView. The property is useful to identify the fields inside the grid that are primary keys:

If we had many primary keys, we would have assigned the DataKeyNames like this:

A quick save and followed with a View in Browser show the selected data in the grid:

Excerpt of the GridView configuration:

Browser results :

Of course, the layout is entirely customizable through the CSS or through the designer. Also, webmasters of multi-language web sites may like the fact the headers can be regionalized. Finally, the data may be formatted:

Updating data

Updating data is one of the most important aspects in a data driven application and the GridView control is an ideal control that get the job done right. While developing the web site, this technical aspect offered me the most interesting challenges. The correctly configured AccessDataSource control allows to, without any code line, to update data:

Excerpt of the GridView control:

UpdateCommand from the AccessDataSource control:

Why does the update works without any code line? It works because the GridView control adds the parameter values to the parameter collection of the UpdateCommand. It first passes the parameter values from the BoundField objects and then it passes the values from the DataKeyNames property.

Adding a field in the collection of columns without adding it to the property definition of the UpdateCommand of the AccessDataSource controls breaks the order into which the fields are added to the collection. This action is responsible for breaking the UpdateCommand from working correctly and what you get is a GridView no more updating the data. We can conclude the order into which the parameter values are added to the collection is important to the UpdateCommand and it confirms the next statement from MSDN:

“… any parameterized SQL queries that you specify in the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties must match the order of any Parameter objects that are in the corresponding parameter collection. If no parameter collection is specified on the AccessDataSource, the order of parameters that are supplied to these operations must match the order in which they appear in the underlying SQL statement.” Ref: MSDN

Excerpt of the GridView configuration:

Excerpt of the AccessDataSource control:

And the fact that we specify the parameters collection inside the AccessDataSource control does not resolve the problem at hand :

Excerpt of the AccessDataSource control:

The solution to the problem is to add a property to the undesired boundfield to prevent the gridview to add it to the collection. adding the readonly property and setting it to true provides the solution:

excerpt of the accessdatasource control:

When you decide to use the templatefield, its presence does not have any impact on how the gridview control adds its parameters to the updatecommand. the accessdatasource still works perfectly:

excerpt of the gridview configuration:

  

Cascading DropDownLists

When we go to the edition mode, we open the door to bad data input from the users. We can limit the mistakes by using the dropDownLists whenever it is possible. In the following example, I will use three tables to show expenses data: the Expenses table, the Suppliers table and the Contracts table:

Here is the first declarative part of the GridView control :

And here is the columns that are included into the GridView control :

And finally the SelectCommand of the AccessDataSource :

The result in the browser:

That said, we want to edit all values from the above Grid. The database schema of the Expenses shows the Expenses table contains foreign keys pointing toward the Supplier table and the Contract table. Those foreign keys are systemic and meaningless to the users. How do we prevent input mistakes ?

All we have to do is to give to the user restricted meaningful values to choose from, link them to systemic values and bind them to the table. Let’s see how we do it…

Inside the columns collection, we first add a CommandField column the property ShowEditButton. This quickly add all necessary buttons to select and update data:

Let’s start with the suppliers. We replace the BoundField SupplierName by a TemplateField:

And we add another AccessDataSource to bind it to the dropDownList :

From this example, I need to clear out a few things. The AccessDataSource2 control is placed outside the GridView control, somewhere on the page. Also, as we can see, the SelectedValue property of the DropDownList uses the Bind function and receives as a parameter the field SupplierID. This field is provided by the AccessDataSource1 control, which is used to fill the grid. This means we need to add the field to the SelectCommand of the AccessDataSource1:

 

Here is the result:

We do the same process form the ContractNo column. We add a TemplateField, a dropDownList and a third AccessDataSource control:

Here is the excerpt of the TemplateField :

And the third AccessDataSource, dropped somewhere on the page, outside the GridView:

And the results are as follow:

The problem of data input of a contract number is half solved. We allow users to choose from a meaningful contract numbers lists. Some, however, are not related to the selected supplier though. We solve this problem by, first, moving the third AccessDataSource, AccessDataSource3, into theGridView, inside the contract TemplateField, right beneth the contract dropDownList:

Contract TemplateField Excerpt:

After that, we add a filter into the AccessDataSource control:

The filter is an expression alike a where clause in an Access query (in this case since we use AccessDataSource). The filter applies to explicitly specified fields and we add it a parameter value. The filter parameters are defined by a FilterParameters collection. Each filter is associated to a control inside the GridView and to one of its property that contains the value to be retrieved in order to apply the filter. In our case, the filter expression is applied on the field SupplierID with the help of the selected value of the suppliers DropDownList.

Finally, before proceeding to results, we have to remove the property SelectedValue from the contract dropDownList. If not, an exception is raised:

However, when showing the page in the browser, we get another exception:

We need to add to the update parameters collection in order to specify the parameters :

When we view the page in the web browser and we test the update capabilities of the grid, we quickly realize that something is wrong. The data seems to disappear from the grid and a quick glance at the data in the database confirms it:

Before the updates :

After 3 updates :

It seems the GridView can no more make the association between some controls and the UpdateCommand from the AccessDataSource. We also take notice that the SupplierID column accepts the new values selected by the suppliers dropDownList but the selected values associated to the contracts dropDownList are discarded.

To correct this, you need to associate the lost value to the UpdateCommand from :

First, the aspx page, by associating the OnRowUpdating command to a function name used in the code behind:

And then, the code behind:

Now, let’s see the results:

 

Inserting data through the GridView

The gridView is not the tool to use for inserting data into a database. If you are using an AccessDataSource control, it is highly recommended to use a FormView control or a DetailsView control.

However there is a way to make it happen and it is to use the FooterRow. For more information, please read the following: how to use FooterRow to insert data. As I mentioned, beware, this is not something I recommend. I rather mention it as a way some have explored.

Conclusion

Finally, the gridView is a great tool for all purpose applications where you need a tabular layout for your data and apply your own CSS. Associated to a data source, such like the AccessDataSource, your productivity will no doubt increased. Correctly configured, the AccessDataSource requires the minimum of code line.

Although everything looks simple, the architecture of such application using a data souce like the AccessDataSource control is quite simple and rarely meets the n-tier architecture layers we often see in most serious business applications.

Before I wrote the condominium management web application, I never used AccessDataSource or SqlDataSource. I rather liked to write my own layers: a data access layer and a business layer. But I must confess, the AccessDataSource offers all the tools a developer requires to build a home web application.

Patrice

Mots clés Technorati : GridView,AccessDataSource