SQL in Delphi (v5)

Posted on 2009-12-18
Last Modified: 2013-11-23
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}

Question by:dovelewis
    LVL 142

    Accepted Solution

    you are missing some quotes, to start with:

     Filter := '(GenericName LIKE ''*'+edit1.text+'*'' )';
    LVL 26

    Expert Comment

    is your database access or sql? if sql  the * operands should be replaced with the %

    Author Comment

    Well, that's embarassing.  Turns out the double ' is required taher than "  but either way, it was the quotes.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Number Format 1 31
    multiple SELECT statements in a function 4 32
    indexed table vs unindexed table 19 51
    SQL Select Query problems 10 37
    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now