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

SQL in Delphi (v5)

I'm trying to find a workaround for wildcard filters in an ADO table in Delphi 5.  (Code below)  The table is connected to an MS Access db.

I have one table with 3 columns of data.  Trade, Generic and Manfaucturer.  My form shows these in a dbGrid with an Edit box above used to get the user's search criteria.

Users may select which column(s) to search by checking a TCheckBox above each column.  The tag values of each checkbox (1,2,4) are summed and that result is found in a case statement that builds the Filter statement.

As users type data in the Edit1 field, the list is searched and filtered through the OnChange event giving the appearance of narrowing down the list.  These are medical terms so it's important to be able to search anywhere within the selected columns for the value of Edit1.  There about 1000 line items so the speed is acceptable.  Some columns may have more than one word such as Glaxco Smith Kline.

The problem is the space bar.  Anytime a space is entered, I get an Out of Range exception.  I think this is due the wildcard in the SQL's LIKE clause.  Any ideas how I might work this to allow spaces in the filter (especially trailing spaces as the search occurs after every keystroke )?

thanks alot

procedure TForm1.Edit1Change(Sender: TObject);
i: integer;

{don't react to a blank search field}
if (edit1.text <> 'Find What...') and (Length(edit1.text) > 0) then begin

if Generic.Checked      then i := i + Generic.tag;        {tag value = 1}
if Trade.Checked        then i := i + Trade.Tag;          {tag value = 2}
if Manufacturer.Checked then i := i + Manufacturer.tag;   {tag value = 4}

  with data do begin
  Filtered := false;
  if i=0 then i:=7;  {nothing selected is equal to everything selected}
      Case I of
      1:  Filter := '(GenericName LIKE *'+edit1.text+'*)';
      2:  Filter := '(TradeName LIKE *'+edit1.text+'*)';
      3:  Filter := '(GenericName LIKE *'+edit1.text+'*) OR (TradeName LIKE *'+edit1.text+'*)';
      4:  Filter := '(Manufacturer LIKE *'+edit1.text+'*)';
      5:  Filter := '(GenericName LIKE *'+edit1.text+'*) OR (Manufacturer LIKE *'+edit1.text+'*)';
      6:  Filter := '(TradeName LIKE *'+edit1.text+'*) OR (Manufacturer LIKE *'+edit1.text+'*)';
      7:  Filter := '(GenericName LIKE *'+edit1.text+'*) OR (TradeName LIKE *'+edit1.text+'*) OR (Manufacturer LIKE *'+edit1.text+'*)';
  Filtered := true;
  label1.caption := Filter;  {just for visual diagnostics}

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are missing some quotes, to start with:

 Filter := '(GenericName LIKE ''*'+edit1.text+'*'' )';
is your database access or sql? if sql  the * operands should be replaced with the %
dovelewisAuthor Commented:
Well, that's embarassing.  Turns out the double ' is required taher than "  but either way, it was the quotes.


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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now