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 eCommerce.mdb database includes "Edit" buttons for the product 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 eCommerce.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="ProductsSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT ItemNumber, ItemType, ItemSupplier, ItemName, ItemPrice, ItemQuantity, ItemSale FROM Products WHERE ItemType = 'Graphics'" UpdateCommand="UPDATE Products SET ItemType=@ItemType, ItemSupplier=@ItemSupplier, ItemName=@ItemName, ItemPrice=@ItemPrice, ItemQuantity=@ItemQuantity, ItemSale=@ItemSale WHERE ItemNumber=@ItemNumber" /> <asp:GridView id="EditGrid" DataSourceID="ProductsSource" Runat="Server" AutoGenerateEditButton="True" DataKeyNames="ItemNumber"/>
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 ItemNumber field is the primary key for the Products 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 "Graphics" as the ItemType value. The ItemDescription field is not displayed in this example simply to reduce the amount of information appearing on this page.
The UPDATE command for updating the Products table contains database field names and associated parameters, or binding values, identified by the prefix "@".
UpdateCommand="UPDATE Products SET ItemType=@ItemType, ItemSupplier=@ItemSupplier, ItemName=@ItemName, ItemPrice=@ItemPrice, ItemQuantity=@ItemQuantity, ItemSale=@ItemSale WHERE ItemNumber=@ItemNumber"
The update specification ItemType=@ItemType, for example, gives the database field to update (ItemType) along with a reference to its update value (@ItemType). As shown in the following illustration, this update value is taken from the 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, ItemNumber is a key field. When a record is placed in edit mode, no edit box is created for this field. Therefore, the ItemNumber value cannot be changed and the update assignment ItemNumber=@ItemNumber 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 ItemNumber field in the Products table is matched by the value of the @ItemNumber parameter in the update row of the GridView (ItemNumber=@ItemNumber). 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 Products table as a paged recordset with various bound-field controls used for display and editing. For illustration purposes it permits editing of only the ItemType and ItemDescription 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 Products table. Also the UPDATE statement updates only the ItemType and ItemDescription fields. A second AccessDataSource supplies ProductTypes 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 coded edit buttons replace the automatic text buttons to edit, update, and cancel editing for a row. The ItemNumber 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="ProductsSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT * FROM Products" UpdateCommand="UPDATE Products SET ItemType=@ItemType, ItemDescription=@ItemDescription WHERE ItemNumber=@ItemNumber" /> <asp:AccessDataSource id="TypeSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT DISTINCT ItemType FROM Products ORDER BY ItemType"/> <asp:GridView id="EditGrid" DataSourceID="ProductsSource" Runat="Server" AutoGenerateColumns="False" AutoGenerateEditButton="False" DataKeyNames="ItemNumber" AllowPaging="True" PageSize="4" CellPadding="3" HeaderStyle-BackColor="#707070" HeaderStyle-ForeColor="#FFFFFF" EditRowStyle-BackColor="Yellow"> <Columns> <asp:TemplateField HeaderText="Edit" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Button CommandName="Edit" Text="Edit" Font-Size="8pt" Width="45px" Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:Button CommandName="Update" Text="Update" Font-Size="8pt" Width="45px" Runat="Server"/> <asp:Button CommandName="Cancel" Text="Cancel" Font-Size="8pt" Width="45px" Runat="Server"/> </EditItemTemplate> </asp:TemplateField> <asp:BoundField DataField="ItemNumber" ReadOnly="True" HeaderText="Number" ItemStyle-VerticalAlign="Top"/> <asp:TemplateField HeaderText="Type" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label Text='<%# Eval("ItemType") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList id="EditType" DataSourceID="TypeSource" Runat="Server" DataTextField="ItemType" DataValueField="ItemType" SelectedValue='<%# Bind("ItemType") %>'/> </EditItemTemplate> </asp:TemplateField> <asp:BoundField DataField="ItemSupplier" ReadOnly="True" HeaderText="Supplier" ItemStyle-VerticalAlign="Top"/> <asp:BoundField DataField="ItemName" ReadOnly="True" HeaderText="Name" ItemStyle-VerticalAlign="Top"/> <asp:TemplateField HeaderText="Description" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Panel Runat="Server" Width="200px" Height="30px" ScrollBars="Vertical"> <asp:Label Text='<%# Eval("ItemDescription") %>' Runat="Server"/> </asp:Panel> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditDescription" Text='<%# Bind("ItemDescription") %>' Runat="Server" TextMode="MultiLine" Rows="2" Width="200px" Font-Name="Arial"/> </EditItemTemplate> </asp:TemplateField> <asp:BoundField DataField="ItemPrice" ReadOnly="True" HeaderText="Price" ItemStyle-VerticalAlign="Top" DataFormatString="{0:N}" ItemStyle-HorizontalAlign="Right"/> <asp:BoundField DataField="ItemQuantity" ReadOnly="True" HeaderText="Qty" ItemStyle-VerticalAlign="Top" DataFormatString="{0:D}" ItemStyle-HorizontalAlign="Right"/> <asp:CheckBoxField DataField="ItemSale" ReadOnly="True" HeaderText="Sale" ItemStyle-VerticalAlign="Top"/> </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" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Button CommandName="Edit" Text="Edit" Font-Size="8pt" Width="45px" Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:Button CommandName="Update" Text="Update" Font-Size="8pt" Width="45px" Runat="Server"/> <asp:Button CommandName="Cancel" Text="Cancel" Font-Size="8pt" Width="45px" Runat="Server"/> </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 ItemNumber, ItemSupplier, ItemName, ItemPrice, ItemQuantity, and ItemSale fields. Code for the BoundField used to display the ItemNumber key field is repeated below. Other BoundField columns are similarly coded.
<asp:BoundField DataField="ItemNumber" ReadOnly="True" HeaderText="Number" ItemStyle-VerticalAlign="Top"/>
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 -- ItemSupplier, ItemName, ItemPrice, ItemQuantity, and ItemSale -- also have ReadOnly="True" settings to disallow changing their values even though they are not key fields. Recall that only the ItemType and ItemDescription fields are editable.
Editing with a TemplateField
TemplateFields are used in place of BoundFields for display of the ItemType and ItemDescription columns. The reason for using TemplateFields is for greater control over their display and editing appearances.
In the case of the ItemType field a DropDownList supplies the ItemType 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 is repeated below.
<asp:AccessDataSource id="TypeSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT DISTINCT ItemType FROM Products ORDER BY ItemType"/> ... <asp:TemplateField HeaderText="Type" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label Text='<%# Eval("ItemType") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList id="EditType" DataSourceID="TypeSource" Runat="Server" DataTextField="ItemType" DataValueField="ItemType" SelectedValue='<%# Bind("ItemType") %>'/> </EditItemTemplate> </asp:TemplateField>
The ItemTemplate displays the current ItemType value for a record. This value is displayed through a Label control bound to the database value with a standard <%# Eval("ItemType") %> binding expression.
The EditItemTemplate presents a DropDownList of ItemType values drawn from the database for choosing a replacement value. This list also preselects the current value for a record. Since this DropDownList is populated from the database, an AccessDataSource must be associated with this control to extract all unique (DISTINCT) item types from the Products table. In addition, the DropDownList preselects the type that matches the database ItemType for the particular record in the edit row. This matching selection takes place by coding a SelectedValue property for the DropDownList and binding to it the current ItemType value for the record being edited.
Binding Updatable Fields
Notice the format of this binding that takes place for the SelectedValue property of the DropDownList.
SelectedValue='<%# Bind("ItemType") %>'
In the previous Label control that simply displays the current value of the ItemType, 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.
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, the 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 ItemDescription field also contains an editable control. Its code is repeated below.
<asp:TemplateField HeaderText="Description" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Panel Runat="Server" Width="200px" Height="30px" ScrollBars="Vertical"> <asp:Label Text='<%# Eval("ItemDescription") %>' Runat="Server"/> </asp:Panel> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditDescription" Text='<%# Bind("ItemDescription") %>' Runat="Server" TextMode="MultiLine" Rows="2" Width="200px" Font-Name="Arial"/> </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("ItemDescription") %> 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 item description. The TextBox uses the binding expression <%# Bind("ItemDescription") %> and is assigned an id since changes to the description are updated in the database.
Using TemplateFields for DataKeys
In the current example, the ItemNumber 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="Number" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label Text='<%# Eval("ItemNumber") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:Label Text='<%# Eval("ItemNumber") %>' Runat="Server"/> </EditItemTemplate> </asp:TemplateField>
When a TemplateField formats a DateKeyNames field, its value normally is displayed in a Label control, which does not permit editing and makes it unnecessary to code a ReadOnly="True" property as would be the case if the value were bound to an editing area such as a TextBox. 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 ItemSale 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 ItemSale field is not updatable; therefore, it must be assigned a ReadOnly="True" property.
<asp:CheckBoxField DataField="ItemSale" ReadOnly="True" HeaderText="Sale" ItemStyle-VerticalAlign="Top"/>
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="Type" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:CheckBox Checked='<%# Eval("ItemType") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:CheckBox id="EditSale" Runat="Server"/ Checked='<%# Eval("ItemType") %>'> </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.
Update Parameters
In the present example, only the ItemType and ItemDescription fields are updatable by rewriting the record with the matching ItemNumber (primary key) value. The SQL UPDATE statement assigned to the UpdateCommand property of the AccessDataSource reflects this updating.
<asp:AccessDataSource id="ProductsSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT * FROM Products" UpdateCommand="UPDATE Products SET ItemType=@ItemType, ItemDescription=@ItemDescription WHERE ItemNumber=@ItemNumber" />
The general rule for coding the UpdateCommand is that a parameter appearing in the UPDATE clause to represent an updatable field must be one of the following:
and a parameter name appearing in the WHERE clause to signify a key field used to update the matching record must be one of the following:
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 ItemPrice and ItemQuantity values are checked for nonnumeric characters. Also, the ItemQuantity 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.
<asp:AccessDataSource id="ProductsSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT * FROM Products" UpdateCommand="UPDATE Products SET ItemType=@ItemType, ItemSupplier=@ItemSupplier, ItemName=@ItemName, ItemDescription=@ItemDescription, ItemPrice=@ItemPrice, ItemQuantity=@ItemQuantity, ItemSale=@ItemSale WHERE ItemNumber=@ItemNumber" /> <asp:AccessDataSource id="TypeSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT DISTINCT ItemType FROM Products ORDER BY ItemType"/> <asp:Label id="EditMSG" EnableViewState="False" ForeColor="Red" Runat="Server"/> <asp:GridView id="EditGrid3" DataSourceID="ProductsSource" Runat="Server" AutoGenerateColumns="False" AutoGenerateEditButton="False" DataKeyNames="ItemNumber" OnRowEditing="Edit_Row" OnRowUpdating="Validate_Data" OnRowUpdated="Display_Message" AllowPaging="True" PageSize="4" CellPadding="3" HeaderStyle-BackColor="#707070" HeaderStyle-ForeColor="#FFFFFF" EditRowStyle-BackColor="Yellow" ButtonFieldStyle-VerticalAlign="Top"> <Columns> <asp:TemplateField HeaderText="Edit" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Button CommandName="Edit" Text="Edit" Font-Size="8pt" Width="45px" Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:Button CommandName="Update" Text="Update" Font-Size="8pt" Width="45px" Runat="Server"/> <asp:Button CommandName="Cancel" Text="Cancel" Font-Size="8pt" Width="45px" Runat="Server"/> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Number" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label id="ItemNumber" Text='<%# Eval ("ItemNumber") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:Label id="ItemNumber" Text='<%# Eval ("ItemNumber") %>' Runat="Server"/> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Type" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label Text='<%# Eval("ItemType") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList id="EditType" DataSourceID="TypeSource" Runat="Server" DataTextField="ItemType" DataValueField="ItemType" SelectedValue='<%# Bind("ItemType") %>' Font-Size="8pt"/> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Supplier" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label Text='<%# Eval("ItemSupplier") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditSupplier" Text='<%# Bind("ItemSupplier") %>' Runat="Server" Font-Size="8pt" Width="80"/> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Name" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Label Text='<%# Eval("ItemName") %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditName" Text='<%# Bind("ItemName") %>' Runat="Server" Font-Size="8pt" Width="140" /> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Description" ItemStyle-VerticalAlign="Top"> <ItemTemplate> <asp:Panel Runat="Server" Width="200px" Height="30px" ScrollBars="Vertical"> <asp:Label Text='<%# Eval("ItemDescription") %>' Runat="Server"/> </asp:Panel> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditDescription" Text='<%# Bind("ItemDescription") %>' Runat="Server" TextMode="MultiLine" Rows="3" Width="200px" Font-Name="Arial" Font-Size="8pt"/> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Price" ItemStyle-VerticalAlign="Top" ItemStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Label Text='<%# FormatNumber(Eval("ItemPrice")) %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditPrice" Text='<%# Bind("ItemPrice") %>' Runat="Server" Font-Size="8pt" Width="60" MaxLength="7" Style="text-align:right"/> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Qty" ItemStyle-VerticalAlign="Top" ItemStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Label Text='<%# FormatNumber(Eval("ItemQuantity")) %>' Runat="Server"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="EditQuantity" Text='<%# Bind("ItemQuantity") %>' Runat="Server" Font-Size="8pt" Width="30" MaxLength="2" Style="text-align:right"/> </EditItemTemplate> </asp:TemplateField> <asp:CheckBoxField DataField="ItemSale" HeaderText="Sale" ItemStyle-VerticalAlign="Top"/> </Columns> </asp:GridView>
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.
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 & " being edited" 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 EditMSG 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.
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 ItemPrice and ItemQuantity 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("ItemPrice")) Then Args.Cancel = True EditMSG.Text = "-- Item Price is not numeric. Record not updated." End If If Not IsNumeric(Args.NewValues("ItemQuantity")) Then Args.Cancel = True EditMSG.Text = "-- Item Quantity is not numeric. Record not updated" End If If Args.Cancel = False Then If Args.NewValues("ItemPrice") < 0 Then Args.Cancel = True EditMSG.Text = "-- Item Price out of range. Record not updated" End If End If If Args.Cancel = False Then If Args.NewValues("ItemQuantity") < 0 Then Args.Cancel = True EditMSG.Text = "-- Item Quantity out of range. Record not updated" End If End If End Sub
Changed values are tested with references in the format Args.NewValues("FieldName"), 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("ItemNumber") & " 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.
Sub Display_Message (Src As Object, Args As GridViewDeletedEventArgs) EditMSG.Text = "Record " & Args.Keys("ItemNumber") & " deleted" End Sub <asp:AccessDataSource id="ProductsSource" Runat="Server" DataFile="../Databases/eCommerce.mdb" SelectCommand="SELECT ItemNumber, ItemType, ItemSupplier, ItemName, ItemPrice, ItemQuantity, ItemSale FROM Products WHERE ItemType = 'Graphics'" UpdateCommand="UPDATE Products SET ItemType=@ItemType, ItemSupplier=@ItemSupplier, ItemName=@ItemName, ItemPrice=@ItemPrice, ItemQuantity=@ItemQuantity, ItemSale=@ItemSale WHERE ItemNumber=@ItemNumber" DeleteCommand="DELETE FROM Products WHERE ItemNumber = @ItemNumber" /> <asp:Label id="EditMSG" EnableViewState="False" ForeColor="Red" Runat="Server"/> <asp:GridView id="EditGrid" DataSourceID="ProductsSource" Runat="Server" AutoGenerateEditButton="True" AutoGenerateDeleteButton="True" DataKeyNames="ItemNumber" OnRowDeleted="Display_Message"/>
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.
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("ItemNumber").
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> <ItemTemplate> <asp:Button CommandName="Delete" Text="Delete" Runat="Server"/> </ItemTemplate> </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.