...

View Full Version : entries still show if status=0



startbar
07-22-2004, 12:07 PM
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

glenngv
07-22-2004, 01:37 PM
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, "'", "''") & "%' " _
...

?

startbar
07-22-2004, 08:28 PM
im not sure - im quite new to this.. its a search script for my database.. some more 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.

glenngv
07-23-2004, 03:03 AM
Try this:


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

startbar
07-23-2004, 12:26 PM
it works! fantastic, thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum