All data-driven Web sites are driven by data management systems. These systems house the information displayed on Web pages and represent the transactions with customers and interactions with clients that promote the success of an organization. Even before a store-front appears on the Web, there must be collections of information representing products and services for sale. Before an internal work-flow procedure takes place, there must be repositories of data to share and data that trigger the flows. Before managers can evaluate the results of operations there must be data available about those operations. In short, any Web-based system must be founded on the information it gathers, processes, analyzes, and disseminates. It must be supported by its repositories of information and the database management systems that maintain them.

Database maintenance applications comprise the back-end infrastructure to collect and store organizational data and to keep them current. Basic maintenance activities include adding new records to database tables, deleting outdated records, and changing the content of current records to keep them up to date. Beginning with this tutorial, various ways of performing database maintenance are discussed, starting with built-in features supplied by the GridView control.

Basic GridView Editing

A GridView control along with a data source control include features to perform basic editing of database fields without the need to write script. In the following example, a GridView that displays selected products from the BooksDB.mdb database includes "Edit" buttons for the book records. When a button is clicked, displayed fields for the record are converted into textboxes or other appropriate controls to permit changing displayed values. "Update" and "Cancel" buttons appear to rewrite the changed values to the database or to cancel updating. The changed record is redisplayed in the GridView table.

Note that making actual changes to the BooksDB.mdb database is not permitted in these tutorials; however, all other functions work as expected.

It requires very little code to produce a default GridView that permits editing. Code for the above editable GridView and its AccessDataSource are shown below.

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  
  SelectCommand="SELECT BookID, BookType, BookTitle, BookAuthor, BookPrice,
                 BookQty, BookSale FROM Books WHERE BookType = 'Database'
                 ORDER BY BookID"
  
  UpdateCommand="UPDATE Books SET BookType=@BookType, BookTitle=@BookTitle, 
                 BookAuthor=@BookAuthor, BookPrice=@BookPrice, 
                 BookQty=@BookQty, BookSale=@BookSale WHERE BookID=@BookID"
/>

<h3>Book Edit</h3>

<asp:GridView id="EditGrid" DataSourceID="BookSource" Runat="Server"
  AutoGenerateEditButton="True"
  DataKeyNames="BookID"
  HeaderStyle-Font-Size="10pt"
  RowStyle-Font-Size="10pt"/>

A column of "Edit" buttons is added to the GridView by setting its AutoGenerateEditButton property to "True". A click on one of these buttons changes the row from display to edit mode and replaces the "Edit" button with "Update" and "Cancel" buttons.

A changed record that is rewritten to the database is identified by a field having unique values that distinguish the updated record from all other records in the table. It is necessary to identify this primary key field to the GridView through its DataKeyNames property. Although it is not necessary for this field to be formally designated as a primary key in the database, it is proper practice to do so. In the above example, the BookID field is the primary key for the Books table and is designated as such in the DataKeyNames property of the GridView.

For certain database tables, more than one field might be needed to provide a unique identifier for records. In these cases, all fields that are combined to produce a unique key are given in a comma-separated list in the DataKeyNames property.

When the chosen row is put into edit mode, DataKeyNames fields are not rendered as textboxes for making changes. Changing a record key can wreck all kinds of havoc with a database. A changed key misidentifies the record being updated, possibly leading to an updating error or causing the wrong record to be updated. Also, if records in other database tables are linked through the key field, then they become disassociated from the changed record. Therefore, record keys should never be changed. Instead, it is better practice to delete the record and create a new one with a new key, at the same time changing related values in associated tables.

GridView Updating

The AccessDataSource for an editable GridView requires two SQL commands—a SELECT statement is needed to select records for display, and an UPDATE statement is required to rewrite changes to the database. The SELECT statement is given by the control's SelectCommand property. The UPDATE statement is given in the UpdateCommand property. The SelectCommand statement is similar to that used in previous examples of displaying database records in a GridView. In this example, it selects only records with "Database" as the BookType value. The BookDescription field is not displayed in this example simply to reduce the amount of information appearing on this page.

The UPDATE command for updating the Books table contains database field names and associated parameters, or binding values, identified by the prefix "@".

UpdateCommand="UPDATE Books SET BookType=@BookType, BookTitle=@BookTitle, 
               BookAuthor=@BookAuthor, BookPrice=@BookPrice, 
               BookQty=@BookQty, BookSale=@BookSale WHERE BookID=@BookID"

The update specification BookType=@BookType, for example, gives the database field to update (BookType) along with a reference to its update value (@BookType). As shown in the following illustration, this update value is taken from the textbox edit field whose original value is bound to it by the SELECT statement that populates the GridView.

It is important to remember that fields identified as DataKeyNames have ReadOnly properties and cannot take part in updating since edit boxes are not created for these fields. In the above example, BookID is a key field. When a record is placed in edit mode, no edit box is created for this field. Therefore, the BookID value cannot be changed and the update assignment BookID=@BookID cannot be part of the UPDATE statement's list of update assignments.

It is also important that a WHERE clause appear in the UPDATE statement to identify which record to update. This clause identifies a DataKeyNames field to match to a database field for rewriting the record. In this example, the record updated is the one where the BookID field in the Books table is matched by the value of the @BookID parameter in the update row of the GridView (BookID=@BookID). If a WHERE clause is not provided, all records in the table mistakenly receive the same changed values.

Updating of the database takes place when the "Update" button is clicked for the row being edited and the UPDATE statement is issued through the AccessDataSource. Then the SELECT statement is reissued to repopulate the GridView with changed values. An option is to click the "Cancel" button to abort updating and return the row to display mode without changes.

Formatted GridView Editing

A GridView can be formatted with bound columns for more control over its display. The following GridView displays the entire Books table as a paged recordset with various bound-field controls used for display and editing. For illustration purposes, it permits editing of only the BookType and BookDescription fields.

Full coding for this application appears below. The AccessDataSource for the GridView is similar to the previous one except that it selects all records from the Books table. Also the UPDATE statement updates only the BookType and BookDescription fields. A second AccessDataSource supplies BookTypes for the DropDownList that appears in edit mode.

The GridView has its AutoGenerateColumns property set to False since bound fields and template fields are defined for the columns. Also, AutoGenerateEditButtons is set to False (the default value) since explicitly coded edit buttons replace the automatic text buttons to edit, update, and cancel editing for a row. The BookID field is the record key and declared as such in the DataKeyNames property. Paging is turned on for four records per page. Note that when a row is put into edit mode an EditRowsStyle property setting becomes available to style this row.

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  
  SelectCommand="SELECT * FROM Books ORDER BY BookID"
  
  UpdateCommand="UPDATE Books SET BookType=@BookType, 
                 BookDescription=@BookDescription
                 WHERE BookID=@BookID"
/>

<asp:AccessDataSource id="TypeSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand="SELECT DISTINCT BookType FROM Books 
                 ORDER BY BookType"/>

<h3>Book Edit</h3>

<asp:GridView id="EditGrid" DataSourceID="BookSource" Runat="Server"
  AutoGenerateColumns="False"
  DataKeyNames="BookID"
  AutoGenerateEditButton="False"
  AllowPaging="True"
  PageSize="4"
  CellPadding="3"
  HeaderStyle-Font-Size="10pt"
  HeaderStyle-BackColor="#707070"
  HeaderStyle-ForeColor="#FFFFFF"
  RowStyle-Font-Size="10pt"
  RowStyle-VerticalAlign="Top"
  EditRowStyle-BackColor="Yellow">
  
  <Columns>
  
  <asp:TemplateField 
    HeaderText="Edit">	
    <ItemTemplate>
      <asp:Button CommandName="Edit" Runat="Server"
      Text="Edit" Font-Size="8pt" Width="45px"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:Button CommandName="Update" Runat="Server"
      Text="Update" Font-Size="8pt" Width="45px"/>		
      <asp:Button CommandName="Cancel" Runat="Server"
      Text="Cancel" Font-Size="8pt" Width="45px"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:BoundField ReadOnly="True"
    HeaderText="ID"
    DataField="BookID"/>
  
  <asp:TemplateField
    HeaderText="Type">
    <ItemTemplate>
      <asp:Label Text='<%# Eval("BookType") %>' Runat="Server"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList id="EditType" Runat="Server"
        DataSourceID="TypeSource"
        DataTextField="BookType" 
        DataValueField="BookType" 
        SelectedValue='<%# Bind("BookType") %>'
        Font-Size="8pt"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:BoundField ReadOnly="True" 
    HeaderText="Title"
    DataField="BookTitle"/>
  
  <asp:BoundField ReadOnly="True" 
    HeaderText="Author"
    DataField="BookAuthor"/>
  
  <asp:TemplateField 
    HeaderText="Description">
    <ItemTemplate>
      <asp:Panel Width="150px" Height="30px" Runat="Server" 
      ScrollBars="Vertical">
        <asp:Label Text='<%# Eval("BookDescription") %>' Runat="Server"/>
      </asp:Panel>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditDescription" Runat="Server"
        Text='<%# Bind("BookDescription") %>'
        TextMode="MultiLine" Rows="3" Width="150px" 
        Font-Name="Arial" Font-Size="8pt"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:BoundField ReadOnly="True" 
    HeaderText="Price"
    DataField="BookPrice"
    DataFormatString="{0:N}"
    ItemStyle-HorizontalAlign="Right"/>
  
  <asp:BoundField ReadOnly="True" 
    HeaderText="Qty"
    DataField="BookQty"
    DataFormatString="{0:D}" 
    ItemStyle-HorizontalAlign="Right"/>
  
  <asp:CheckBoxField ReadOnly="True" 
    HeaderText="Sale"
    DataField="BookSale"/>
  
  </Columns>

</asp:GridView>

Creating Edit Buttons

Rather than using default text buttons to edit, update, and cancel editing, an <asp:TemplateField> is defined to create a set of explicitly coded buttons to take on these functions. Since AutoGenerateEditButton="False" is coded for the GridView, an alternate set of buttons must be provided. They are provided here in the form of <asp:Button> controls. Coding for this TemplateField is repeated below.

<asp:TemplateField 
  HeaderText="Edit">	
  <ItemTemplate>
    <asp:Button CommandName="Edit" Runat="Server"
    Text="Edit" Font-Size="8pt" Width="45px"/>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:Button CommandName="Update" Runat="Server"
    Text="Update" Font-Size="8pt" Width="45px"/>		
    <asp:Button CommandName="Cancel" Runat="Server"
    Text="Cancel" Font-Size="8pt" Width="45px"/>
  </EditItemTemplate>
</asp:TemplateField>

In addition to a HeaderTemplate, ItemTemplate, and FooterTemplate, a TemplateField can include an <EditItemTemplate>. Whereas its ItemTemplate describes the normal display appearance of a column; the EditItemTemplate describes its appearance when the row is placed in edit mode.

The ItemTemplate includes an <asp:Button> control taking the place of the normal "Edit" text button. This button's CommandName="Edit" property must be coded in order for the replacement button to function like its replaced text button. The EditItemTemple describes the column's appearance when in edit mode. In this case, <asp:Button> controls replace the normal "Update" and "Cancel" text buttons. They must contain CommandName="Update" and CommandName="Cancel" properties, respectively, to serve as functional replacements for the text buttons.

Editing with a BoundField

Several of the columns are formatted with <asp:BoundField> controls. These include the columns to display the BookID, BookTitle, BookAuthor, BookPrice, BookQty, and BookSale fields. Code for the BoundField used to display the BookID key field is repeated below. Other BoundField columns are similarly coded.

<asp:BoundField ReadOnly="True"
  HeaderText="ID"
  DataField="BookID"/>

This is a field named in the DataKeyNames list. Because it is a record key field, it must include the ReadOnly="True" property to prohibit display of an edit box when the row is placed in edit mode. The other fields formatted with BoundField controls—BookTitle, BookAuthor, BookPrice, BookQty, and BookSale—also have ReadOnly="True" settings to disallow changing their values even though they are not key fields. Recall that only the BookType and BookDescription fields are editable in this example.

Editing with a TemplateField

TemplateFields are used in place of BoundFields for display of the BookType and BookDescription columns. The reason for using TemplateFields is for greater control over their display and editing appearances.

In the case of the BookType field, a DropDownList supplies the BookType values from which to choose a replacement. Using a DropDownList of available types ensures that only valid types are chosen, something that cannot be guaranteed if editing is done in a standard free-entry textbox. Code for this TemplateField and its associated AccessDataSource are repeated below.

<asp:AccessDataSource id="TypeSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand="SELECT DISTINCT BookType FROM Books ORDER BY BookType"/>

...
<asp:TemplateField
  HeaderText="Type">
  <ItemTemplate>
    <asp:Label Text='<%# Eval("BookType") %>' Runat="Server"/>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:DropDownList id="EditType" Runat="Server"
      DataSourceID="TypeSource"
      DataTextField="BookType" 
      DataValueField="BookType" 
      SelectedValue='<%# Bind("BookType") %>'
      Font-Size="8pt"/>
  </EditItemTemplate>
</asp:TemplateField>

The ItemTemplate displays the current BookType value for a record. This value is displayed through a Label control bound to the database value with a standard <%# Eval("BookType") %> binding expression.

The EditItemTemplate presents a DropDownList of BookType values drawn from the database for choosing a replacement value. Since this DropDownList is populated from the database, an AccessDataSource must be associated with this control to extract all unique (DISTINCT) book types from the Books table. In addition, the DropDownList preselects the type that matches the database BookType for the particular record in the edit row. This matching selection takes place by coding a SelectedValue property for the DropDownList and binding (see below) to it the current BookType value for the record being edited.

Binding Updatable Fields

Notice the format of the binding that takes place for the SelectedValue property of the DropDownList.

SelectedValue='<%# Bind("BookType") %>'

In the previous Label control that simply displays the current value of the BookType, a binding expression in the form <%# Eval("fieldname")%> is used. This expression displays a value from a database; however, it does not permit updating of a changed value back to the database. The Eval() form of binding expression cannot be associated with a @fieldname parameter in an UPDATE statement.

Importantly, GridView fields that permit updating must be bound to their values with binding expressions in the form <%# Bind("fieldname") %>; that is, updatable values must use binding rather than evaluating for their display.

<%# Bind("fieldname") %>

This type of binding takes place for updatable BoundField controls, although it takes place behind the scenes and is not specified. For other types of bound controls, such as those appearing in the EditItemTemplate of a TemplateField, this binding must be explicit.

It also is important to remember that when a Bind() expression is used for an updatable field, the bound control must be assigned an id value. This is the reason for the assignment id="EditType" in the DropDownList in the above example. This id does not have to match the name of the bound data field, but it must be unique among all id values assigned to editable controls.

The TemplateField for the BookDescription field also contains an editable control. Its code is repeated below.

<asp:TemplateField 
  HeaderText="Description">
  <ItemTemplate>
    <asp:Panel Width="150px" Height="30px" Runat="Server"
    ScrollBars="Vertical">
      <asp:Label Text='<%# Eval("BookDescription") %>' Runat="Server"/>
    </asp:Panel>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:TextBox id="EditDescription" Runat="Server"
      Text='<%# Bind("BookDescription") %>'
      TextMode="MultiLine" Rows="3" Width="150px" 
      Font-Name="Arial" Font-Size="8pt"/>
  </EditItemTemplate>
</asp:TemplateField>

The ItemTemplate defines a Label control to display the current database value for the record, linking to the value through the <%# Eval("BookDescription") %> binding expression. The Label appears inside a Panel control that is given style settings to create a scrollable display area.

The EditItemTemplate defines a multiline TextBox to display the editable book description. The TextBox uses the binding expression <%# Bind("BookDescription") %> and is assigned an id since changes to the description are updated in the database.

Using TemplateFields for DataKeys

In the current example, the BookID key field is formatted with a BoundField control. A key field also can be formatted as a TemplateField. The following field does not appear in the example, but can be used in place of the BoundField.

<asp:TemplateField
  HeaderText="ID">
  <ItemTemplate>
    <asp:Label Text='<%# Eval("BookID") %>' Runat="Server"/>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:Label Text='<%# Eval("BookID") %>' Runat="Server"/>
  </EditItemTemplate>
</asp:TemplateField>

When a TemplateField formats a DateKeyNames field, its value normally is displayed in a Label control in the EditItemTemplate, which does not permit editing and makes it unnecessary to code a ReadOnly="True" property. The data field is bound to the control with an Eval() expression rather than a Bind() expression since the value is not updatable.

Editing with a CheckBoxField

The BookSale field is defined as an <asp:CheckBoxField> to match its format in the database. It is also identical in format and function to the field supplied in the default GridView. In this example, the BookSale field is not updatable; therefore, it must be assigned a ReadOnly="True" property.

<asp:CheckBoxField ReadOnly="True"
  HeaderText="Sale"
  DataField="BookSale"/>

An alternative to using the GridView's CheckBoxField is to create a TemplateField with an <asp:CheckBox> control. This alternate coding, although not used in the current example, is shown below.

<asp:TemplateField 
  HeaderText="Sale">
  <ItemTemplate>
    <asp:CheckBox Checked='<%# Eval("BookSale") %>' Runat="Server"/>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:CheckBox Checked='<%# Eval("BookSale") %>' Runat="Server"/>
  </EditItemTemplate>
</asp:TemplateField>

The current setting for the CheckBox is given by assigning its Checked property from the database. Recall that this is a "Yes/No" field in the Access database that has a value of True or False depending on whether it is checked or not. Therefore, the Checked property takes on a value of True or False, checking or not checking the CheckBox. Notice that this value is assigned with an Eval() expression in the EditItemTemplate since changing its value is not permitted. Were this to be an updatable field, it would be bound with a Bind() expression and an id would be assigned to the field.

Update Parameters

In the present example, only the BookType and BookDescription fields are updated by rewriting the record with the matching BookID (primary key) value. The SQL UPDATE statement assigned to the UpdateCommand property of the AccessDataSource reflects this updating.

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  
  SelectCommand="SELECT * FROM Books ORDER BY BookID"
  
  UpdateCommand="UPDATE Books SET BookType=@BookType, 
                 BookDescription=@BookDescription
                 WHERE BookID=@BookID"
/>

The general rule for coding the UpdateCommand is that a parameter appearing in an UPDATE clause to represent an updatable field must be one of the following:

• a BoundField without a ReadOnly="True" property and without a DataKeyNames entry,
• a TemplateField in the EditItemTemplate with a Bind() binding expression, without a ReadOnly="True" property, and without a DataKeyNames entry,
• an ImageField, CheckBoxField, or HyperLinkField without a ReadOnly="True" property, with a Bind() binding expression, and without a DataKeyNames entry,

and a parameter name appearing in a WHERE clause to signify a key field used to update the matching record must be one of the following:

• a BoundField with ReadOnly="True" and a DataKeyNames entry,
• a TemplateField with an Eval() binding expression and a DataKeyNames entry,
• an ImageField, CheckBoxField, or HyperLinkField with an Eval() binding expression and a DataKeyNames entry.

In short, the data field appearing in a WHERE clause cannot also appear in the UPDATE clause since this would mean updating, or changing, the value used to locate the record being updated.

GridView Edit Events and Handlers

When performing GridView updating it is wise not to permit invalid or unreasonable data to enter the database. Prior to updating, then, changed data should be tested to avoid errors, either run-time errors caused by processing of invalid data or corruptions to the database from unreasonable data values.

The following GridView opens all data fields for editing and performs update validations to ensure that entered data are correct. When the "Update" button is clicked, entered BookPrice and BookQty values are checked for nonnumeric characters. Also, the BookQty value is verified to be no larger that 100 units. If a validation test is not passed, the update event is cancelled and an error message is displayed.

Notice in the following listing that most fields are defined as TemplateFields for greater control over their layout and formatting. All of these update fields have their data bindings made with Bind() expressions to supply values for their like-named parameters in the SQL UPDATE statement.

<SCRIPT Runat="Server">

Sub Edit_Row (Src As Object, Args As GridViewEditEventArgs)

  EditMSG.Text = "Row " & Args.NewEditIndex + 1 & " edit"

End Sub

Sub Validate_Data (Src As Object, Args As GridViewUpdateEventArgs)

  If Not IsNumeric(Args.NewValues("BookPrice")) Then
    Args.Cancel = True
    EditMSG.Text = "-- Book Price is not numeric. Record not updated."
  End If
  
  If Not IsNumeric(Args.NewValues("BookQty")) Then
    Args.Cancel = True
    EditMSG.Text = "-- Book Quantity is not numeric. Record not updated"
  End If
  
  If Args.Cancel = False Then
    If Args.NewValues("BookPrice") < 0 _
    OR Args.NewValues("BookPrice") > 200 Then
      Args.Cancel = True
      EditMSG.Text = "-- Book Price out of range. Record not updated"
    End If
  End If
  
  If Args.Cancel = False Then
    If Args.NewValues("BookQty") < 0 Then
      Args.Cancel = True
      EditMSG.Text = "-- Book Quantity out of range. Record not updated"
    End If
  End If
  
End Sub

Sub Display_Message (Src As Object, Args As GridViewUpdatedEventArgs)
  
  EditMSG.Text = " Record " & Args.Keys("BookID") & " updated"

End Sub

</SCRIPT>

<form Runat="Server">

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  
  SelectCommand="SELECT * FROM Books ORDER BY BookID"
  
  UpdateCommand="UPDATE Books SET BookType=@BookType, BookTitle=@BookTitle, 
                 BookAuthor=@BookAuthor, BookDescription=@BookDescription, 
                 BookPrice=@BookPrice, BookQty=@BookQty, BookSale=@BookSale
                 WHERE BookID=@BookID"
/>

<asp:AccessDataSource id="TypeSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand="SELECT DISTINCT BookType FROM Books ORDER BY BookType"/>

<h3>Book Edit w/Validation</h3>

<asp:Label id="EditMSG" Text="&nbsp;" ForeColor="Red" Runat="Server"
EnableViewState="False"/>

<asp:GridView id="EditGrid" DataSourceID="BookSource" Runat="Server"
  AutoGenerateEditButton="False"
  DataKeyNames="BookID"
  OnRowEditing="Edit_Row"
  OnRowUpdating="Validate_Data"
  OnRowUpdated="Display_Message"
  AutoGenerateColumns="False"
  AllowPaging="True"
  PageSize="4"
  CellPadding="3"
  HeaderStyle-Font-Size="10pt"
  HeaderStyle-BackColor="#707070"
  HeaderStyle-ForeColor="#FFFFFF"
  RowStyle-Font-Size="9pt"
  RowStyle-VerticalAlign="Top"
  EditRowStyle-BackColor="Yellow">
  
  <Columns>
  
  <asp:TemplateField 
    HeaderText="Edit">	
    <ItemTemplate>
      <asp:Button CommandName="Edit" Runat="Server"
        Text="Edit" Font-Size="8pt" Width="45px"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:Button CommandName="Update" Runat="Server"
        Text="Update" Font-Size="8pt" Width="45px"/>		
      <asp:Button CommandName="Cancel" Runat="Server"
        Text="Cancel" Font-Size="8pt" Width="45px"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField 
    HeaderText="ID">
    <ItemTemplate>
      <asp:Label Text='<%# Eval ("BookID") %>' Runat="Server"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:Label Text='<%# Eval ("BookID") %>' Runat="Server"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField 
    HeaderText="Type">
    <ItemTemplate>
      <asp:Label Text='<%# Eval("BookType") %>' Runat="Server"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList id="EditType" Runat="Server"
        DataSourceID="TypeSource" 
        DataTextField="BookType" 
        DataValueField="BookType" 
        SelectedValue='<%# Bind("BookType") %>'
        Font-Size="8pt"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField 
    HeaderText="Title">
    <ItemTemplate>
      <asp:Label Text='<%# Eval("BookTitle") %>' Runat="Server"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditTitle" Runat="Server"
      Text='<%# Bind("BookTitle") %>' 
      Font-Size="8pt" Width="80px"/>
    </EditItemTemplate>
  </asp:TemplateField>	
  
  <asp:TemplateField 
    HeaderText="Author">
    <ItemTemplate>
      <asp:Label Text='<%# Eval("BookAuthor") %>' Runat="Server"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditAuthor" Runat="Server"
      Text='<%# Bind("BookAuthor") %>'
      Font-Size="8pt" Width="80px"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField 
    HeaderText="Description">
    <ItemTemplate>
      <asp:Panel Runat="Server" Width="150px" Height="30px" Runat="Server"
      ScrollBars="Vertical">
        <asp:Label Text='<%# Eval("BookDescription") %>' Runat="Server"/>
      </asp:Panel>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditDescription" Runat="Server"
        Text='<%# Bind("BookDescription") %>'
        TextMode="MultiLine" Rows="3" Width="150px"
        Font-Name="Arial" Font-Size="8pt"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField 
    HeaderText="Price"
    ItemStyle-HorizontalAlign="Right">
    <ItemTemplate>
      <asp:Label HorizontalAlign="Right" Runat="Server"
      Text='<%# String.Format("{0:N2}", Eval("BookPrice")) %>'/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditPrice" Runat="Server"
      Text='<%# Bind("BookPrice") %>'
      Font-Size="8pt" Width="50" MaxLength="7" Style="text-align:right"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField 
    HeaderText="Qty"
    ItemStyle-HorizontalAlign="Right">
    <ItemTemplate>
      <asp:Label Text='<%# String.Format("{0:D}", Eval("BookQty")) %>' 
      Runat="Server"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditQuantity" Runat="Server"
      Text='<%# Bind("BookQty") %>'
      Font-Size="8pt" Width="30" MaxLength="2" Style="text-align:right"/>
    </EditItemTemplate>
  </asp:TemplateField>
	
  <asp:CheckBoxField 
    HeaderText="Sale"
    DataField="BookSale"/>
  
  </Columns>

</asp:GridView>

</form>

GridView Edit Event

When a row's "Edit" button is clicked, a GridView issues a RowEditing event that can be intercepted and programmed by including an OnRowEditing event handler as shown in the above listing. This event handler calls a named subprogram, Edit_Row in this example, through which event properties are exposed. This event, its event handler, and its subprogram argument are shown in the following listing.

For the RowEditing event, there are two properties exposed to a subprogram called by the OnRowEditing event handler and received through argument GridViewEditEventArgs. Script references to these properties are in the formats shown below.

argument.NewEditIndex
argument.Cancel="False|True"

The NewEditIndex property returns the index of the row being editing (counting from 0). The Cancel property can be set to True to halt editing and return the row to display mode from edit mode. The following subprogram is called by the OnRowEditing handler for the example GridView to report the row number being edited when the "Edit" button is clicked.

Sub Edit_Row (Src As Object, Args As GridViewEditEventArgs)

  EditMSG.Text = "Row " & Args.NewEditIndex + 1 & " edit"

End Sub

Properties are exposed through the subprogram's argument, named Args in this example. Notice that 1 is added to the Args.NewEditIndex property for the edit row. Since GridView row indexing begins with 0, this addition reports a row number that is more obvious when viewing the GridView. This message is displayed in the UpdateMSG Label appearing above the GridView. Editing is not cancelled, but awaits the user's click on the now-displayed "Update" and "Cancel" buttons.

GridView Update Events

When a row's "Update" button is clicked, a GridView issues two events which can be intercepted and programmed with event handlers. A subprogram can be called on a RowUpdating event, when the update process begins, and on a RowUpdated event, when updating is completed. These two events, their event handlers, and their subprogram arguments are shown below.

To trap a RowUpdating event in a GridView, an OnRowUpdating event handler is coded; to trap a RowUpdated event, an OnRowUpdated handler is coded. These event handlers are associated with subprograms containing arguments GridViewUpdateEventArgs and GridViewUpdatedEventArgs, respectively.

Subprogram arguments for both RowUpdating and RowUpdated events include properties that can be investigated by scripts. Script references to these properties are in the general formats shown below.

--GridViewUpdateEventArgs Properties--

argument.OldValues("field")
argument.NewValues("field")
argument.Keys("field")
argument.RowIndex
argument.Cancel="False|True"

--GridViewUpdatedEventArgs Properties--

argument.OldValues("field")
argument.NewValues("field")
argument.Keys("field")
argument.AffectedRows
argument.KeepInEditMode="False|True"

For both RowUpdating and RowUpdated events, the OldValues property contains a collection of field names and values from the original GridView row; the NewValues property contains a collection of names and values from the changed row; the Keys property contains names and values of key fields for the row. For the RowUpdating event, a Cancel property is available and can be set to True during updating, prior to the RowUpdated event, in order to cancel updating. The RowIndex property returns the index of the row (counting from 0) that is being updated. For the RowUpdated event, the AffectedRows property gives the number of rows updated. Normally, when the "Update" button is clicked and a row is updated, the GridView switches back to display mode. A row can be kept in edit mode by setting the KeepInEditMode property to True.

The following Validate_Data subprogram is called on a RowUpdating event for the example GridView to test entered values for the BookPrice and BookQty fields. If the data are invalid (nonnumeric) or not within a reasonable range (less than 0), then updating is cancelled and an error message is displayed.

Sub Validate_Data (Src As Object, Args As GridViewUpdateEventArgs)

  If Not IsNumeric(Args.NewValues("BookPrice")) Then
    Args.Cancel = True
    EditMSG.Text = "-- Book Price is not numeric. Record not updated."
  End If

  If Not IsNumeric(Args.NewValues("BookQty")) Then
    Args.Cancel = True
    EditMSG.Text = "-- Book Quantity is not numeric. Record not updated"
  End If

  If Args.Cancel = False Then
    If Args.NewValues("BookPrice") < 0 _
    OR Args.NewValues("BookPrice") > 200 Then
      Args.Cancel = True
      EditMSG.Text = "-- Book Price out of range. Record not updated"
    End If
  End If

  If Args.Cancel = False Then
    If Args.NewValues("BookQty") < 0 Then
      Args.Cancel = True
      EditMSG.Text = "-- Book Quantity out of range. Record not updated"
    End If
  End If

End Sub

Changed values are tested with references in the format Args.NewValues("field"), using the field names given by the DataField properties of BoundFields or the binding names in TemplateFields. If a validation test fails, then the GridView's RowUpdating event is cancelled (Args.Cancel="True") and an error message is displayed in the page's EditMSG Label. If all validation tests are passed (Args.Cancel is still False), then the record is automatically updated.

These are just a couple of the validation tests that could, and probably should, be made on user-entered data. If other fields were open to editing, then other tests are appropriate. These current checks just serve as examples of the kinds of tests that can be performed. In a production environment it is often the case that more code is written to perform data validation that to carry out actual processing performed on the data.

One final subprogram supports current GridView updating. The following Display_Message subprogram is called on a RowUpdated event (when the RowUpdating event has not been cancelled). It displays a record-updated message in the EditMSG Label.

Sub Display_Message (Src As Object, Args As GridViewUpdatedEventArgs)
	
  EditMSG.Text = " Record " & Args.Keys("BookID") & " updated"

End Sub

Again, caution is needed to match the event handler, OnRowUpdated in this case, with the appropriate argument, GridViewUpdatedEventArgs for this subprogram.

GridView Delete Events and Handlers

In addition to record editing, a GridView supports record deletion. By coding its AutoGenerateDeleteButton="True" property, a "Delete" text button is displayed in a column of the grid that, when clicked, deletes the associated record. You can, if needed, create a "Delete" button without an accompanying "Edit" button. The default appearance of the "Delete" button is shown below.

Complete coding for the above GridView, AccessDataSource, and script processing is shown below.

<SCRIPT Runat="Server">

Sub Display_Message (Src As Object, Args As GridViewDeletedEventArgs)

  EditMSG.Text = "Record " & Args.Keys("BookID") & " deleted"

End Sub

</SCRIPT>

<form Runat="Server">

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"

  SelectCommand="SELECT BookID, BookType, BookTitle, BookAuthor,
                 BookPrice, BookQty, BookSale FROM Books 
                 WHERE BookType='Database' ORDER BY BookID"
					
  UpdateCommand="UPDATE Books SET BookType=@BookType, BookTitle=@BookTitle, 
                 BookAuthor=@BookAuthor, BookPrice=@BookPrice,
                 BookQty=@BookQty, BookSale=@BookSale
                 WHERE BookID=@BookID"
	
  DeleteCommand="DELETE FROM Books WHERE BookID = @BookID"
/>

<h3>Book Edit w/Deletion</h3>

<asp:Label id="EditMSG" Text="&nbsp;" ForeColor="Red" Runat="Server"
EnableViewState="False"/>

<asp:GridView id="EditGrid" DataSourceID="BookSource" Runat="Server"
  DataKeyNames="BookID"
  AutoGenerateEditButton="True"
  AutoGenerateDeleteButton="True"
  OnRowDeleted="Display_Message"
  HeaderStyle-Font-Size="10pt"
  RowStyle-Font-Size="10pt"/>

</form>

When the record-deletion feature is added to a GridView, its associated AccessDataSource must include a DeleteCommand with an SQL DELETE statement. This is a parameterized statement using the DataKeyNames field to identify the record to be deleted.

GridView Delete Events

A GridView recognizes RowDeleting and RowDeleted events which can be trapped for subprogram calls. These events, their handlers, and their subprogram arguments are listed below.

A GridView can include OnRowDeleting and OnRowDeleted event handlers to trap and program RowDeleting and RowDeleted events. Called subprograms have the arguments GridViewDeleteEventArgs and GridViewDeletedEventArgs, respectively. The following properties are accessible in subprograms called by these two events.

--GridViewDeleteEventArgs Properties--

argument.Values("field")
argument.Keys("field")
argument.RowIndex
argument.Cancel="False|True"

--GridViewDeletedEventArgs Properties--

argument.Values("field")
argument.Keys("field")
argument.AffectedRows

The Values property returns the value in a field that is being deleted or has been deleted. The Keys property returns the value of a key field. The RowIndex property gives the row number (counting from 0) of the row being deleted. Prior to the RowDeleted event, the Cancel property can be set to True to halt record deletion. After deletion, the AffectedRows property gives the number of rows deleted.

In the example GridView, an OnRowDeleted event handler calls the Display_Message subprogram with its GridViewDeletedEventArgs argument. The subprogram displays a record-deleted message that includes the key of the deleted record—Args.Keys("BookID").

Delete Buttons

Rather than using the default "Delete" text link, an <asp:Button CommandName="Delete"> control can be coded in a TemplateField, as is done for previous edit, update, and cancel buttons. Unlike the case with update buttons, though, there is only a single delete button coded in the <ItemTemplate> of the Template Field. There is no associated <EditItemTemplate> needed because a delete button does not cause a switch to an editing row. It immediately deletes its associated row.

<asp:TemplateField 
  HeaderText="Edit">	
  <ItemTemplate>
    <asp:Button CommandName="Edit" Runat="Server"
      Text="Edit" Font-Size="8pt" Width="45px"/>
    <asp:Button CommandName="Delete" Runat="Server"
      Text="Delete" Font-Size="8pt" Width="45px"/>
  </ItemTemplate>
  <EditItemTemplate>
    <asp:Button CommandName="Update" Runat="Server"
      Text="Update" Font-Size="8pt" Width="45px"/>		
    <asp:Button CommandName="Cancel" Runat="Server"
      Text="Cancel" Font-Size="8pt" Width="45px"/>
  </EditItemTemplate>
</asp:TemplateField>

It is probably not a good idea to immediately delete a record when the "Delete" button is clicked. The button could be inadvertently clicked and cause loss of information that is difficult, if not impossible, to recreate. This is why record deletion processes often include a confirmation step asking the user to verify deletion. Delete confirmation is not built into a GridView; it must be coded and scripted. This task is taken up in a later tutorial. However, you might recognize that the separate RowDeleting and RowDeleted events offer the opportunity to insert a confirmation step between the former and latter events.

A GridView does not include a built-in feature to add records to a database. This database maintenance need can be realized with a DetailsView or FormView control, either stand-alone or in combination with a GridView. These editing controls are described in subsequent tutorials.