March 1998

Valid date entry

This isn't a difficult problem, but the answer is hard for new Visual Component Library (VCL) users to find. While researching an answer, I tried the OnValidate event of the TDateTimeField object. However, somewhere deep in the bowels of C++Builder, an error is reported and OnValidate doesn't fire. Next, I tried the OnUpdate event of TDataSource. Still no luck. So I tried BeforePost. I knew there had to be a way to solve the problem, but I couldn't find it. Then, someone on the Internet suggested using the SetText event--and it works! SetText, which is attached to the persistent field objects, fires when a new value is assigned to a TField's Text property. It's perfect for validating entries before standard system validation takes over.

To view the persistent fields, double-click on a TTable or TQuery object. If the field editor box is empty, right-click on it and add all the fields. Click on one of the persistent fields and press the [F11] key to view the properties and events for the selected persistent field.

The SetText function prototype includes two arguments--the TField object being modified and the new Text value, as follows:

void SetText(TField *Sender,const 
             AnsiString Text)

void SetText(TField *Sender,const 
             AnsiString Text)
{
Sender->AsString = Text;
}

void SetText(TField *Sender,const AnsiString Text)
{
  String NewText(Text); //copy Text to NewText
  try
  {
    TDateTime d(NewText);
    NewText = d.FormatString("mm/dd/yy");
  }
  catch(...){;} // if NewText isn't 
                // a valid date
  Sender->AsString= NewText;
}

These rules don't allow for entering the month as text (Jan, Feb, and so on). However, any other practical date entry can be validated using the following pseudo code snippets:

//Pseudo Code
Replace all non-numeric characters 
  with a slash (/).
Scrub the entry to prevent 
  double slashes (`//'). 
Delete leading and trailing slashes. 
Count the number of slashes in the entry.

 

Tip: No such thing as a standard date There is, of course, no standard date. The order of a valid date entry changes from continent to continent and country to country around the world. Fortunately, C++Builder provides SysUtils to untangle this quagmire. The following date-related system variables are available: extern char DateSeparator; extern System::AnsiString ShortDateFormat; extern System::AnsiString LongDateFormat; extern System::AnsiString ShortMonthNames[12]; extern System::AnsiString LongMonthNames[12]; extern System::AnsiString ShortDayNames[7]; extern System::AnsiString LongDayNames[7];

Performing these pseudo-code operations on the Text value assures the programmer that the resultant string will be in the format n/n/n, where n is a number and the slash represents the computer's DateSeparator character. The user can include spaces, dashes, slashes, ampersands, and/or dollar signs to delimit date components.

If the scrubbed entry contains more than two slashes, it isn't a valid date entry. If it contains only one slash, you can assume the year is missing. When no slashes survive the pseudo-code scrubbing, the user entered only the day of the month.

In the last case, when the user enters only a day, it's nice to add a little intelligence to the algorithm that will determine which month to select. It's always safe to select the current month, but early in each month, users might be catching up on last month's postings. I use the code shown in Listing A to select the month. The integer variable newday is assigned a value from the Text argument in the SetText event handler. Remember, this code executes when the scrubbed date entry consists of a single numeric.

Listing A: Selecting the month

#define MONTH_CHANGE_MIN 5
#define MONTH_CHANGE_MAX 25
unsigned short year, month, day, newday; 

TDateTime d(Date());  //default Date = Today
d.DecodeDate(&year, &month, &day); 
newday = unsigned  short(Text.ToInt());

if ((newday >= MONTH_CHANGE_MAX) 
    && (day <= MONTH_CHANGE_MIN))
{
  month--;	//use last month
  if(month == 0)	// roll to previous year
  {	month = 12;
    year--;
  }
}
else if((newday <= MONTH_CHANGE_MIN) 
        && (day >= MONTH_CHANGE_MAX))
{
  month++;	//use next month
  if(month==13)	// roll to coming year
  {	month=1;
    year++;
  }
}

Listing B: Building the date

if (ShortDateFormat[1]=='M') //format is M/d/y
{
  Text = month;
  Text += DateSeparator;
  Text += newday;
}
else  //assume day month year
{
  Text = newday;
  Text += DateSeparator;
  Text += month;
}

Listing C: Retrieving the current record number

void __fastcall f::OnChange(TObject *Sender)
{
  TDBEdit *e = dynamic_cast<TDBEdit*>(Sender);
  if (e)    //the sender is a TDBEdit
  { String s("");
    TDataSet *ds = e->DataSource->DataSet;
    if(ds)    //There is a TDataSource
      if(ds->Active)  //the dataset is active
      { s = "Record ";
        if (ds->RecNo == -1)
          s += '*';  //new record or no support
        else
          s += int(ds->RecNo);

        s += " of ";
        s += int(ds->RecordCount);
      }
      txtRecordNumber->Text = s;
  }
}

There are a couple of interesting nuances in this code. For example:

s += int(ds->RecordCount);

Table A: Some field types available in Borland's Local SQL for use in the CREATE TABLE statement

SQL syntax	BDE Logical	Paradox	dBASE
SMALLINT	fldINT16	Short	Number (6,10)
INTEGER	fldINT32	Long Integer	Number (20,4)
DECIMAL(x,y)	FldBCD	BCD	N/A
NUMERIC(x,y)	FldFLOAT	Number	Number (x,y)
FLOAT(x,y)	FldFLOAT	Number	Float (x,y)
CHARACTER(n)	FldZSTRING	Alpha	Character
DATE	FldDATE	Date	Date
BOOLEAN	FldBOOL	Logical	Logical
TIME	FldTIME	Time	N/A
MONEY	FldFLOAT, FldstMONEY	Money	Number (20,4)
AUTOINC	fldINT32, fldstAUTOINC	Autoincrement	N/A
x = precision (default: specific to driver) y = scale (default: 0) n = length in bytes (default: 0)

For a complete list, look under CREATE TABLE in the Local SQL help. For example, the following SQL syntax creates a single table with an autoincrement field as the primary key:

CREATE TABLE "employee.db"
(
NAME_ID AUTOINC,
LAST_NAME CHAR(20),
FIRST_NAME CHAR(15),
SALARY NUMERIC(10,2),
DEPT_NO SMALLINT,
STARTEDON DATE,
PRIMARY KEY(Name_ID)
)

I use the code shown in Listing D to execute action queries. There's one small problem with creating tables at runtime: If the table exists, it won't be re-created. The following code segment uses Listing D to first DROP (delete) the table and then to re-create it. DROP TABLE is an SQL command to delete tables from a database.

  String s("DROP TABLE 'employee.db'");
  ExecuteSql(s,false);//No error report

  s="CREATE TABLE 'employee.db'";
  s+="(NAME_ID AUTOINC, ";
  s+="LAST_NAME CHAR(20), ";
  s+="FIRST_NAME CHAR(15), ";
  s+="SALARY NUMERIC(10,2), ";
  s+="DEPT_NO SMALLINT, ";
  s+="STARTEDON DATE, ";
  s+="PRIMARY KEY(Name_ID))";
  ExecuteSql(s,true);  // report errors

bool ExecuteSql(String& sql, const bool 
                ShowErr)
{ bool ok = true;
  TQuery *q = new TQuery(0);
  try
  { q->Active=False;    q->SQL->Clear();
    q->DatabaseName = "ts";
    q->SQL->Add(sql);
    q->ExecSQL(); 
  }
  catch(EDBEngineError *E)
  { String m (
    "Error Executing Action Query: " + sql);
    m += E->Message;
    if(ShowErr)  ShowMessage(m.c_str());
    ok=false;
  }
  delete q;
  return ok;
}