Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    7

    Unanswered: exclude a field from a wildcard search (was "A simple question! (heeelp)")

    Hey guys, i'm new in SQL and i have a very enervating problem.
    I have a form and with some fields.I want users to be able to write just a part of a word in order to search for it (except the first field) .So,as you can see in the querie below i have put the "%" symbol as i know in all fields except tha first one.
    So,this querie works only for the first field. If i put the "%" in the first field too the all the fields are ok.
    The point is that I don't want the first field to search that way (with "%")...What should i do?
    Thanks in advance!


    strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID") & "' and "
    strQ = strQ & " Surname LIKE '%" & Request.Form.Item("surname") & "%' and"
    strQ = strQ & " Name LIKE '%" & Request.Form.Item("name") & "%' and"
    strQ = strQ & " IdentityNo LIKE '%" & Request.Form.Item("IdentityNo") & "%' and"
    strQ = strQ & " Address LIKE '%" & Request.Form.Item("Address") & "%' and"
    strQ = strQ & " Area LIKE '%" & Request.Form.Item("Area") & "%' and"
    strQ = strQ & " Zip LIKE '%" & Request.Form.Item("Zip") & "%' and"
    strQ = strQ & " PhoneNo LIKE '%" & Request.Form.Item("PhoneNo") & "%' and"
    strQ = strQ & " CellNo LIKE '%" & Request.Form.Item("CellNo") & "%' and"
    strQ = strQ & " Email LIKE '%" & Request.Form.Item("Email") & "%' and"
    strQ = strQ & " AddrNo LIKE '%" & Request.Form.Item("AddrNo") & "%';"

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Rather than put every column in the query you should perhaps consider testing if the requestform value has a value in. it is pointless including columns in the where clause if your setting will return all / any rows already.

    Code:
    eg ' not knowing the language you are using, but guessing at VB or one of its realtives....
    
    strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID")
    if len(request.form.item("surname"))>0 then
      strQ = strQ & " AND Surname LIKE '%" & Request.Form.Item("surname") & "%'"
    endif
    if len(request.form.item("name"))>0 then
      strQ = strQ & " AND Name LIKE '%" & Request.Form.Item("name") & "%'"
    endif
    
    etc......
    your code as written suggests it is expecting the user to place a value in each and every setting. You also might consider whether you are looking for an 'AND' or an 'OR' constraint ie are you looking for every occurance of Surname=Smith AND name=John or every row containing some with a Surname=Smith OR name=John
    HTH

  3. #3
    Join Date
    Dec 2004
    Posts
    7
    I use "and" cause i want the search function to operate if i have fill in one or more fields.But the problem it's not there.This works fine.

  4. #4
    Join Date
    Dec 2004
    Posts
    7
    And somethings else

    how can i see the results of COUNT?

    strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"


    P.S: I use vbscript and javascript

  5. #5
    Join Date
    Dec 2004
    Posts
    7
    Quote Originally Posted by MPSP
    And somethings else

    how can i see the results of COUNT?

    strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"


    P.S: I use vbscript and javascript
    Ok i found that (expr1000)!!!!

Posting Permissions

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