Delphi sql search with both column

Question

When i try to search both column, there is no filter. i want to list 5 and 30.05.2016 in dbgrid.

adoquery1.Close;
adoquery1.SQL.CLEAR;
adoquery1.SQL.Add('select * FROM Sheet11 ');
adoquery1.SQL.Add('where field9 like :blm and field12 like :blm1');
adoquery1.Parameters.ParamByName('blm').Value:='5';
adoquery1.Parameters.ParamByName('blm1').Value:='30.05.2016';
adoquery1.Open;

What is the type of field9?

Ilyes
– Ilyes

2016-08-05 08:24:04 +00:00
Commented Aug 5, 2016 at 8:24 — Ilyes
– Ilyes, Commented Aug 5, 2016 at 8:24

Arioch 'The · Accepted Answer · 2016-08-05 14:51:10Z

1

So what is exactly the question there ? Using parameters is right, one better not use string splicing due to all kinds of fragility and unexpected side effects - http://bobby-tables.com

And use proper data types!

adoquery1.SQL.Text := 'select * FROM Sheet11 ' +
                      'where ( field9 = :_p_field9 ) ' + 
                      '  and ( field12 = :_p_field12 )';
with adoquery1.Parameters do begin
   ParamByName('_p_field9').AsInteger := 5;
   ParamByName('_p_field12').AsDateTime := EncodeDate( 2016, 05, 30 );
end;
adoquery1.Open;

edited Aug 5, 2016 at 14:51

answered Aug 5, 2016 at 7:55

Arioch 'The

16.2k39 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dsm Over a year ago

This is a better solution than mine (now deleted).

Arioch 'The Over a year ago

@Dsm well, maybe it is, maybe it is not. I still don't get what even is the problem here?..

Dsm Over a year ago

well your solution is certainly better than mine. As to what is the problem - my - I am not an expert in SQL, but don't date fields need to be surrounded by quotation marks? I would expect the original query to generate "select * from sheet11 where field9 like 5 and field 10 like 30.05.2016". But maybe I am wrong...

Arioch 'The Over a year ago

@Dsm, well the topicstarter did not word his specific problem, so we can only wander in the myst blindguessing. There are many POTENTIAL problems and which one bit his low parts - we can only guess unless he would come down to us and spill it out :-(

Arioch 'The Over a year ago

However with dates - yes, it should be presented as string literal, quoted, and here we go - different database engines would demand different quotation marks, different ad hoc patterns of string representation, and some of those would not have implicit auto-typecast, requiring explicit conversion string to date. that all CAN be his problem, or CAN be not it. We can only guess :-(

Ken White · Accepted Answer · 2016-08-05 17:38:52Z

1

You've got a pretty bad SQL error. LIKE does not work for any types other than strings (CHAR, VARCHAR, etc.). It does not work for numbers or dates. You're looking for = instead, for exact matches, or BETWEEN if you want something between two values.

This should work for you:

  adoquery1.Close;
  adoquery1.SQL.CLEAR;
  adoquery1.SQL.Add('select * FROM Sheet11 ');
  adoquery1.SQL.Add('where field9 = :blm and field12 = :blm1');
  adoquery1.Parameters.ParamByName('blm').Value:= 5; // Notice no quotes
  adoquery1.Parameters.ParamByName('blm1').Value:= '2016-05-30'; // Note no extra quotes
  adoquery1.Open;

answered Aug 5, 2016 at 17:38

Ken White

126k15 gold badges237 silver badges476 bronze badges

Collectives™ on Stack Overflow

Delphi sql search with both column

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related