Solved

Select from a ADOTable

Posted on 2004-04-16
8
311 Views
Last Modified: 2010-04-05
I have a Delhi program with a ADO Connection
I have a Query with the fileds id, name, ianuary, february, ..., december
each month fileld have the value only X or O
This is for payment a tax of a TV Cable network
If the client pay then program put X in the right month (he can play for any number of month between 1 and 12)
After the pay,the user want to display clients who pay/dont pay for a number of month
How i made this selection using that
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('here is problem');
AdoQuery1.ExecSQL;
Thanks
p.s. let pretend i have  ComboBox1 and ComboBox2 who indicate me month1 and month2 (period)
Hope i was clear
0
Comment
Question by:fradolcino
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10842332

   The SQL should looks like this :
 
   "SELECT *
    FROM PAYMENTS
    WHERE MONTH BETWEEN " + ComboBox1.Text +
            " AND " + ComboBox2.Text
 
0
 

Author Comment

by:fradolcino
ID: 10842359
perhaps i don't was so much clear
i want to display me all the clients to pay between 2 month
that mean all the clients who have values x between those 2 months
Thx
0
 
LVL 17

Accepted Solution

by:
mokule earned 185 total points
ID: 10842458
Hi,
what do You think about this

const
  months: array[0..1] of string =('ianuary','february');
var
  first: Boolean;
  no: integer;
  i: integer;
begin
  ADOQuery1.SQL.Clear;
  first := True;
  ADOQuery1.SQL.Add('SELECT * FROM table WHERE ');
  for i := 0 to 11 do
    begin
    if (i >= ComboBox1.ItemIndex) and (i <= ComboBox1.ItemIndex) then
      begin
      if not first then
        ADOQuery1.SQL.Add(' OR ('+months[i]+'=''X'')')
      else
        ADOQuery1.SQL.Add('('+months[i]+'=''X'')');
      first := False;
      end;
    end;
  ADOQuery1.Open;
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10842468

   tell us something more about the DB table structure ...
0
 

Author Comment

by:fradolcino
ID: 10842560
DB Table structure
id         number
name   text
ian       chr
feb       chr
mar      chr
apr       chr
may      chr
iun        chr
iul         chr
aug       chr
sep       chr
oct        chr
nov       chr
dec       chr
that's all
months value is only X or O ( X - if the client make the payment, O - otherwise)
0
 
LVL 17

Expert Comment

by:mokule
ID: 10842598
So in my solution put

const
  months: array[0..11] of string =('ian','feb','mar',..);
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 10845250
Just in addition to Mokule, that is in the right direction:

const
  months: array[0..11] of string =('ian','feb','mar',..);
var
  i: integer;
  S: String;
begin
   for i := ComboBox1.ItemIndex to ComboBox2.ItemIndex do //i assume that the test for cb2.idx >= cb1.idx was already done
      S := S+', '+months[i]; //just to display only the desired months
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT ID, Name'+S+' FROM table WHERE ');
  for i := ComboBox1.ItemIndex to ComboBox2.ItemIndex do
    begin
    if i > ComboBox1.ItemIndex  then
        ADOQuery1.SQL.Add(' OR ('+months[i]+'=''X'')')
    else
        ADOQuery1.SQL.Add('('+months[i]+'=''X'')');
    end;
  ADOQuery1.Open;
end;
0
 
LVL 17

Expert Comment

by:mokule
ID: 10845307
Just in addition to me and Ferruccio:

const
  months: array[0..11] of string =('ian','feb','mar',..);
var
  i: integer;
  S: String;
begin
   for i := ComboBox1.ItemIndex to ComboBox2.ItemIndex do //i assume that the test for cb2.idx >= cb1.idx was already done
      S := S+', '+months[i]; //just to display only the desired months
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT ID, Name'+S+' FROM table WHERE ');
  ADOQuery1.SQL.Add('('+months[ComboBox1.ItemIndex]+'=''X'')');
  for i := ComboBox1.ItemIndex+1 to ComboBox2.ItemIndex do
    begin
    ADOQuery1.SQL.Add(' OR ('+months[i]+'=''X'')')
    end;
  ADOQuery1.Open;
end;
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
indy Tmemory stream non latin charcter issue 2 121
Intraweb download file link ? 1 156
Multiple image collision 13 81
Can Live bindings change TGrid Cell Colour ? 1 5
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question