Session 6 - Editing, Deleting and Inserting Data
All the database linked controls provide mechanisms for editing and deleting data, each with its own configuration mechanism. Inserting data can be done with the DetailsView and FormView controls. The DataList and Repeater controls require a lot of work to allow editing. For the purposes of this course we will look at three examples in detail. The first, just using a GridView, is a trivial solution for performing edits and deletes. The second, using the DetailsView control, is a trivial solution for performing insertion of new records. The third integrates the previous two to provide a comprehensive and tidier solution for edit, delete and insert.
Configuring the SQLDataSource for Updates, Edits and Deletes.
In Session 5 we configured the Advanced properties for the connection to table tblStock and selected the option to 'Generate INSERT, UPDATE and DELETE statements'. If we have a simple database browsing application (i.e. we only want to see data, not edit it), we would not check need to do this. However, in preparation for this session we have preconfigured our SQLDataSource to include the necessary SQL statements and parameters to enable editing functions. In a multiuser environment there may be issues of concurrency, where potentially more than one person is trying to edit the same record. The theory for this is beyond the scope of this tutorial, but one mechanism to tackle this problem is called Optimistic Concurrency and we can choose this in the advanced options too.
Editing using a GridView - step by step
This is a simple process involving the setting of a couple of properties.
- Close all projects and copy your GridView website to a new project called GridViewEdit.
- In design view click on the GridView control on your web page.
- Click on the small triangle to the top right of the GridView control to display the Smart Tag popup. From here it is possible to configure the data source (so you can check whether the advanced options have been set).
- You will see checkboxes for Editing and Deleting (Paging, Sorting and Record Selection can also be enabled from here). Select the checkboxes you require.
- Save and run the application.
Insertion using a DetailsView - step by step
- Close all projects and Copy your DetailsView website to a new project called DetailsViewInsert.
- Open the default web form 'default.aspx'.
- Use the Smart Tag for the DetailsView control select 'Enable Inserting'.
- Amend the DefaultMode property of the DetailsView control to be Insert.
- Save your project and run it.
Note that now the DetailsView is locked in insert mode. Once you add a new record the DetailsView changes ready to accept a new record. If you set the DefaultMode to ReadOnly you will always see the current record, until you select the Insert link.
Using a DetailsView with a GridView - step by step
The previous two examples show how easy it is to cover the functionality of Edit, Delete and Insert. However, it would be nice to link the two approaches. A GridView can be used as the master view for selecting an active record, and the DetailsView can be used as the interface for editing, deleting and inserting records.
This approach is shown below and uses the GridView to present the table data and allow selection of an individual record. Selecting a record brings up that record in a DetailsView. The DetailsView has links to allow editing the record, deleting the record or inserting a new record. This example requires some C# scripts to synchronise the various elements on the web page.
- Close all projects and copy your GridView website to a new project called GridViewDetailsEdit.
- Use the SmartTab popup for the GridView to enable the Select record, Paging and Sorting options. (Do not enable Editing and Deleting)
- Drop a DetailsView control onto your web page.
- Configure a new SQLDataSource to link the DetailsView to your database, but at the 'Configure the SELECT Statement' page of the wizard you need to click the WHERE button.
- In the 'Add WHERE Clause' page you should select StockCode in the 'Column' dropdown, Control in the 'Source' dropdown and GridView1 in the 'ControlId' drop down, as in the following image.
- Now click Add and then click Cancel to exit the Add WHERE Clause dialog.
- Save your project and run it.
- Select a record by clicking the Select link by the corresponding record. The DetailsView should show the detail for that record.
- You can click Edit or Delete to perform the required action.
- If you click New in the details view a blank record is displayed for you to fill in. Click Insert to insert the new record. You will notice that the new record does not appear in the GridView. The next step solves this.
- Close Internet Explorer and select the DetailsView control.
- In the properties for the DetailsView select the Events button and double click the ItemInserted event. In the empty event handler for DetailsView1_ItemInserted type the following code:
- You need to repeat this for the ItemDeleted event and the ItemUpdated event.
- The one remaining problem with the page is that it does not show an initial record in the DetailsView. We would like the first record in the table to be selected in the GridView and displayed in the DetailsView. We can acheive this by typing the following code into the empty Page_Load event handler in the .aspx.cs file:
if (!Page.IsCallback) GridView1.SelectedIndex = 0;We should only set the initial selected record the first time the page is loaded. Succeeding calls to the page will retain the previous setting.
Note: This approach will not work if the table is initially empty.
In a real application you would probably only display the stock code and description in the GridView. This can easily be done using the Smart Tag and choosing Edit Columns.
Validation with Database controls
The automatic functionality of the database controls makes it easy to provide edit and insert functionality. However, with any user driven input mechanism it is wise to build in input validation. A simple DetailsView form set to insert records into a table can be used 'carelessly' and the default behaviour for attempting to insert invalid data is to bring up an unfriendly error page.
In this section we will look at how to handle erroneous input using validation controls. For the purposes of this we will use a simple database with one table which has a range of data fields of differing requirements. The design view for the table is shown below:
A simple DetailsView page defaulting to Insert mode can be set up as follows:
If we run this application and try to insert a record without a value for the RequiredInteger field as follows:
we get the following error page:
As you can see, unless you are an application programmer, the resulting screen is not very instructive; and you have also lost control of the application. The only option is to hit the Back button, and this can pose problems in database driven web pages.
We can use validation controls to check user input before the application attempts to insert the new record, however, it is not quite so simple as dropping a suitable validation control for each field onto the form. Database controls, like DetailsView, are by default automatically created and so the actual textboxes used when you want to edit or insert a record are hidden and so they are not available for you to link your validation controls to. You need to convert the fields you want to validate to what VWD refers to as 'templated controls'. What this means is that VWD will put actual controls on your web form, rather than hiding them. You do this by accessing the Edit Fields dialog from the SmartTag popup. As follows:
Choose the field you wish to add validation controls to and click the 'Convert this field into a Template Field' link. In our case we want to add required field validators to the RequiredString and RequiredInteger fields so we will just convert these two fields.
When VWD converts the fields to actual controls, it gives them simple names, like TextBox1, TextBox2 etc. To make it easy to keep track of we need to look in the source code and identify the controls, and perhaps give them meaningful names. The section of source code for the RequiredString field is shown below:
Text='<%# Bind("RequiredString") %>' ID="TextBox1">
Text='<%# Bind("RequiredString") %>' ID="Label1">
Text='<%# Bind("RequiredString") %>' ID="TextBox1">
You can see that the TemplateField for RequiredString has three templates: an EditItemTemplate, an ItemTemplate and an InsertItemTemplate. When we are in Insert mode the InsertItemTemplate is displayed on the form, so from the InsertItemTemplate we now know that the TextBox is called TextBox1. If we only want to use one of the templates (like this example where we will only ever use Insert mode) we can delete the others. This is also true of the other elements in the SQLDataSource, such as the SQL commands and parameters.
We can change the templates easily by selecting Edit Templates from the Smart Tag.
The Smart Tag changes to this:
We choose the InsertItemTemplate for our field from the drop down list. We can now edit our template, just like it was a simple form, and drop on the validation control, and set its properties.
Above the ID of TextBox1 has been changed to 'txtRequiredString' to make it easy to keep track of. The validation control is now linked to the textbox by amending its ControlToValidate property to the name of the TextBox. We can edit the template for the RequiredInteger field in the same way.
If we run the application and attempt to insert an empty record we will get:
Note: We have only trapped some of the potential errors with this application. We would need to put extra validation in to check for validity of the numeric values, and also the length of text, as the text fields are only defined for 50 character fields. If we do not cater for a possible error then we will get an error page when that error occurs. As well as validation controls we can also use properties for the text boxes such as MaxLength to assist in the process.
Note 2: We can't possible trap all possible errors at the client side. A duplicate key field has to be checked for on the server, so we may need to use a custom validator and an alternative mechanism for interacting with the database to make sure error pages do not arise.
Session 6 - Workshop
- Create a names and contact details database with a single table and develop a simple database web application which allows you to browse through, insert, edit and delete records in the database.
- Modify your contact details application from exercise 1 to include validation for all input. You should be able to catch all potential errors, by using combinations of validation controls, and other properties, on the fields within the record.