Office Test - Tutorial 3

We are about to start the 3rd page of the OfficeTest General Tests tutorial. If you haven't reviewed the second page of the tutorial you can find it here BillingType: Simple. Information covered in previous tutorial pages will not be covered in later pages to keep focus on new issues.

3. Field Validators, Lookup Tables and Templated Fields.

    The next web page we'll look at is the Employee: Simple page. This page was designed visually and uses 3 SqlDataSources, a GridView and a DetailsView. The GridView handles display for records from the Employee table. The DetailsView handles all editing (Insert, Update, Delete) for the Employee table.

    The overall design of this page is the same as the BillingType: Simple page. In fact the contents of the EmployeeSimple.aspx.cs file are exactly the same as the BillingTypeSimple.aspx.cs file. The Employee table links to the JobPosition table which means we have a new display and editing issue to handle. I also added some basic FieldValidator controls to make sure at the least the Not Null fields are supplied when editing. In order to implement these features we need to look at the concept of field templates.

    Employee: Simple

    Lets breakdown the important objects and property settings:

    1. SqlDataSource: dsAllEmployees

      The following is a list of the important properties.

      • SelectCommand='SELECT "EmployeeID", "FirstName", "LastName", "HireDT", E."JobPositionID", "JobTitle" FROM "Employee" E INNER JOIN "JobPosition" J on J."JobPositionID" = E."JobPositionID"'

      The configuration of the primary SqlDataSource is exactly as in the previous tutorial. The only property we need to look at is the SelectCommand. In this example I decided to perform an INNER JOIN to get the JobPosition information for each Employee. Specifically I wanted the JobTitle for display purposes. While it isn't absolutely necessary to do it this way, it does make it easier.

    2. SqlDataSource: dsCurrentEmployee

      The following is a list of the important properties.

      • SelectCommand='SELECT "EmployeeID", "FirstName", "LastName", "HireDT", E."JobPositionID", "JobTitle" FROM "Employee" E INNER JOIN "JobPosition" J on J."JobPositionID" = E."JobPositionID" WHERE ("EmployeeID" = ?)'

      Again when getting just the current employee I still want to lookup the JobTitle in the select so its available to the DetailsView during display, without requiring any special code or additional lookup.

    3. SqlDataSource: dsJobPosition

      The following is a list of the important properties.

      • SelectCommand='SELECT "JobPositionID", "JobTitle" FROM "JobPosition"'

      The EmployeeSimple page also has a third SqlDataSource, which just supplies a full list of all the JobPosition records.

    4. GridView: gvEmployee

      The only import change here is the use of a field that is bound to the JobTitle.
      <Columns>
        <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression="JobTitle" />
      </Columns>
               

      There is nothing really special about this field which is why we selected the JobTitle back in an inner join in the first place. This way just a normal BoundField could be used to display the JobPosition.JobTitle.

    5. DetailsView: dvEmployees

      The difference with the DetailsView dvEmployees lies in the Templated fields. We'll take a look at the templating for a few of the fields in detail. Before we do that lets take a look at creating templated fields. You can either create them by simply editing the HTML or they can be created visually.

      To change a normal field into a templated field first right-click on the DetailsView and select "Show Smart Tag" from the popup menu and then select "Edit Fields". This will open the Fields dialog.

      As you can see I have HireDT selected since its the only fields that is not already a template. On the bottom right of the dialog you will find a link called "Convert this field into a TemplateField". Click that link to turn HireDT or any other field into a templated field.

      Again right-click on the DetailsView and select "Show Smart Tag".

      From this dialog select "Edit Templates" from the bottom. This will open the template editing mode dialog.

      This popup allows you to select a single template for any field or you can choose the field itself. If you choose the field then you can edit all templates for that field at once. Select the EmployeeID field to get the following:

      From here you can drag and drop and select and modify controls just as you would anywhere else on the web page. If you right-click on the label in the ItemTemplate and from the popup menu select "Edit DataBindings" you will get this dialog:

      The DataBindings dialog will allow you to choose how to bind the field. If you converted an existing field the bindings are most likely already done. Otherwise you need to manually select or enter the bindings. Something you may noticed, if you are editing a template on the DetailsView, is that the top right fields under "Binding for Text" are disabled. This occurs because the select SQL for the DetailsView SqlDataSource has a parameter which is dependent on the GridView's SqlDataSource. Visual Studio cannot resolve this link without the code running and thus it can't lookup field information. This also blocks lookups of fields and auto field/code generation in other areas of the interface as well. The code will work fine when it runs but you have to type the field names or binding information manually.

      Lets take a look at the fields themselves now.
      EmployeeID

        The primary key field contains the standard 3 templates - ItemTemplate, EditItemTemplate and InsertItemTemplate.

        ItemTemplate

        <asp:Label ID="Label2" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                 

        The ItemTemplate contains only a label since this template is used for display purposes not editing. The syntax <%# Eval("EmployeeID") %> is used to retrieve the value from the EmployeeID field from the datasource and place it in the label. Eval is a one-way binding call which can only be used to retrieve values. <%# Bind("EmployeeID") %> could also be used here which provides two-way binding but since this is a label editing isn't possible anyway.

        EditItemTemplate

        <asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                 

        Since this is the primary key field it doesn't require editing and therefore the EditItemTemplate is exactly the same as the ItemTemplate.

        InsertItemTemplate

        <asp:TextBox ID="txtInsertEmployee" runat="server" Text='<%# Bind("EmployeeID") %>'></asp:TextBox>
        <br />
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtInsertEmployee" ErrorMessage="EmployeeID is required."></asp:RequiredFieldValidator>
                 

        The InsertItemTemplate is a little more complex for 2 reasons. First we need to be able to edit the EmployeeID and also because I added a validator.

        Notice that we replaced the label control with a TextBox and in this case we must use the <%# Bind("EmployeeID") %> (two-way) binding syntax. This way the value entered in the TextBox is automatically sent to the EmployeeID field to be used as a parameter in the insert SQL.

        A RequiredFieldValidator was also added to ensure the EmployeeID is not left blank when the page is posted. The validator is not necessary to make this work of course. It was just added to avoid code level validation or SQL errors. Templates provide a handy way of adding validators into your DetailsView or GridView so the error message displays beside or below the associated field.

      FirstName

        ItemTemplate
        <asp:Label ID="Label3" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
                 

        This template works the same as it does for the EmployeeID and either Eval or Bind can be used.

        EditItemTemplate

        <asp:TextBox ID="txtEditFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
        <br />
        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtEditFirstName" ErrorMessage="FirstName is required."></asp:RequiredFieldValidator>
                 

        This template works like the InsertItemTemplate does for the EmployeeID because we can edit the FirstName for updating.

        InsertItemTemplate

        <asp:TextBox ID="txtInsertFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
        <br />
        <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtInsertFirstName" ErrorMessage="FirstName is required."></asp:RequiredFieldValidator>
                 

        This template works the same as it does for the EmployeeID.

      JobPosition

        ItemTemplate
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("JobTitle") %>'></asp:Label>
                 

        This template works the same as it does for the EmployeeID and FirstName.

        EditItemTemplate

        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="dsJobPosition" DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
                 

        Now we have a new case. JobPositionID is a lookup from the JobPosition table and we want the user to be able to select a value from a list. We already have a SqlDataSource with all the JobPosition records so all we need is a DropDownList inside this template. Notice the DataSourceID is assigned to "dsJobPosition" and the binding is this case goes on the SelectedValue.

        InsertItemTemplate

        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="dsJobPosition" DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
                 

        The InsertItemTemplate template works exactly the same as the EditItemTemplate. Its actually too bad you can't just create a generic template and assign modes to it.

      HireDT

        <asp:BoundField DataField="HireDT" HeaderText="HireDT" SortExpression="HireDT" ApplyFormatInEditMode="True" DataFormatString="{0:yyyy-MM-dd}" HtmlEncode="False" />
                 

        Finally we'll take a look at the HireDT field which is not actually a template. The reason we're looking at HireDT is to show the format settings. Notice that DataFormatString is set to "{0:yyyy-MM-dd}" which formats the date as 2006-01-15. In order for this format style to work you need to remember to set HtmlEncode to "False".

This last tutorial isn't really so much a tutorial on Firebird. All the concepts covered here really apply to any database. However since I use the features described in this tutorial in other pages I wanted to make sure they were covered first.

See next Tutorial...BillingType: Editable Grid/SpreadSheet web page using a fully editable GridView more like a spreadsheet.