Data Entry Input Validation With Delphi, Kylix, and C++Builder

Age : 34 Joined : 08 Mar 2008 Posts : 1264

Introduction

In this article, I will demonstrate how to build data entry forms on top of IBM DB2® Universal DatabaseTM as database tables and records, and how to perform data entry input validation using Borland DelphiTM Studio, Borland KylixTM, and Borland C++Builder® Studio.

Note that you'll use dbExpressTM again to connect to the DB2 Personal Edition v8.1 database, so make sure you've read the previous articles that describe how to connect from Delphi, Kylix, or C++Builder.

Note: When I write about Delphi in this article, you can also substitute Kylix or C++Builder and perform the same steps. When showing the source code, I will provide both Delphi (Kylix-compatible) source code and C++(Builder) code snippets.

Build the connection

In previous articles, I've mainly used a TDBGrid component to show an overview of multiple records at the same time. This time, however, I'll focus on only one record at a time that the end user can edit (for the first time) or modify. To build the example application, start Delphi, and drop TSQLConnection, TSQLDataSet, TDataSetProvider, TClientDataSet, and TDataSource components.

Set the ConnectionName property of TSQLConnection to DB2Connection. Also specify the right values in the connection parameters, such as database name, user_name, and password. Now point TSQLDataSet's SQLConnection property to the TSQLConnection component, set the CommandType to ctTable, and select the EMPLOYEE table as the value for the CommentText property. Connect TDataSetProvider's DataSet property to the TSQLDataSet component, connect TClientDataSet's ProviderName property to the TDataSetProvider component, and finally connect TDataSource's DataSet property to the TClientDataSet component.

Design the data entry forms

You can double-click on the TClientDataSet component to start the Fields Editor. If you right-click in the Fields Editor, you can add persistent fields for the EMPLOYEE table. The table has 14 fields: EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, and COMM (see Figure 1). You need them all, so add all fields in the Fields Editor.

Figure 1. Fields Editor

As a quick-and-dirty way to create data entry controls on the form, you can now "drag" the fields from the Fields Editor and "drop" them onto the form. You can do this for one or more fields at the same time. If you select them all and drag them all at once, make sure to drop at the upper-left corner of the form, because each control will be created a little lower than the previous one, and if you start too low you'll probably need to reposition them again. This produces TLabel and TDBEdit components for each of the persistent fields. A TDBNavigator component, connected through its DataSource property to the TDataSource, finishes the rough data entry form for now (see Figure 2).

Figure 2. Delphi data entry form

You need to write one line of code to make sure that any changes made to the data will be posted back to the original DB2 SAMPLE database. Select the TClientDataSet component, go to the Object Inspector, and write this code for the OnAfterPost event handler:

ClientDataSet1.ApplyUpdates(0);

For C++Builder, use this syntax (an arrow instead of a dot):

ClientDataSet1->ApplyUpdates(0);

You should also connect the OnAfterDelete event handler to the same event handler code using the Object Inspector.

Now you need to ensure that this call to ApplyUpdates (in OnAfterPost and OnAfterDelete) is executed only if the data that the end user submits is valid - that is, if the input contains no inconsistencies or incorrect values. To do this, you'll implement some input validation techniques at different levels.

Define the input validation

Some of the fields in Figure 2 have obvious restrictions in what the end user can enter. HIREDATE is a date field, which is always "fun" to allow an end user to enter without help. JOB is another string field, but from the looks of it, it should be taken from an existing list of string values ("PRES" being one of them). EDLEVEL is a small integer; SEX is a string field, probably with values "F" and "M" being the only acceptable ones; BIRTHDATE is another date field, which apart from a valid date should also be a sensible date in the past. Finally, SALARY, BONUS, and COMM are BCD fields, which probably shouldn't contain negative values. (Have you ever been asked to pay your boss to come to work?)

Mask the input fields

At the lowest level, you can define so-called input masks. For this, you must work with the individual fields that you added to the Fields Editor. String fields have an EditMask property that you can assign using the Input Mask Editor (see Figure 3), which already contains a number of predefined masks for telephone numbers and extensions, social security numbers, zip codes, dates, and times.

Figure 3. Delphi Input Mask Editor

The value for the EditMask property consists of three parts, separated by semi-colons. The first part is the actual mask, which uses 0 for a required digit, 9 for an optional digit, A for a required character, and so on (you can find details in the online help for the TEditMask topic). The second part of the mask specifies whether literals (such as brackets in phone numbers) should be included in the actual database field, and the third and final part of the mask is the dummy character used for blanks - an underscore, for example.

As an example, make sure that the end user can enter only digits for the PHONENO field: four digits, so the EditMask is set to 0000;1;_. For WORKDEPT, you can set the mask to A00;1;_ because it must start with a character followed by two digits.

You can also use an EditMask for the BIRTHDATE and HIREDATE fields, but this can lead to confusion if you plan to use the application in different countries - with different locales and hence different ways to enter a date. (yyyy/mm/dd is my personal preference, if only because it sorts easily, but I haven't yet found a country that supports this format.) I'll cover the date fields later with another solution that will support end users even more.

Sometimes the possible values are not easily placed in an EditMask. For example, the JOB field can be PRES, MANAGER, SALESREP, CLERK, ANALYST, DESIGNER, OPERATOR, or FIELDREP.

Putting these values in an EditMask will help only if the end user knows which values are possible, so a first letter can produce the rest of the value. However, I would feel better supporting the user by showing the possible values. Assuming no other JOB values exist, you can solve this problem using a TDBComboBox component.

Connect the TDBComboBox's DataSource property to the TDataSource and set the DataField to the JOB field. Enter the eight possible JOB values in the Items property (pad the values with spaces at the end until they are all eight characters long), and set the Style property to DropDownList - this will prevent the end user from selecting anything but the eight predefined values. If you want to allow entry of a new JOB, you can leave the Style value as DropDown, which means the user can either select one of the existing eight options or enter a new JOB title (obviously, it's not easy to check the validity of that user-defined input).

The SEX field provides a perfect opportunity to use a TDBRadioGroup component: drop it on the form, point the DataSource property to the TDataSource, enter "Female" and "Male" in the Items property, and "F" and "M" in the Values property. This ensures that you see the long Female and Male options, but use only F and M in the SEX field itself.

Now take a look at the BIRTHDATE and HIREDATE fields. In my view, the best way to allow the end user to specify a date is to provide a calendar in which he or she can click on the specific day. Don't confuse the end user with formatting details such as using / or - as separator character; the order of year, month, and day; and so on. Delphi offers a component called the TDateTimePicker that you can use for this purpose. It looks like a drop-down combobox showing the date value, but a month calendar will appear if the end user opens it. The user can click on the day required, as well as browse to the previous and next months, previous and next years, and so on. TDateTimePicker is quite intuitive to use.

The only disadvantage is that TDateTimePicker is not data-aware, so you have to "connect" it to the field of the ClientDataSet, and make sure that its DateTime property value is updated when the dataset changes: in an OnAfterScroll or OnAfterCancel (undo) event handler. Assuming you use two TDateTimePicker components - one for HIREDATE and one for BIRTHDATE - here's the code to respond to the TClientDataSet's OnAfterScroll and OnAfterCancel event handler: