Solved

SQL Formatting:

Posted on 2006-11-29
4
169 Views
Last Modified: 2013-12-24
I am working on a search page.  There are form type text, list boxes, and checkboxes.  I was wondering I could get some suggestions on how this code is structured.  I have been testing it out and it seems like the search function works.  

Then I added the boolean types.  The checkboxes seem to not work.  When I check pool or view, it always returns an error

I am using Microsoft Access and made the checkboxes "on/off".  I thought this would work after I tried cfoutputting Form.spool and Form.sview.   They both ouput as "on"

1) How to make the checkboxes work.

2) Suggestions on the structure.

Thanks in advance.

SELECT reals_id, zip.zip, city.city, state.state, price, street, baths, beds, mls, sqft, pool, view
FROM reals, zip, city, state
WHERE (0=0)
      AND mls LIKE '#Session.mls#%'
      AND reals.city = city.city_id
      AND reals.state = state.state_id
      AND reals.zip = zip.zip_id
      AND reals.price >= #Session.minprice#
      AND reals.price <= #Session.maxprice#
      AND city.city LIKE '#Session.scity#%'
      AND state.state LIKE '#Session.sstate#%'
      AND zip.zip LIKE '#Session.szip#%'
      AND sqft >= #Val(Session.ssqft)#
      AND beds LIKE '#Session.sbeds#%'
      AND baths LIKE '#Session.sbaths#%'
      AND pool LIKE '#Session.spool#%'
      AND view LIKE '#Session.sview#%'
0
Comment
Question by:sjha81
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
dgrafx earned 500 total points
Comment Utility
on your action page before you run your query do this:
<cfif structkeyexists(form,"pool")>
<cfset pool=1>
<cfelse>
<cfset pool=0>
</cfif>
Do this for any checkbox form fields you have.
then in your query do:
and pool = #pool#
your error cam from the ' ' around a bit column
only use ' ' around text columns
0
 

Author Comment

by:sjha81
Comment Utility
Yeah! no errors.  

However, in the search page I check pool and view it keeps passing 0

Am i missing something?
0
 

Author Comment

by:sjha81
Comment Utility
Oh GOT it..  I changed the form name  duh
 
<cfif structkeyexists(form,"spool")>
<cfset pool=1>
<cfelse>
<cfset pool=0>
</cfif>

Thanks!
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
glad I could help and good luck ...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now