Accessing and Updating Data in ASP.NET: Updating Basics
By Scott Mitchell
A Multipart Series on ASP.NET's Data Source Controls |
---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
Introduction
ASP.NET 2.0's data source controls make it possible to retrieve, insert, update, and delete data without having to write the tedious dedata access code that was so commonplace in ASP.NET version 1.x. Previous installments of this article series have looked at returning, inserting, and deleting data using the SqlDataSource control. However, we have yet to look at updating data.
In addition to the SqlDataSource control, ASP.NET 2.0 introduces three new data Web controls: the GridView, the DetailsView, and the FormView. These controls can work in unison with the data source controls to facilitate inserting, updating, and deleting data. In this article we will examine basics of using the SqlDataSource and GridView controls to update records from a database. In particular, we look at enabling editing support in the GridView. Future installments will explore more advanced updating scenarios, such as customizing the GridView's editing interface and programmatically setting the updated values. Read on to learn more!
An Overview of Updating Data Using the Data Source Controls
The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities. In a nutshell, all three have a
Update
method that, when invoked, kicks off the following updating workflow:
- The data source's
Updating
event is fired - The updating "action" occurs
- The data source's
Updating
event is fired
UPDATE
statement specified by the control's
UpdateCommand
property. For the ObjectDataSource, the updating "action" involves creating an instance of the data source's underlying
object and invoking the specified UpdateMethod
. This article focuses on the SqlDataSource control; for a detailed
look at updating with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my
Working with Data in ASP.NET 2.0 tutorial series.
Let's explore the updating "action" for the AccessDataSource and SqlDataSource controls in more detail. The
UPDATE
statement or stored procedure specified by the control's UpdateCommand
uses a
parameterized query. That is, if you use an ad-hoc SQL statement for the UpdateCommand
, that UPDATE
statement will use parameters like so:
UPDATE TableName SET
|
Most ad-hoc UPDATE
statements look like the one above: they specify a table name, a set of columns to update, the values to use
when updating the columns, and (usually) a single condition in their WHERE
clause. The WHERE
clause specifies a particular primary key value to update. In the case where a table has a composite primary key
(i.e., a primary key made up of multiple columns), then the WHERE
clause would compare each key column, like so:
UPDATE TableName SET
|
In the Filtering Database Data with Parameters article,
we looked at using parameters in the SelectCommand
to filter the results, such as SELECT * FROM Products
WHERE Price < @UnitPrice
. The parameter - @UnitPrice
, in this instance - has its value specified
via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded
value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user
to enter a price bound in a TextBox; from the querystring; from session state; and so on.
Likewise, the parameter values in the UPDATE
statement are assigned based on the parameters in the data source
control's UpdateParameters collection, and these parameters can use the same sources as the SelectParameters.
Using Less Rigid WHERE Clauses | |
---|---|
In certain situations, the WHERE clause could be more broad. For example, in an employee database you could give all employees who
work for a particular department a 10% raise via a single UPDATE statement by have a WHERE clause like the following:
Alternatively, you could give every employee a 10% raise by omitting the
However, such encompassing |
The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform
their data access. That is, they connect to the database using a SqlConnection
or OleDbConnection
object and specify the command text and parameters via a SqlCommand
or OleDbCommand
object.
Given this information, the updating workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:
- The data source's
Updating
event is fired SqlConnection
andSqlCommand
(orOleDbConnection
andOleDbCommand
) objects are created- The command object's
CommandText
property is assigned the data source control'sUpdateCommand
property - The parameters in the data source control's UpdateParameters collection are added the command object's
Parameters
collection - A connection to the database is established and the command is executed, thereby deleting the data
- The data source's
Updated
event is fired
Updating
, in the
case of the updating workflow); creation of the ADO.NET objects and execution of the appropriate command with the appropriate set of
parameters (UpdateCommand
and UpdateParameters, respectively, in the case of the updating workflow); and firing of the
appropriate post-action event (Updated
, in the case of the updating workflow).
Using Data Web Controls to Update Data
When we examined inserting data and deleting data we looked at doing so both through manually-created Web Forms and through data Web controls, like the GridView or DetailsView. In the manually-created Web Forms example for inserting, for example, we added TextBoxes, DropDownLists, and other Web controls to collect the values for the new record. A SqlDataSource control was added whose InsertParameters referred to these controls. The Web Form also included a Button whose
Click
event handler simply called the SqlDataSource control's
Insert
method, thereby kicking off the inserting workflow.
Updating data through manually-created Web Forms is a bit more difficult and less practical because the Web controls that make up the updating
interface must also be populated with the values of the record being edited. For example, to let a visitor edit a database record you first need
to let them choose which record to edit. Once they've selected this record, you need to show it's existing values in editable fields (textboxes,
cehckboxes, and so on), let the user make any modifications, and then save them. We can easily create the Web controls necessary to
facilitate an editing interface and it is simple enough to use a SqlDataSource control with appropriate UpdateParameters to execute
the appropriate UPDATE
statement with values based on the Web controls' values. However, what is more challenging is loading the
selected record's values into the Web controls. Granted, this is not that difficult to accomplish, but it isn't as easy as pointing and
clicking and requires the page developer to write a bit of code.
Fortunately, ASP.NET 2.0's data Web controls can provide an editing interface. In this article we will look at using the GridView control to update data, although the DataList, DetailsView, and FormView controls are also suitable choices.
Using the GridView Control to Facilitate Updating
The GridView control makes displaying, editing, and deleting data as easy as pointing and clicking. To demonstrate this functionality, let's look at using a GridView to list all of the records from the
Products
table in the Northwind database in a GridView and then
make the grid editable.
Start by adding a SqlDataSource control to the page and specify its SelectCommand
such that it returns the ProductID
,
ProductName
, CategoryID
, UnitPrice
, and Discontinued
fields from the Products
table and the corresponding CategoryName
value from the Categories
table. That is, configure the SqlDataSource so
that it's SelectCommand
contains the following query:
SELECT [ProductID], [ProductName], [Products].[CategoryID], [CategoryName], [UnitPrice], [Discontinued]
|
The SelectCommand
can be specified through the data source's wizard, via the SelectQuery property in the Properties window,
or declaratively. Previous installments in this article series have covered these steps in detail.
With the SqlDataSource control's SelectCommand
specified, we are ready to specify its UpdateCommand
. This can be done
manually through the UpdateQuery property in the Properties window or by entering the statement and UpdateParameters declaratively.
Enter the following UPDATE
statement for the UpdateCommand
:
UPDATE [Products] SET
|
Next, we need to add parameters to the UpdateParameters collection for each of the parameters above (@ProductName
, @CategoryID
,
and so forth). Add a Parameter object for each of the parameters. If you are configuring this information via the UpdateQuery property from the
Properties window, then your screen should look like the following:

The SqlDataSource's resulting declarative markup should look like so:
<asp:SqlDataSource ID="ProductsDataSource" runat="server" ConnectionString="..."
|
Note that each parameter in the UpdateParameters collection is a Parameter object. Recall that when using manually-created Web Forms in the inserting and deleting scenarios, we would use ControlParameter objects that referenced the particular Web control that held the parameter's value. When editing with a GridView we'll
Autogenerating the UpdateCommand |
---|
Rather than manually specifying the UpdateCommand , INSERT , UPDATE , and DELETE statements
can be autogenerated through the SqlDataSource control's wizard.
However, this option is only available if the SelectCommand returns the primary key column(s) in its select list and if
all of the columns are returned from a single table. Moreover, the autogenerated
INSERT , UPDATE , and DELETE statements correspond to the SelectCommand . That is, if the
SelectCommand returns a primary key column and three additional columns, the UPDATE statement will update those three
columns' values. In some cases, however, you might only want to let the user edit a subset of the fields that are displayed. In this case you'd
need to alter the UPDATE statement and the UpdateParameters after they were autogenerated.
For these reasons, I usually prefer to manually construct the |
Next, add a GridView to the page and bind it to the SqlDataSource. Visual Studio will automatically add BoundFields for the ProductID
,
ProductName
, CategoryID
, CategoryName
, and UnitPrice
fields and a CheckBoxField for the
Discontinued
field (since it is a bit field).
At this point, we have a page with a GridView that displays the records from the Products
table. If you view this page in a browser,
your screen should look similar to the following screenshot:

Making the GridView Editable
Making the GridView editable is as simple as checking a checkbox. If the GridView's underlying data source has an
UpdateCommand
specified (as our's does), then the GridView's smart tag will include a checkbox titled "Enable Editing." Check this checkbox to enable editing.
What checking this checkbox does is add a CommandField to the GridView's field list. The CommandField is a column that shows Edit-related or
Delete-related buttons, depending on its property settings. Checking "Enable Editing" adds a CommandField with its ShowEditButton
property set to True. This displays a column of Edit buttons.
When the visitor clicks the Edit button that row becomes editable. The Edit button disappears and in its place Update and Cancel buttons appear. Furthermore, the columns in the GridView become editable, too. In particular, BoundFields turn into TextBox Web controls; CheckBoxFields, which are displayed as unclickable checkboxes in the read-only status, become clickable. This interface allows the user to make changes to the selected row's values.
If the Cancel button is clicked, the row reverts to its pre-editing interface and any changes are not saved. If the Update button is clicked, the SqlDataSource's updating workflow begins, thereby updating the database with the user's changes. After updating the data, the GridView is returned to its pre-editing state.
To illustrate the GridView's editing capabilities, let's step through the editing process from the end user's perspective. The screenshot below shows the GridView when one of its record's Edit buttons has been clicked. There has been a postback and the selected row is now editable. The Edit button has been replaced with Update and Cancel buttons and the row is now shown as TextBoxes and an enabled CheckBox.

As you can see in the screenshot above, the ProductID
column is not editable. The BoundField has a property
named ReadOnly
. If it
is set to True then the field does not become editable. When we bound the SqlDataSource to the GridView it identified that ProductID
was an IDENTITY
column (i.e., one whose values are automatically assigned by the database) and therefore set its ReadOnly
property to True.
Now go ahead and change the values of the ProductName
column and click Update. Clicking Update
causes a postback. On postback, the GridView takes the values from the input controls in its editing interface and automatcially populates
those into the corresponding UpdateParameters. It then automatically starts the SqlDataSource's updating workflow, which causes the
database to be updated. The updated data is then rebound to the GridView.
To recap: configuring the GridView to support editing simply adds a CommandField with its ShowEditButton
property set to True. When a particular record in the grid is edited, the GridView's columns switch to their editing interface.
This causes the BoundFields to be rendered as TextBoxes and CheckBoxFields as clickable checkboxes. Moreover, the Edit button is replaced
by two new buttons: Update and Cancel. Clicking Update causes a postback that saves the changes to the underlying database.
Shortcomings of the GridView's Default Editing Interface
While the GridView's default editing interface makes editing data as easy as checking a checkbox, the default editing interface is somewhat lacking. If a table being edited has foreign keys to other tables - like the
CategoryID
in the Products
table - then
the default editing interface is rather lacking. To see why, try editing a record and changing its category. How do you do this? You might think
that you could just change the CategoryName
value. Go ahead and edit a record and put in a different value for its
CategoryName
field and then click Update. Despite the change, the CategoryName
value is the same as it was prior to
editing the row. This is because there is no @CategoryName
parameter in the SqlDataSource and because the
Products
database table doesn't even have a CategoryName
column, as it comes from the Categories
table. If you want to change a product's category, you need to update the CategoryID
value. Currently, the CategoryID
value's editing interface is rendered as a TextBox, meaning if you want to change a product's category from, say, Beverages to Dairy, you need
to change its CategoryID
value from 1 to 4.
Having to change the CategoryID
column is annoying because it means the user has to know that a CategoryID
value
of 1 corresponds to Beverages and a CategoryID
of 4 corresponds to Dairy. Instead, we should replace the CategoryID
TextBox with a DropDownList listing the available categories. We'll examine how to do this in a future installment of this series.
For now, just leave the CategoryID
as a TextBox, but set the CategoryName
BoundField's ReadOnly
property
to True (since modifications to this column are ignored).
Moreover, the default editing interface lacks any input validation. For example, if you omit the ProductName
value and click Update,
a database-level exception will be thrown because the ProductName
column does not allow NULL
s. Similarily, if you
enter a CategoryID
value outside of the range of valid CategoryID
s or if you enter a non-numeric Salary
value.
It is possible to add validation Web controls to the GridView's editing interface. We'll see how to accomplish this in a future installment
as well.
Conclusion
In this article we looked at how to use the SqlDataSource and GridView controls to update data from a database. The SqlDataSource control encapsulates many of the data access tasks, such as connecting to the database, creating the command object, and executing the parameterized query. Likewise, the GridView control handles the task of generating the editing interface and assigning the user's entries to the data source's UpdateParameters. A future installment of this article series will explore more advanced updating scenarios, including how to customize the GridView's editing interface.
Until then... Happy Programming!
Attachments:
Further Readings:
A Multipart Series on ASP.NET's Data Source Controls |
---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |