Solved

Select from a ADOTable

Posted on 2004-04-16
8
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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