Professional Web Applications Themes

Another saved parameter query question (MSAccess XP) - ASP Database

Trying to retrieve a recordset using a saved parameter query in Access. the query is named "resultsq1x" works fine when i run it within access: SELECT Results.ref, Results.commodity, IMPACTWEB.Field5, IMPACTWEB.Field20 FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5 WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 LIKE "*" & [searchterm] & "*"); I call this query from an ASP page like so: searchterm = Request.Form("blah") Set objConnection = Server.CreateObject("ADODB.Connection") objConnection.Open import_entry Set RS = Server.CreateObject("ADODB.Recordset") objConnection.resultsq1x searchterm, RS Do While Not RS.EOF Response.Write(RS("ref")) Response.Write(RS("Field20")) Response.Write("<BR>") RS.MoveNext Loop When I run it from a browser (after inputting the searchterm), It will return correctly if I put ...

  1. #1

    Default Another saved parameter query question (MSAccess XP)

    Trying to retrieve a recordset using a saved parameter query in
    Access. the query is named "resultsq1x"

    works fine when i run it within access:

    SELECT Results.ref, Results.commodity, IMPACTWEB.Field5,
    IMPACTWEB.Field20
    FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5
    WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 LIKE "*" &
    [searchterm] & "*");


    I call this query from an ASP page like so:

    searchterm = Request.Form("blah")
    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open import_entry
    Set RS = Server.CreateObject("ADODB.Recordset")
    objConnection.resultsq1x searchterm, RS
    Do While Not RS.EOF
    Response.Write(RS("ref"))
    Response.Write(RS("Field20"))
    Response.Write("<BR>")
    RS.MoveNext
    Loop

    When I run it from a browser (after inputting the searchterm), It will
    return correctly if I put in a "ref" number that exists.

    However, it will not return any records if I put in a string that
    exists in the "Field20" field of a record.

    If I modify the query and change: LIKE"*" & [searchterm] & "*"
    to just: LIKE [searchterm]

    then the query works correctly, returning record(s) if there is a
    string that equals the "ref" field, or if the string matches a
    complete string in the "Field20".

    Why can't i use the LIKE "*" & [searchterm] & "*" when it works
    perfectly run from within Acess?? I want to search on fields that
    just might CONTAIN the string.

    Thanks,

    Larry Rekow
    - - - - - - - - - - - - - - - - - -
    "Forget it, Jake. It's Chinatown."
    Larry Rekow Guest

  2. #2

    Default Re: Another saved parameter query question (MSAccess XP)

    Larry Rekow wrote:
    > Trying to retrieve a recordset using a saved parameter query in
    > Access. the query is named "resultsq1x"
    >
    > works fine when i run it within access:
    >
    > SELECT Results.ref, Results.commodity, IMPACTWEB.Field5,
    > IMPACTWEB.Field20
    > FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5
    > WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 LIKE "*" &
    > [searchterm] & "*");
    >
    >
    > I call this query from an ASP page like so:
    >
    > searchterm = Request.Form("blah")
    > Set objConnection = Server.CreateObject("ADODB.Connection")
    > objConnection.Open import_entry
    > Set RS = Server.CreateObject("ADODB.Recordset")
    > objConnection.resultsq1x searchterm, RS
    > Do While Not RS.EOF
    > Response.Write(RS("ref"))
    > Response.Write(RS("Field20"))
    > Response.Write("<BR>")
    > RS.MoveNext
    > Loop
    >
    > When I run it from a browser (after inputting the searchterm), It will
    > return correctly if I put in a "ref" number that exists.
    >
    > However, it will not return any records if I put in a string that
    > exists in the "Field20" field of a record.
    >
    > If I modify the query and change: LIKE"*" & [searchterm] & "*"
    > to just: LIKE [searchterm]
    >
    > then the query works correctly, returning record(s) if there is a
    > string that equals the "ref" field, or if the string matches a
    > complete string in the "Field20".
    >
    > Why can't i use the LIKE "*" & [searchterm] & "*" when it works
    > perfectly run from within Acess?? I want to search on fields that
    > just might CONTAIN the string.
    >
    I know it's counterintuitive, but when calling a query, even a saved query,
    via ADO, you have to use the ODBC wildcards (& and _) instead of the Jet
    wildcards (* and ?) in your LIKE comparisons.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

  3. #3

    Default Re: Another saved parameter query question (MSAccess XP)

    On Wed, 4 Aug 2004 22:23:44 -0400, "Bob Barrows [MVP]"
    <reb01501NOyahoo.SPAMcom> wrote:
    >I know it's counterintuitive, but when calling a query, even a saved query,
    >via ADO, you have to use the ODBC wildcards (& and _) instead of the Jet
    >wildcards (* and ?) in your LIKE comparisons.
    >
    >Bob Barrows
    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++
    counterintuitive INDEED :)

    it worked after I changed the query in Access to:

    SELECT Results.ref, Results.commodity, IMPACTWEB.Field5,
    IMPACTWEB.Field20
    FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5
    WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 Like "%" &
    [searchterm] & "%");

    sheesh. Thanks a ton. (isn't there a page that describes all of the
    varying versions of wildcards, et al? I seem to have misplaced it.)

    You would think I would know all this stuff about wildcards since,
    when coding, i'm always saying stuff like: #&$#!%

    Larry Rekow
    - - - - - - - - - - - - - - - - - -
    "Forget it, Jake. It's Chinatown."
    Larry Rekow Guest

Similar Threads

  1. MSAccess query : detect data type
    By Conti in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 29th, 09:57 PM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. MS Access d/b saved parameter query problem
    By Larry Rekow in forum ASP Database
    Replies: 3
    Last Post: August 3rd, 07:38 PM
  4. Date Parameter For Saved Parameter Queries
    By melody in forum ASP Database
    Replies: 13
    Last Post: December 19th, 09:22 AM
  5. Parameter query Help
    By Mark A. Sam in forum Microsoft Access
    Replies: 3
    Last Post: July 30th, 04:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139