Solved

Big Query Assistance Required

Posted on 2007-11-21
24
147 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

770 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