4 Replies Latest reply on Sep 10, 2007 12:39 PM by Newsgroup_User

    Why SQL WHERE not working

    traq
      Can anyone help. Why is this WHERE clause not working.

      The results retrived from the SQL database depend on selections made in an online html form. The user can select one or all options. The problem seems to be the OR Requestedby LIKE 'varReqBy%'. If I use OR (as shown) only data selected from the ReqBy variable is correctly retrieved any other selection retrieves all data. Using AND retrieves nothing from the ReqBy variable but all others work fine. I need to keep the LIKE and % wildcard elements as this is a free text box.

      Is there anyone out there that can help. I search all over the web but all instances I found seem to suggest that it should work. What am I missing?

      Here's my code

      SELECT *
      FROM dbo.adhoc
      WHERE Ref = 'varRef' OR ActionedTo = 'varActTo' OR Urgency = 'varUrg' OR Status = 'varStat' OR Requestedby LIKE 'varReqBy%'
      ORDER BY dateofRequest desc

      Ps the form uses a post method.
        • 1. Re: Why SQL WHERE not working
          bregent Most Valuable Participant
          It's not clear to me what you are asking. Can you provide some examples of data and query parms?
          • 2. Re: Why SQL WHERE not working
            Level 7
            With a set of ORs like that, only one of them has to be true.
            If you put nothing into the varReqBy parameter, then all records will be
            returned because LIKE '%' returns all non-NULL values.


            "traq" <webforumsuser@macromedia.com> wrote in message
            news:fbrju4$h2m$1@forums.macromedia.com...
            > Can anyone help. Why is this WHERE clause not working.
            >
            > The results retrived from the SQL database depend on selections made in an
            > online html form. The user can select one or all options. The problem
            > seems to
            > be the OR Requestedby LIKE 'varReqBy%'. If I use OR (as shown) only
            > data
            > selected from the ReqBy variable is correctly retrieved any other
            > selection
            > retrieves all data. Using AND retrieves nothing from the ReqBy variable
            > but all
            > others work fine. I need to keep the LIKE and % wildcard elements as
            > this is
            > a free text box.
            >
            > Is there anyone out there that can help. I search all over the web but
            > all
            > instances I found seem to suggest that it should work. What am I missing?
            >
            > Here's my code
            >
            > SELECT *
            > FROM dbo.adhoc
            > WHERE Ref = 'varRef' OR ActionedTo = 'varActTo' OR Urgency = 'varUrg' OR
            > Status = 'varStat' OR Requestedby LIKE 'varReqBy%'
            > ORDER BY dateofRequest desc
            >
            > Ps the form uses a post method.
            >


            • 3. Re: Why SQL WHERE not working
              traq Level 1
              Brilliant Lionstone just changed the default value or varReqBy from % to 1 and it works. FAB Thank you very much.
              • 4. Re: Why SQL WHERE not working
                Level 7
                NP. Sometimes it just takes an extra set of eyes.

                "traq" <webforumsuser@macromedia.com> wrote in message
                news:fc3j0h$dvg$1@forums.macromedia.com...
                > Brilliant Lionstone just changed the default value or varReqBy from % to
                > 1 and it works. FAB Thank you very much.