Reading a Field's Value into a TStrings Property

Problem/Question/Abstract:

Reading a Field's Value into a TStrings Property

Answer:

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.