Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Big Query Assistance Required

Posted on 2007-11-21
24
Medium Priority
?
160 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
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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

963 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