• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

Assistance needed with SQL Syntax

Hi all,

I need some assistance with some code that I would like to expand on:

Here is the code:

if sModels.Text<>'ANY' then
    Begin
      ADOQuery1.SQL.Add(
        'Select * from motors where (Make) = '
        + Quotedstr(ComboBox1.text)
        + ' AND ((Model) LIKE "%'  
        + ComboBox2.text            //Fiesta
        + '%")'                     // %")
        );
     
    end;

I need to add

AND (Price) <= PriceComboBox.text

also PriceComboBox.text is a String ....Do I need to Add StrToInt for it to work???

Hope you can help

ST3VO
0
ST3VO
Asked:
ST3VO
  • 9
  • 6
  • 5
  • +1
2 Solutions
 
rfwoolfCommented:
Try this. And because you are inserting SQL as a string, you want to send it through as a string not an integer anyway.
if sModels.Text<>'ANY' then
    Begin
      ADOQuery1.SQL.Add(
        'Select * from motors where (Make) = '
        + Quotedstr(ComboBox1.text) 
        + ' AND ((Model) LIKE "%'  
        + ComboBox2.text            //Fiesta
        + '%")'                     // %")
        + ' AND ((Price) <= '
        + PriceComboBox.text
        + ')
        );
     
    end;

Open in new window

0
 
ST3VOAuthor Commented:
I'm getting a syntax error:

if sModels.Text<>'ANY' then
    Begin
      ADOQuery1.SQL.Add(
        'Select * from motors where (Make) = '
        + Quotedstr(ComboBox1.text)
        + ' AND ((Model) LIKE "%'  
        + ComboBox2.text            //Fiesta
        + '%")'                     // %")
        + ' AND ((Price) <= '
        + PriceComboBox.text
        + ') <-- HERE
        );
     
    end;

Undetermined String...
0
 
LimbeckCommented:
add a ' after the )

but also test if the price is a valid price

f.i.

var iDummy:integer;
if sModels.Text<>'ANY'  then
try
  iDummy=StrToInt(PriceComboBox.text);
      ADOQuery1.SQL.Add(
        'Select * from motors where (Make) = '
        + Quotedstr(ComboBox1.text)
        + ' AND ((Model) LIKE "%'  
        + ComboBox2.text            //Fiesta
        + '%")'                     // %")
        + ' AND ((Price) <= '
        + PriceComboBox.text
        + ') '
        );
  //etc
except
  //errorhandling
end;
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
ST3VOAuthor Commented:
Hmmm...Query won't run...Sytax Error :o/
0
 
LimbeckCommented:
what are the values for PriceComboBox.TEXT,ComboBox2.text  & ComboBox1.text ?
0
 
ST3VOAuthor Commented:
Here is the Exact error message:

Syntax error in query expression '(Make) = 'Ford' AND ((Model) LIKE "%Mondeo%")
Select * From motors where (Make)='Ford' AND ((Model) LIKE "%Mondeo%") AND ((Price) <=10000)'

Hope it helps!!!

0
 
rfwoolfCommented:
Hmm... do you know if your Price field is an integer or string or what... but it might not even matter ...

Also I suppose it wouldn't hurt to add a bracket around the first condition...


var iDummy:integer;
if sModels.Text<>'ANY'  then
try
  iDummy=StrToInt(PriceComboBox.text);
      ADOQuery1.SQL.Add(
        'Select * from motors where ((Make) = '
        + Quotedstr(ComboBox1.text) 
        + ') AND ((Model) LIKE "%'  
        + ComboBox2.text         
        + '%") AND ((Price) <= '
        + PriceComboBox.text
        + ')'
        );
  //etc
except
  //errorhandling
end;
 
 
////ALSO TRY:
 
 
var iDummy:integer;
if sModels.Text<>'ANY'  then
try
  iDummy=StrToInt(PriceComboBox.text);
      ADOQuery1.SQL.Add(
        'Select * from motors where ((Make) = '
        + Quotedstr(ComboBox1.text) 
        + ') AND ((Model) LIKE "%'  
        + ComboBox2.text         
        + '%") AND ((Price) <= "'
        + PriceComboBox.text
        + '")'
        );
  //etc
except
  //errorhandling
end;

Open in new window

0
 
ST3VOAuthor Commented:
The Query now works but the Price is not working properly!

I selected Price of 4000 and it's showing prices of 10000 etc  

Any ideas?

Thanks

ST3VO
0
 
LimbeckCommented:
what type is the field price in the database? sounds like it is a string/varchar; not a int :)
0
 
ST3VOAuthor Commented:
Yes it's a string as it's reading from a CSV file
0
 
MerijnBSr. Software EngineerCommented:
why don't you use format() ? In generally it makes your code much better readable with such complex string 'generation'
if sModels.Text<>'ANY'  then
  ADOQuery1.SQL.Add(Format('select * from motors where Make = %s AND ((Model like "%%%s%%") and ((Price <= %s)',
                           [QuotedStr(ComboBox1.Text), ComboBox2.Text, PriceCombobox.Text]);

Open in new window

0
 
LimbeckCommented:
not sure if this will work but try:

       + '%") AND (Val(Price) <= '

instead of        
      + '%") AND ((Price) <= '

0
 
ST3VOAuthor Commented:
MerijnB:

I get a Syntax error with your code see below:
Missing ),], or item in query expression 'Make = 'Ford' AND ((Model like "%Mondeo%") AND ((Price <= 4000)'

Limbeck:
Syntax  error too.

Sorry guys :o/

0
 
MerijnBSr. Software EngineerCommented:
oops...

can you check the typedef of the price table...?
if sModels.Text<>'ANY'  then
  ADOQuery1.SQL.Add(Format('select * from motors where Make = %s AND ((Model like "%%%s%%") and (Price <= %s))',
                           [QuotedStr(ComboBox1.Text), ComboBox2.Text, PriceCombobox.Text]);

Open in new window

0
 
ST3VOAuthor Commented:
Hmmm...

Type Mismatch error

0
 
MerijnBSr. Software EngineerCommented:
where do you get this error?
0
 
ST3VOAuthor Commented:
After running the Query
0
 
MerijnBSr. Software EngineerCommented:
so, this error comes from the DB, not from delphi?

Can you post the resulting query?
0
 
LimbeckCommented:
might be the "" . try it like this:

var sLike:String;
sLike:='%'+ComboBox2.text+ '%'

     + ') AND ((Model) LIKE '  
        + Qutedstr(sLike)
0
 
LimbeckCommented:
Quotedstr(sLike)
0
 
ST3VOAuthor Commented:
Here is the whole code:

Hope it helps!

Thanks again

ST3VO

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;

Open in new window

0
 
MerijnBSr. Software EngineerCommented:
I don't see where you use the contents of the string 'Query'?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 9
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now