Author: Lou Adler
How do I retrieve the text from a list box to add to the SQL property of a TQuery
then create both a Paradox and dBase table?
Answer:
One thing I love about Delphi is that since it's object oriented, you can perform a
lot of quick and dirty code tricks that wouldn't be possible with other languages.
For instance, the ability to assign values of like properties from one object to
another saves so much coding time. Take a list box, for example, as in your
question.
What you essentially want to do is create a SQL statement from fields listed in a
list box. If you think about it, a list box's Items property and a TQuery's SQL
property are both TStrings descendants. This means that you can do a direct
assignation between the two.
Actually, that's only half true. You have to format the fields into a proper SQL
statement format first, and that requires an intermediate TStrings object.
Luckily though, we can easily accomplish the conversion for field list to SQL
statement with a simple function. The function listed below takes a list of fields,
a Boolean value to determine whether or not the query is a DISTINCT select, and a
table name, and puts all of those together into a valid SQL statement that can
easily be
assigned to a TQuery's SQL property. Here's the listing:
1
2 {==========================================================
3 This function will create a SELECT or SELECT DISTINCT SQL
4 statement given input from a TStrings descendant like a
5 list. It will properly format the list into field decla-
6 rations of a SQL statement then, using the supplied
7 TableNm parameter, will construct an entire statement that
8 can be assigned to the SQL property of a TQuery.
9
10 Params: Distinct SELECT DISTINCT or regular SELECT
11 TableNm Table name: Should either be a fully
12 qualified table name, or preceeded by
13 an alias (ie, ':DbName:MyTable.db')
14 FieldList Any TStrings descendant will work here,
15 like the Items property of a TListBox.
16 ==========================================================}
17
18 function CreateSelect(Distinct: Boolean;
19 TableNm: string;
20 const FieldList: TStrings)
21 : TStrings;
22 var
23 Sql: TStringList;
24 I: Integer;
25 buf,
26 QueryType: string;
27 begin
28 //First, instantiate the SQL lines list
29 Sql := TStringList.Create;
30
31 //Determine whether or no this is a regular SELECT
32 //or a SELECT DISTINCT query.
33 if Distinct then
34 QueryType := 'SELECT '
35 else
36 QueryType := 'SELECT DISTINCT ';
37
38 buf := QueryType;
39
40 try
41 //Now add the fields to the select statement
42 //Notice that if we're on the last item,
43 //we don't want to add a trailing comma.
44 for I := 0 to (FieldList.Count - 1) do
45 if (I <> FieldList.Count - 1) then
46 buf := buf + FieldList[I] + ', '
47 else
48 buf := buf + FieldList[I];
49
50 //Now, put the query together
51 Sql.Add(buf);
52 Sql.Add('FROM "' + TableNm + '"');
53 Result := Sql;
54 finally
55 Sql.Free;
56 end;
57 end;
To use this, let's say you have a list box call ListBox1, and a query called
Query1. You also have a TEdit called Edit1 that holds the table name value. Here's
how you'd make the call:
58 with Query1 do
59 begin
60 Active := False;
61 SQL.Clear;
62 //This will create a SELECT DISTINCT statement
63 SQL := CreateSelect(True, Edit1.Text, ListBox1.Items);
64 Open;
65 end;
Okay, now that we've finished creating the statement and running the query, we have
to move the answers to both Paradox an dBase. This is easily accomplished with a
TBatchMove component.
Building on the previous example,. let's say you have a TBatchMove component
embedded on your form. We'll call it BatchMove1. To move the answer to a Paradox
and a dBase table, you need to use the BatchMove to move the contents of the answer
from Query1 to two new tables. The listing below lists an entire procedure that
will accomplish this:
66 procedure GetFieldsAndMove;
67 var
68 tblPdox,
69 tbldBas: TTable;
70 begin
71
72 with Query1 do
73 begin
74 Active := False;
75 SQL.Clear;
76 //This will create a SELECT DISTINCT statement
77 SQL := CreateSelect(True, Edit1.Text, ListBox1.Items);
78 Open;
79 end;
80
81 tblPdox := TTable.Create(nil);
82 with tblPdox do
83 begin
84 Active := False;
85 DatabaseName := ExtractFilePath(Application.EXEName);
86 TableName := 'MyPdoxTable';
87 TableType := ttParadox;
88 end;
89
90 tbldBas := TTable.Create(nil);
91 with tbldBase do
92 begin
93 Active := False;
94 DatabaseName := ExtractFilePath(Application.EXEName);
95 TableName := 'MydBaseTable';
96 TableType := ttDBase;
97 end;
98
99 try
100 with BatchMove1 do
101 begin
102 Source := Query1;
103 Destination := tblPdox;
104 Execute;
105 end;
106
107 with BatchMove1 do
108 begin
109 Source := Query1;
110 Destination := tbldBase;
111 Execute;
112 end;
113 finally
114 tblPdox.Free;
115 tbldBase.Free;
116 end;
117 end;
Again, this is pretty straight-forward stuff. If you need more information on the TBatchMove component, it is well-documented in the online help.
|
