Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    entries still show if status=0

    Code:
    strSQL = "SELECT Name, Category, County, Date, Summary, URL, Town, ID, Status " _
    		& "FROM NETWORK " _
    		& "WHERE Status=1 LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR Name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR Category LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR Town LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR County LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "ORDER BY " & ordervariable & " " & sortorder
    all the entries on the database still show up even if their status = 0

    why is this?

    thanks

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    What's this?

    & "WHERE Status=1 LIKE '%" & Replace(strSearch, "'", "''") & "%' " _

    Do you mean

    & "WHERE Status=1 OR someOtherField LIKE '%" & Replace(strSearch, "'", "''") & "%' " _

    or

    & "WHERE Status LIKE '%" & Replace(strSearch, "'", "''") & "%' " _

    or just

    & "WHERE Status=1 " _
    & "OR Name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    ...

    ?

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    im not sure - im quite new to this.. its a search script for my database.. some more code.

    Code:
    dim ordervariable, sortorder
    ordervariable = "Category"   'default column to sort on
    sortorder = "ASC"   'default sortorder
    
    if (len(request.querystring("sortby")) >= 1) then 
         ordervariable = request.querystring("sortby")
    end if
    if (len(request.querystring("sortorder")) >= 1) then 
    sortorder = request.querystring("sortorder")
    end if
    
    strSQL = "SELECT Name, Category, County, Date, Summary, URL, Town, ID, Status " _
    		& "FROM NETWORK " _
    		& "WHERE Status=1 LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR Name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR Category LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR Town LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "OR County LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    		& "ORDER BY " & ordervariable & " " & sortorder
    
    	' Execute our query using the connection object.  It automatically
    	' creates and returns a recordset which we store in our variable.
    	Set rstSearch = Server.CreateObject("ADODB.Recordset")
    	rstSearch.PageSize  = PAGE_SIZE
    	rstSearch.CacheSize = PAGE_SIZE
    
    	' Open our recordset
    	rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText
    
    	' Get a count of the number of records and pages
    	' for use in building the header and footer text.
    	iRecordCount = rstSearch.RecordCount
    	iPageCount   = rstSearch.PageCount
    
    	If iRecordCount = 0 Then
    		' Display no records error.
    		%>
    perhaps this will put it into context.

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    Try this:
    Code:
    strSearch = Replace(strSearch, "'", "''")
    strSQL = "SELECT Name, Category, County, Date, Summary, URL, Town, ID, Status " _
    		& "FROM NETWORK " _
    		& "WHERE Status=1 " _
    		& "AND (Name LIKE '%" & strSearch & "%' " _
    		& "OR Category LIKE '%" & strSearch & "%' " _
    		& "OR Town LIKE '%" & strSearch & "%' " _
    		& "OR County LIKE '%" & strSearch & "%') " _
    		& "ORDER BY " & ordervariable & " " & sortorder

  • #5
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    it works! fantastic, thanks
    Web and Business Development from Cornwall - Startbar.co.uk


  •  

    Posting Permissions

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