Some SQL/ASP works and other similar bits do not. Please help, sob!

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
 
S

Si

thorpe said:
that is some messy code. what exactly IS the error you speak of?

I thought it was concise for what it had to do, how can I improve on it?
It may be the wrapping of the message.

I do have to separate out the query to loop through the multiple ID for
the same field name, but always open to suggestions for improvement.

But, to answer your question, if you where to select just a single cat
for say property type, you get the expected result, the same as for
property condition. if you do this for property situation, all DB fields
are returned.

Also, I have to point out for anyone that may comment, I am aware that I
should not be using tblName.* , I have just stripped this page to a very
general form whilt I try to get it working.

All combo's work for the search except the property situation which just
behaves like it's not there.

Si
 
S

Si

thorpe said:
that is some messy code. what exactly IS the error you speak of?

My previous OT response aside, why exactly is the code messy.
I only ask, as for so long I havn't used NG's for exactly this sort of
response.

I thought the whole idea was to help and assist where it's due, rather
than just make sweeping statements without comment and advice.

How can I improve if I receive comments that just put me off using the
group for help again. Don't get mw wrong, I don't want to start a flame
war or anything like that, but should errors be pointed with reason?
rather than critism, how can anyone learn?

Or shall I forget NG's and carry on bodging things! Although they mostly
work. Aprat from slowing the server or being haphazard with if, else
statements etc. I think you get my point.

I want to learn! and do things as they should be done.
Please don't put me off asking!

Thanks for your time anyway

Si

PS, my sessions on the server have showed that none have been created
until the time of this post! So how can a comment like this be made
about the point of my error when no test was done, I thought URL's where
thought of as a good things for real time testing.

Fingers burnt I think by this post.
 
C

CJM

Si,

I think you might be over-reacting a little.

You didnt specify clearly what the problem was, ie if there was an error, or
in what way the page wasnt working. Plus the code snippets are a bit on the
cumbersome side. I'm almost certain that you can streamline your code a
little.

From what I understand, you are saying that the search page works fine,
except that it ignores the Property Situation checkboxes?

The first issue is to find out what is being returned when the form is being
submitted:

Response.Write Request.Form
Response.End

This should show you all that is being returned - and through this you
should be able to confirm that your page is returning an appropriate value
for SituationID.

What DB are you using? Which ever one it is, I'm sure it will have a Query
Analyzer or equivalent tool.

The next step is to output the SQL that you are using:

Response.Write sql
Response.End

Does this appear to have athe SituationID included appropriately?
If so, throw this into your QA program, and see what it returns.

In simple, you need to determine whether your HTML is failing, your ASP is
dodgy or the SQL is the problem...

Post back when you have some of this information....


Chris
 
B

Bob Barrows [MVP]

Si said:
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
You cannot debug a sql statement without knowing what it is. Use

Response.Write sql

during the debugging process to determine what your root problem is. Then
get back to us if that does not help you figure out your problem. (We will
need to see what the actual sql statement is that is returning the incorrect
results. The vbscript code that is supposed to result in the sql statement
is secondary.)

In the meantime, you should investigate passing parameters to a stored
procedure (if SQL Server) or saved parameter query (if Jet) instead of using
the unsafe and inefficient dynamic sql you are using.

Bob Barrows

PS. Never ask a database/query-related question without telling us what
database type and version you are using. This is always relevant.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top