Solved

SQL Formatting:

Posted on 2006-11-29
4
187 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 25

Accepted Solution

by:
dgrafx earned 500 total points
ID: 18035914
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
ID: 18036011
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
ID: 18036019
Oh GOT it..  I changed the form name  duh
 
<cfif structkeyexists(form,"spool")>
<cfset pool=1>
<cfelse>
<cfset pool=0>
</cfif>

Thanks!
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 18036023
glad I could help and good luck ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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