Solved

Big Query Assistance Required

Posted on 2007-11-21
24
149 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reconfigure Delphi Install? 2 61
Create a path if not exists 7 92
Not able to call Delphi XE10 dll function from Delphi 6.0 4 37
Firemonkey allowing RTL on android 6 47
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…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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