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 use SQL in combination with a TCheckListBox 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
27-Aug-02
Category
VCL-General
Language
Delphi 2.x
Views
54
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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.

			
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