Author: Sunish Issac 

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 
1   
2   ChecklistBox.Items.CommaText := GetCommaTextFromdb(table.DatabaseName, 'fieldName',
3     'Tablename');
4   
5   //possible. 
6   
7   function GetCommaTextFromdb(const Dbname, dbField, Tablename: string): string;
8   var
9     i: integer;
10    QryTemp: TQuery;
11    sFieldname: string;
12  begin
13    Result := '';
14    QryTemp := TQuery.Create(nil);
15    with QryTemp do
16    begin
17      DatabaseName := Dbname;
18      SQL.Clear;
19      SQL.Add('SELECT DISTINCT ' + dbField + ' FROM ' + Tablename);
20      Active := True;
21      First;
22      for i := 0 to QryTemp.RecordCount - 1 do
23      begin
24        sFieldname := FieldByName(dbField).AsString;
25        if (sFieldname <> '') then
26        begin
27          Result := Result + '"' + (sFieldname) + '"';
28          if i <> (QryTemp.RecordCount - 1) then
29            Result := Result + ',';
30          Next;
31        end;
32        Active := False;
33      end;
34      QryTemp.Free;
35    end;
36  
37  function GetCommaTextFromDataSet(Dataset: TBDEDataSet; dbField: string): string;
38  var
39    i: integer;
40    QryTemp: TQuery;
41    sFieldname: string;
42    BatchMove: TBatchMove;
43    TempOutTable: TTable;
44  begin
45    Result := '';
46    QryTemp := TQuery.Create(nil);
47    BatchMove := TBatchMove.Create(nil);
48    TempOutTable := TTable.Create(nil);
49    TempOutTable.TableName := 'TempOutTable';
50  
51    if Dataset is TQuery then
52      QryTemp.DatabaseName := TQuery(Dataset).DatabaseName
53    else
54      QryTemp.DatabaseName := TTable(Dataset).DatabaseName;
55  
56    TempOutTable.DatabaseName := QryTemp.DatabaseName;
57  
58    with BatchMove do
59    begin
60      Mappings.Clear;
61      Source := Dataset;
62      Destination := TempOutTable;
63      Mode := batCopy;
64      Execute;
65    end;
66  
67    with QryTemp do
68    begin
69      SQL.Clear;
70      SQL.Add('SELECT DISTINCT ' + dbField + ' FROM TempOutTable');
71      Active := True;
72      First;
73  
74      for i := 0 to QryTemp.RecordCount - 1 do
75      begin
76        sFieldname := FieldByName(dbField).AsString;
77        if (sFieldname <> '') then
78        begin
79          Result := Result + '"' + (sFieldname) + '"';
80          if i <> (QryTemp.RecordCount - 1) then
81            Result := Result + ',';
82        end;
83        Next;
84      end;
85      Active := False;
86    end;
87    TempOutTable.DeleteTable;
88    QryTemp.Free;
89    BatchMove.Free;
90    TempOutTable.Free;
91  end;