Solved

Select from a ADOTable

Posted on 2004-04-16
8
331 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
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!

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

717 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