How to use SQL in combination with a TCheckListBox

Problem/Question/Abstract:

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:

var
  InPredicate: string;
  i: Integer;
begin
  InPredicate := '';
  with Query1 do
  begin
    for i := 0 to (CheckListBox1.Items.Count - 1) do
      if CheckListBox1.Checked[i] then
        InPredicate := InPredicate + '"' + CheckListBox1.Items[i] + '",';
    System.Delete(InPredicate, Length(InPredicate), 1);
    Close;
    SQL[2] := Format('WHERE State IN (%s)', [InPredicate]);
    Open;
  end;
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.