Any programming environment is not without its faults, and Delphi is no exception to
this. And while I consider myself to be one of the biggest fans of Delphi, there are still
things that are either missing or are so poorly implemented in it, that they make me want
to pull my hair out! Of those "things" there are two components that make me
rankle: The TDBLookupListBox and the TDBComboBox. On the surface, these components have
the potential to be incredibly useful. Load values from a field from one table so they can
be used in another. Unfortunately, most people, including myself, have had only marginal
success with them. It's not because they don't work, it's just that I feel they're poorly
implemented.
Typically, property names should give a good indication of what a property represents.
For instance, it's very clear in DataSet components that DatabaseName
actually means a database name. Unfortunately in the case of the DBLookup components, the
property names are a bit misleading, and it makes using these components a bit unwieldy.
For instance, both components have the properties, Field and DataField.
If you didn't know any better, you'd think that Field is the lookup field and DataField is
the field into which the lookup value is applied. Actually, the converse is true.
Furthermore, while the DBLookup components offer incredible flexibility by allowing you
specify different display fields in place of the actual data field that will be
used for inserting the value, providing these introduce a bit of complexity that while
useful, is poorly implemented by, yet again, confusing property names.
Don't get me wrong here. I actually use these components quite a bit becasue I
understand how they work and have had a lot of practice using them various applications.
But there are some applications where I don't really need lookup and insert capabilities,
only lookup capabilities. After all, the DBLookup components are for data entry, and not
all applications are data-entry applications. For instance, many of my applications are
specifically geared towards data retrieval. But for ease of use, I employ a lot of list
boxes and combo boxes based on lookup table data to aid in the selection criteria process.
When I'm ready to execute a retrieval, I'm not interested in grabbing field values from a
table, all I want to do is get the entered value in the edit boxes or the selected or
checked item(s) in a list or combo directly.
So in these cases, I employ a simple list load mechanism that reads data from a table's
field and inserts the values into some sort of TStrings property. Mind you, it doesn't
have the flexibility of a DBLookup component, but its mere simplicity makes it a much more
attractive alternative when doing pure reference types of applications. That said, you'll
probably kick me for taking so long to lead into the code, which happens to be moronically
simple.
Below are two procedures that I use to load TStrings types of properties. The first
employs a TTable to get the values, the second employes a TQuery. I'll discuss the
particulars following the code.
// ======================================================================
// This procedure will load a list box with values taken from a specific
// field in a TTable.
// ======================================================================
procedure DBLoadListTbl( dbSource, {database name}
tblSource, {table name}
fldName : String; {field name to load from}
const LBox: TStrings);{List Box on Form}
var
SourceTbl : TTable;
begin
SourceTbl := TTable.Create(Application); {Create an instance of sourceTbl}
with SourceTbl do
begin
Active := False;
DatabaseName := dbSource;
TableName := tblSource;
try
Open;
First;
while NOT EOF do begin
LBox.Add(SourceTbl.FieldByName(fldName).AsString);
Next;
end;
finally
Free;
end;
end;
end;
// =======================================================================
// This is a variant on the procedure above. Instead, it uses a TQuery
//
//
// =======================================================================
procedure DBLoadListQry(tblSource, {table name}
fldName : String; {field name to load from}
const List : TStrings); {Any TStrings}
var
qry : TQuery;
begin
qry := TQuery.Create(nil);
with qry do begin
Active := False;
DatabaseName := ExtractFilePath(tblSource);
SQL.Add('SELECT DISTINCT d."' + fldName + '" ');
SQL.Add('FROM "' + tblSource + '" d');
try
Open;
while NOT EOF do begin
List.Add(FieldByName(fldName).AsString);
Next;
end;
finally
Free;
end;
end;
end;
Now you might be wondering why in the world I have two procedures that perform almost
identical tasks. The reason for this is that with the DBLoadListTbl procedure, there is a
complete disregard for duplicate value checking. Simply put, the first procedure has the
potential to include duplicate values. The second procedure, DBLoadListQry, on the other
hand, employs a SELECT DISTINCT query to remove duplicates. I know, it could be argued
that I could probably combine the two procedures into a single one that does duplicate
checking, but why bother? While it would probably be much more elegant to do something
like that, sometimes just sheer simplicity makes for a much more attractive path to
follow. So rather than create a procedure that has a bunch of duplication checking logic,
I employ two procedures: One that allows duplicates, another that disallows duplicates.
Both of these calls are quick, painless, and don't require a lot thought to implement. And
in today's world of short deadlines, I'll take the most simple road over the more complex,
elegant solution any day.
Copyright © 1997 Brendan V. Delumpa All Rights Reserved
|