Get field values of a dataset as comma text

Problem/Question/Abstract:

How to get field values of a dataset as comma text ?
Getting the unique field values (strings of course) as comma text can be a big advantage in populating any TStrings descendant. The following functions implement it with respect to a table and also on TBDEDataset.

Answer:

Getting the unique field values (strings of course) as comma text can be a big advantage if you want to fill in a List box or CheckedListBox or for that matter a PickList of DBGrid.

Here are two functions that will let you get the field values as CommaText.The first one gets it from a table given the databasename ,tablename and field name. The second function retrieves it from a TBDEDataSet given the dataset  and field name. The components used in the functions are created at runtime so you don't require a component to be added to the form per se, but the respective units should be added in the uses clause.

The idea is to use a query to get just the required field values. A for loop is used to concatenate the values with a comma in between. The use of DISTINCT in the SQL ensures that there are no repeated entries.
The second function, which works with a dataset, uses a BatchMove component to move the data to a table and then does the function of creating a commatext string.

The Commatext can be assigned to any TStrings descendant making stuff like

ChecklistBox.Items.CommaText := GetCommaTextFromdb(table.DatabaseName, 'fieldName',
  'Tablename');

possible.

function GetCommaTextFromdb(const Dbname, dbField, Tablename: string): string;
var
  i: integer;
  QryTemp: TQuery;
  sFieldname: string;
begin
  Result := '';
  QryTemp := TQuery.Create(nil);
  with QryTemp do
  begin
    DatabaseName := Dbname;
    SQL.Clear;
    SQL.Add('SELECT DISTINCT ' + dbField + ' FROM ' + Tablename);
    Active := True;
    First;
    for i := 0 to QryTemp.RecordCount - 1 do
    begin
      sFieldname := FieldByName(dbField).AsString;
      if (sFieldname <> '') then
      begin
        Result := Result + '"' + (sFieldname) + '"';
        if i <> (QryTemp.RecordCount - 1) then
          Result := Result + ',';
        Next;
      end;
      Active := False;
    end;
    QryTemp.Free;
  end;

function GetCommaTextFromDataSet(Dataset: TBDEDataSet; dbField: string): string;
var
  i: integer;
  QryTemp: TQuery;
  sFieldname: string;
  BatchMove: TBatchMove;
  TempOutTable: TTable;
begin
  Result := '';
  QryTemp := TQuery.Create(nil);
  BatchMove := TBatchMove.Create(nil);
  TempOutTable := TTable.Create(nil);
  TempOutTable.TableName := 'TempOutTable';

  if Dataset is TQuery then
    QryTemp.DatabaseName := TQuery(Dataset).DatabaseName
  else
    QryTemp.DatabaseName := TTable(Dataset).DatabaseName;

  TempOutTable.DatabaseName := QryTemp.DatabaseName;

  with BatchMove do
  begin
    Mappings.Clear;
    Source := Dataset;
    Destination := TempOutTable;
    Mode := batCopy;
    Execute;
  end;

  with QryTemp do
  begin
    SQL.Clear;
    SQL.Add('SELECT DISTINCT ' + dbField + ' FROM TempOutTable');
    Active := True;
    First;

    for i := 0 to QryTemp.RecordCount - 1 do
    begin
      sFieldname := FieldByName(dbField).AsString;
      if (sFieldname <> '') then
      begin
        Result := Result + '"' + (sFieldname) + '"';
        if i <> (QryTemp.RecordCount - 1) then
          Result := Result + ',';
      end;
      Next;
    end;
    Active := False;
  end;
  TempOutTable.DeleteTable;
  QryTemp.Free;
  BatchMove.Free;
  TempOutTable.Free;
end;