Author: Tomas Rutkauskas
On a form, I have a TCheckListBox with records (fields code and name, strings) of a
TTable (emp.DB).
[ ] 001 - aaaaaaaaaaa
[ ] 002 - bbbbbbbbbbb
[ ] 003 - ccccccccccc
etc.
How to build an SQL with only the checked items of the CheckListBox? For example,
SELECT * FROM emp WHERE code = (???)...
Answer:
You need to inspect all of the items in the TCheckListBox and, for each one
checked, add the text of the item (with quotation marks) to a string to be used for
an IN predicate in the WHERE clause of your statement.
Written manually, your SQL statement might look like this (for the first two items
checked):
SELECT *
FROM emp
WHERE code in ("aaaaaaaaaaa", "bbbbbbbbbbb")
Done programmatically, it would look something like this:
1 var
2 InPredicate: string;
3 i: Integer;
4 begin
5 InPredicate := '';
6 with Query1 do
7 begin
8 for i := 0 to (CheckListBox1.Items.Count - 1) do
9 if CheckListBox1.Checked[i] then
10 InPredicate := InPredicate + '"' + CheckListBox1.Items[i] + '",';
11 System.Delete(InPredicate, Length(InPredicate), 1);
12 Close;
13 SQL[2] := Format('WHERE State IN (%s)', [InPredicate]);
14 Open;
15 end;
16 end;
Of course, this assumes the SQL statement starts out with a WHERE clause and this
filter will always be on the third line. At any rate, that routine demonstrates
dynamically building the values list for the IN predicate.
If you have too many items in that TCheckListBox, it might be possible to exceed the maximum length of a line in the TQuery.SQL property (255 characters). In such cases, you would need to add checking for this and account for building the filter across multiple lines in the SQL statement.
|
