S
Si
Hi All.
This may be DB issue, but rather that cross post, I'll post here first.
I have a page on a site that submits a form to search for various
different values in a database. It's a property site and the various
different fields searches all perform as expected apart from 1. I have
been over this so many times, I think I need a splash of cold water coz
I really cannot see the problem.
Anyway, for fear of rambling, the following page:
http://europeanpropertyportal.co.uk/Property/Search.asp allows you to
select what criteria you want, all works fine except the last bit about
property situation, this just seems to get ignored and results in a full
DB search, as if you hadn't selected anything.
I initially thought it may be a submission problem, so for the time
being I am using the generic request("") rather than form or querstring,
but the same problem still exists.
I have pasted the relevent part of the request code below to show you
what I'm doing and also am displaying the SQL query on the submit page
of the site so you can see the correctly formatted SQL.
You will se that the different request are OK, except the Situation that
doesn't even form the resulting SQL.
The following is the abridged code, any thoughts much appreciated.
Code snipped for sanity's sake
if instr(PropTypeID, ", ") <> 0 then
PropTypeID = Split(PropTypeID, ", ")
sqlPropTypeID = " AND ("
PropTypeIDCounter = 0
For PropTypeIDLoop = LBound(PropTypeID) to UBound(PropTypeID)
if PropTypeIDCounter = UBound(PropTypeID) then
sqlPropTypeID = sqlPropTypeID & "tbpProp.PropTypeID = " &
PropTypeID(PropTypeIDLoop) & ") "
else
sqlPropTypeID = sqlPropTypeID & "tbpProp.PropTypeID = " &
PropTypeID(PropTypeIDLoop) & " OR "
end if
PropTypeIDCounter = PropTypeIDCounter + 1
next
elseif PropTypeID="0" then
sqlPropTypeID = ""
else
sqlPropTypeID = " AND tbpProp.PropTypeID = " & PropTypeID & ""
end if
if request("ConditionID") = "" or request("ConditionID") = "0" then
ConditionID = "0"
else
ConditionID = request.form("ConditionID")
end if
if request("Search")<>"" then
session("ConditionID") = ConditionID
end if
if instr(ConditionID, ", ") <> 0 then
ConditionID = Split(ConditionID, ", ")
sqlConditionID = " AND ("
ConditionIDCounter = 0
For ConditionIDLoop = LBound(ConditionID) to UBound(ConditionID)
if ConditionIDCounter = UBound(ConditionID) then
sqlConditionID = sqlConditionID & "tbpProp.PropConditionID = " &
ConditionID(ConditionIDLoop) & ") "
else
sqlConditionID = sqlConditionID & "tbpProp.PropConditionID = " &
ConditionID(ConditionIDLoop) & " OR "
end if
ConditionIDCounter = ConditionIDCounter + 1
next
elseif ConditionID="0" then
sqlConditionID = ""
else
sqlConditionID = " AND tbpProp.PropConditionID = " & ConditionID & ""
end if
if request("SituationID") = "" or request("SituationID") = "0" then
SituationID = "0"
else
SituationID = request("SituationID")
end if
if request("Search")<>"" then
session("SituationID") = SituationID
end if
if instr(SituationID, ", ") <> 0 then
SituationID = Split(SituationID, ", ")
sqlSituationID = " AND ("
SituationIDCounter = 0
For SituationIDLoop = LBound(SituationID) to UBound(SituationID)
if SituationIDCounter = UBound(SituationID) then
sqlSituationID = sqlSituationID & "tbpProp.PropSituationID = " &
SituationID(SituationIDLoop) & ") "
else
sqlSituationID = sqlSituationID & "tbpProp.PropSituationID = " &
SituationID(SituationIDLoop) & " OR "
end if
SituationIDCounter = SituationIDCounter + 1
next
elseif SituationID="0" then
sqlSituationID = ""
elseif SituationID>"0" then
sqlSituationID = " AND tbpProp.PropSituationID = " & SituationID & ""
end if
Code snipped for sanity's sake
Then the SQL
sqlVars = sqlBeds & sqlSituation & sqlConditionID & sqlFeatured &
sqlCommercial & sqlPropTypeID & sqlListingType
if sqlPropLocationSearch="True" then
sql = sql & sqlPropLocation & " AND " & sqlPrice & sqlVars & "
ORDER BY " & sqlOrderBy & ";"
elseif sqlSubPropLocationSearch = "True" then
sql = sql & sqlSubPropLocation & " AND " & sqlPrice & sqlVars & "
ORDER BY " & sqlOrderBy & ";"
elseif Latest = "True" then
sql = sql & " ORDER BY " & sqlOrderBy & ";"
else
sql = sql & sqlPrice & sqlVars & " ORDER BY " & sqlOrderBy & ";"
end if
Hope someone can see my error, usually a stupid one as well.
Cheers
Si
This may be DB issue, but rather that cross post, I'll post here first.
I have a page on a site that submits a form to search for various
different values in a database. It's a property site and the various
different fields searches all perform as expected apart from 1. I have
been over this so many times, I think I need a splash of cold water coz
I really cannot see the problem.
Anyway, for fear of rambling, the following page:
http://europeanpropertyportal.co.uk/Property/Search.asp allows you to
select what criteria you want, all works fine except the last bit about
property situation, this just seems to get ignored and results in a full
DB search, as if you hadn't selected anything.
I initially thought it may be a submission problem, so for the time
being I am using the generic request("") rather than form or querstring,
but the same problem still exists.
I have pasted the relevent part of the request code below to show you
what I'm doing and also am displaying the SQL query on the submit page
of the site so you can see the correctly formatted SQL.
You will se that the different request are OK, except the Situation that
doesn't even form the resulting SQL.
The following is the abridged code, any thoughts much appreciated.
Code snipped for sanity's sake
if instr(PropTypeID, ", ") <> 0 then
PropTypeID = Split(PropTypeID, ", ")
sqlPropTypeID = " AND ("
PropTypeIDCounter = 0
For PropTypeIDLoop = LBound(PropTypeID) to UBound(PropTypeID)
if PropTypeIDCounter = UBound(PropTypeID) then
sqlPropTypeID = sqlPropTypeID & "tbpProp.PropTypeID = " &
PropTypeID(PropTypeIDLoop) & ") "
else
sqlPropTypeID = sqlPropTypeID & "tbpProp.PropTypeID = " &
PropTypeID(PropTypeIDLoop) & " OR "
end if
PropTypeIDCounter = PropTypeIDCounter + 1
next
elseif PropTypeID="0" then
sqlPropTypeID = ""
else
sqlPropTypeID = " AND tbpProp.PropTypeID = " & PropTypeID & ""
end if
if request("ConditionID") = "" or request("ConditionID") = "0" then
ConditionID = "0"
else
ConditionID = request.form("ConditionID")
end if
if request("Search")<>"" then
session("ConditionID") = ConditionID
end if
if instr(ConditionID, ", ") <> 0 then
ConditionID = Split(ConditionID, ", ")
sqlConditionID = " AND ("
ConditionIDCounter = 0
For ConditionIDLoop = LBound(ConditionID) to UBound(ConditionID)
if ConditionIDCounter = UBound(ConditionID) then
sqlConditionID = sqlConditionID & "tbpProp.PropConditionID = " &
ConditionID(ConditionIDLoop) & ") "
else
sqlConditionID = sqlConditionID & "tbpProp.PropConditionID = " &
ConditionID(ConditionIDLoop) & " OR "
end if
ConditionIDCounter = ConditionIDCounter + 1
next
elseif ConditionID="0" then
sqlConditionID = ""
else
sqlConditionID = " AND tbpProp.PropConditionID = " & ConditionID & ""
end if
if request("SituationID") = "" or request("SituationID") = "0" then
SituationID = "0"
else
SituationID = request("SituationID")
end if
if request("Search")<>"" then
session("SituationID") = SituationID
end if
if instr(SituationID, ", ") <> 0 then
SituationID = Split(SituationID, ", ")
sqlSituationID = " AND ("
SituationIDCounter = 0
For SituationIDLoop = LBound(SituationID) to UBound(SituationID)
if SituationIDCounter = UBound(SituationID) then
sqlSituationID = sqlSituationID & "tbpProp.PropSituationID = " &
SituationID(SituationIDLoop) & ") "
else
sqlSituationID = sqlSituationID & "tbpProp.PropSituationID = " &
SituationID(SituationIDLoop) & " OR "
end if
SituationIDCounter = SituationIDCounter + 1
next
elseif SituationID="0" then
sqlSituationID = ""
elseif SituationID>"0" then
sqlSituationID = " AND tbpProp.PropSituationID = " & SituationID & ""
end if
Code snipped for sanity's sake
Then the SQL
sqlVars = sqlBeds & sqlSituation & sqlConditionID & sqlFeatured &
sqlCommercial & sqlPropTypeID & sqlListingType
if sqlPropLocationSearch="True" then
sql = sql & sqlPropLocation & " AND " & sqlPrice & sqlVars & "
ORDER BY " & sqlOrderBy & ";"
elseif sqlSubPropLocationSearch = "True" then
sql = sql & sqlSubPropLocation & " AND " & sqlPrice & sqlVars & "
ORDER BY " & sqlOrderBy & ";"
elseif Latest = "True" then
sql = sql & " ORDER BY " & sqlOrderBy & ";"
else
sql = sql & sqlPrice & sqlVars & " ORDER BY " & sqlOrderBy & ";"
end if
Hope someone can see my error, usually a stupid one as well.
Cheers
Si