Solved

Big Query Assistance Required

Posted on 2007-11-21
24
153 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
[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
  • 12
  • 9
  • 3
24 Comments
 
LVL 6

Expert Comment

by:bokist
ID: 20327076
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
ID: 20327105
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
ID: 20327109
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
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!

 

Author Comment

by:ST3VO
ID: 20327221
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
ID: 20327249
what is the final result (query) you get?
0
 

Author Comment

by:ST3VO
ID: 20327294
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
ID: 20327310
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
ID: 20327327
I mean the query (string) you sent to the DB
0
 
LVL 6

Expert Comment

by:bokist
ID: 20327392
Pehaps it's worth to try my solution also.
0
 

Author Comment

by:ST3VO
ID: 20327420
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
ID: 20327438
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
ID: 20327446
Actually the query was:

Query := 'select * from motors' + WhereClause;
0
 
LVL 19

Expert Comment

by:MerijnB
ID: 20327467
post the contents of Query
0
 

Author Comment

by:ST3VO
ID: 20327657
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
ID: 20327674
Something like this???

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

Author Comment

by:ST3VO
ID: 20327686
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
ID: 20327779
I don't see you use the contents of the variable Query anywhere.
0
 

Author Comment

by:ST3VO
ID: 20327813
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
ID: 20327839
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
ID: 20327944
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
ID: 20327986
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
ID: 20328012
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
ID: 20328030
can you post some records from the CSV file?
0
 

Author Closing Comment

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

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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