Solved

Big Query Assistance Required

Posted on 2007-11-21
24
142 Views
Last Modified: 2010-04-21
Hi Experts,

I know I have another opened question which has to do with this but I have a problem putting it all together.

This is want I'm trying to do:

I have 4 ComboBoxes

1. MAKE
2. MODEL
3. MAX PRICE
4. POSTCODE

By default I'm set the comboBox Text String to ANY

My biggest problem here is going to be getting a queries for all combinations.

The way I started doing it is by using an IF Statement

For example:

If ComboBox1.text = 'ANY' then
DoQuery
also
If ComboBox1.text<>'ANY' then
DoAnotherQuery

Etc...

And this goes for all the others.

Is this the best way to do this?

Also, with the last ComboBox (PostCode) I want to search the First 3 Digits of the postcode and not the full 6 Digits

I guess this is complex, but if you guys cannot help NO-ONE can....So I'm giving MAX Points possible for it.

Thanks

ST3VO


if
0
Comment
Question by:ST3VO
  • 12
  • 9
  • 3
24 Comments
 
LVL 6

Expert Comment

by:bokist
Comment Utility
Hi !
One of possible solutions :


with ADOQuery1 do

   begin

   Close;

   SQL.Clear;

   SQL.Add('Select * from motors where ');

   if copy(AnsiUpperCase(ComboBox1.text),1,3) <> 'ANY' then

      begin

      SQL.Add('Make = :p1');

      Parameters.Parambyname('p1').value := ComboBox1.text;

   end;

   if copy(AnsiUpperCase(ComboBox2.text),1,3) <> 'ANY' then

      begin

      if pos('where', SQL.Text) = 0 then SQL.Add(' where Model like :p2')

      else                               SQL.Add('   and Model like :p2');

      Parameters.Parambyname('p2').value := ComboBox2.text + '%';

   end;

   if copy(AnsiUpperCase(ComboBox3.text),1,3) <> 'ANY' then  //supposing price is string !!

      begin

      if pos('where', SQL.Text) = 0 then SQL.Add(' where Price like :p3')

      else                               SQL.Add('   and Price like :p3');

      Parameters.Parambyname('p3').value := ComboBox3.text + '%';

   end;

   if copy(AnsiUpperCase(ComboBox4.text),1,3) <> 'ANY' then

      begin

      if pos('where', SQL.Text) = 0 then SQL.Add(' where left(postcode,3) = :p4')

      else                               SQL.Add('   and left(postcode,3) = :p4');

      Parameters.Parambyname('p4').value := copy(ComboBox4.text,1,3);

   end;

   Open;

end;

Open in new window

0
 
LVL 6

Expert Comment

by:bokist
Comment Utility
oops, small mistake : line 5-10 corrected
 
   SQL.Add('Select * from motors');
   if copy(AnsiUpperCase(ComboBox1.text),1,3) <> 'ANY' then
      begin
      SQL.Add(' where Make = :p1');
      Parameters.Parambyname('p1').value := ComboBox1.text;
   end;

--sorry--
0
 
LVL 19

Accepted Solution

by:
MerijnB earned 500 total points
Comment Utility
why don't you split the generating of the where clause up?
See the example, it's not working, just to illustrate the idea...
var WhereClause: string;

    WhereClauseParts: TStringList;

    i: integer;

    Query: string;

begin

 WhereClauseParts := TStringList.Create();
 

 // check make combobox

 if ComboBox1.Text <> 'ANY' then

  WhereClauseParts.Add(Format('(Make = %s)', [QuotedStr(ComboBox1.Text)]));
 

 // check model combobox

 if CombobBox2.Text <> 'ANY' then

  WhereClauseParts.Add(Format('(Model like "%%%s%%")', [ComboBox2.Text]));
 

 // check price combobox

 if ComboBoxPrice.Text <> 'ANY' then

  WhereClauseParts.Add(Format('(Price <= %s)', [ComboBoxPrice.Text]));
 

 // check postcode combobox

 if ComboBoxPostcode.Text <> 'ANY' then

  WhereClauseParts.Add(Format('(Postcode like "%s%%")', [Copy(ComboBoxPostcode.Text, 1, 3)]));
 

 // now combine all where clauses

 if WhereClauseParts.Count > 0 then

 begin

  WhereClause := ' where';

  for i := 0 to WhereClauseParts.Count - 1 do

  begin

   WhereClause := WhereClause + ' ' + WhereClauseParts[i];
 

   if i < WhereClauseParts.Count - 1 then

    WhereClause := WhereClause + ' AND';

  end;

 end;
 

 WhereClauseParts.Free();
 

 // now we have the where clause, make the rest of the query

 Query := 'select * from mytable' + WhereClause;

end;

Open in new window

0
 

Author Comment

by:ST3VO
Comment Utility
MerijnB:

Your code compiles but I get the error below when I run the query

Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', or 'UPDATE'

Any ideas???
0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
what is the final result (query) you get?
0
 

Author Comment

by:ST3VO
Comment Utility
It doesn't go ahead and execute the query.

Messagebox:

Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', or 'UPDATE'

Comes out and doesn't continue.
0
 

Author Comment

by:ST3VO
Comment Utility
Coould it be that after your code...I have this:

if not sCheckBox1.Checked then
   begin
  ADOQuery1.SQL.Add('UNION  ALL SELECT TOP 200 * from motors where Make <> "' + (ComboBox1.Text) + '"');
   end;

  ADOQuery1.Active := true;
   ADOQuery1.Open;

   if ADOQuery1.EOF  then
  begin
    sMessageDlg('Sorry No Matches Found! Please try another search!', mtInformation, [mbOK], 0);

  Exit;
end;  


0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
I mean the query (string) you sent to the DB
0
 
LVL 6

Expert Comment

by:bokist
Comment Utility
Pehaps it's worth to try my solution also.
0
 

Author Comment

by:ST3VO
Comment Utility
I just run the code you posted :o/

Query := 'select * from mytable' + WhereClause;

I'm I being a n00b again? :o(

0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
nope, but I can't see what's in the different comboboxes.

so what is in Query after you did:

Query := 'select * from mytable' + WhereClause;
0
 

Author Comment

by:ST3VO
Comment Utility
Actually the query was:

Query := 'select * from motors' + WhereClause;
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
post the contents of Query
0
 

Author Comment

by:ST3VO
Comment Utility
I think I don't understand what I needd to do here...

Could you post a dummy Query so I can see please?
0
 

Author Comment

by:ST3VO
Comment Utility
Something like this???

 ADOQuery1.SQL.Add('select * from motors where (Make) = "' + (sco.Text) + '"');
0
 

Author Comment

by:ST3VO
Comment Utility
Sorry....I should have posted this here:

procedure TForm1.Button12Click(Sender: TObject);
 var WhereClause: string;
    WhereClauseParts: TStringList;
    i: integer;
    Query: string;
begin
   
 
  ADOQuery1.active := false;
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
 
  /////////////////////////////////////////////////////////////
 
 
begin
 WhereClauseParts := TStringList.Create();
 
 // check make combobox
 if sco.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Make = %s)', [QuotedStr(sco.Text)]));
 
 // check model combobox
 if sModels.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Model like "%%%s%%")', [sModels.Text]));
 
 // check price combobox
 if sPrice.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Price <= %s)', [sPrice.Text]));
 
 // check postcode combobox
 if sPostCode.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Postcode like "%s%%")', [Copy(sPostCode.Text, 1, 3)]));
 
 // now combine all where clauses
 if WhereClauseParts.Count > 0 then
 begin
  WhereClause := ' where';
  for i := 0 to WhereClauseParts.Count - 1 do
  begin
   WhereClause := WhereClause + ' ' + WhereClauseParts[i];
 
   if i < WhereClauseParts.Count - 1 then
    WhereClause := WhereClause + ' AND';
  end;
 end;
 
 WhereClauseParts.Free();
 
 
 
 // now we have the where clause, make the rest of the query
 Query:=('select * from motors' + WhereClause);
end;
 
 
 
 
 
 ////////////////////////////////////////////////////////////////
 
   if not sCheckBox1.Checked then
   begin
  ADOQuery1.SQL.Add('UNION  ALL SELECT TOP 200 * from motors where Make <> "' + (sco.Text) + '"');
   end;
 
//  ADOQuery1.Active := true;
 //  ADOQuery1.Open;
 
   if ADOQuery1.EOF  then
  begin
    sMessageDlg('Sorry No Matches Found! Please try another search!', mtInformation, [mbOK], 0);
 
  Exit;
end;
 
 
 
 
end;
0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
I don't see you use the contents of the variable Query anywhere.
0
 

Author Comment

by:ST3VO
Comment Utility
Query:=('select * from motors' + WhereClause);

Also Tried:

 Query:=('select * from motors' + WhereClause + '(Make) = "' + (sco.Text) + '"');

But got no results back....Hmmm
0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
the variable Query is only a string, it doesn't do anything by itself.

You'll need to execute the string on the database to get anything back from it

probably something like:
ADOQuery1.SQL.Add(Query);
0
 

Author Comment

by:ST3VO
Comment Utility
Right!!!!

Got it now!!! :o)   Sorry for being sooo Damn!

The only thing that I cannot get to work is the Price.

I get an error when it's not ANY

Error is: Data Type mismatch in ceteria expression...It is to do with the fact that the Price is actually a String?
0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
please post the contents of the field query (the sql query you actually sent to the DB) and the type definition of the field price in the database
0
 

Author Comment

by:ST3VO
Comment Utility
It's a CSV file it's reading the data from.

That's the main problem with Price :o/
0
 
LVL 19

Expert Comment

by:MerijnB
Comment Utility
can you post some records from the CSV file?
0
 

Author Closing Comment

by:ST3VO
Comment Utility
Sorted!  I managed to change the data type to Integer!  
Thanks a million for your help!!!!
Works perfect!!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now