Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How to get field values of a dataset as comma text Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
30-Jun-03
Category
DB-General
Language
Delphi 2.x
Views
141
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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; 



			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC