Mega Code Archive
How to get field values of a dataset as comma text
<![CDATA[
Title: How to get field values of a dataset as comma text ?
Question: 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;
]]>