#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    7
    Rep Power
    0

    Question TADOQUERY "Search" not "Filter"


    Hey all I have an Access database I wish to "Search" for data in, I am using the TadoQuery Control. I am also doing SQL commands with this component ( Main Reason I am using it ) I have only found ways to Filter Data but not search through it without either filtering or searching by Scanning ( Which is VERY slow ) I need speed! I have database RecordSets that exceed 29k Records. I am using a Jet 4.0 connection. any Ideas?

    Thank you,
    Jeremy
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    Have you tried changing the SQL property of the TADOQuery at runtime.
    Code:
    with Query1 do
    begin
        Close;
        SQL.Clear;
        SQL.Add('SELECT col1, col2, col3');
        SQL.Add('FROM tablename');
        if (sLike <> '') then
            SQL.Add('WHERE col4 LIKE ''' + sLike + '%'' ');
        Open;    
    end;
    Of course, it would help if tablename has an index for col4 in the above example.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    7
    Rep Power
    0
    Thank you for your reply, the code you supplied was for Filtering the data not searching. I say Search meaning it Keeps the Whole Record on the Screen and sets the Active Record (Row) to the one found in the Search function.

    P.S. I already have the code for my SQL commands finished, which filters the data Perfectly

    Thanks Again


    Originally Posted by Scorpions4ever
    Have you tried changing the SQL property of the TADOQuery at runtime.
    Code:
    with Query1 do
    begin
        Close;
        SQL.Clear;
        SQL.Add('SELECT col1, col2, col3');
        SQL.Add('FROM tablename');
        if (sLike <> '') then
            SQL.Add('WHERE col4 LIKE ''' + sLike + '%'' ');
        Open;    
    end;
    Of course, it would help if tablename has an index for col4 in the above example.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    7
    Rep Power
    0
    After doing some research I found limitations with the TADOQuery component as of which pertain mostly to Searching. And since it does not look like I am going to be able to do what I need with any decently "Short" code I am going to modify my Application and use the Filter property along Side the Regular SQL commands. In testing works much better then I would have hoped and will function for what I need. I do appreciate your help.

    Thank you
    Jeremy

IMN logo majestic logo threadwatch logo seochat tools logo