3 Drop down search boxes (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

mjmtravel -> 3 Drop down search boxes (2/15/2007 21:06:53)

I have 3 drop down search boxes and I want to search a table containing the 3 columns in the search boxes, but I want the results to match the criteria in the drop down search boxes. I have the search working but I believe I have not figured out the and/or problem. Some searches come back with the same results even though the criteria is different. Also, how to I insert the word any in the table to show as default in the search box and have the search ignore the drop down totally. Check out this page and you will see what I'm trying to achieve. I only have samples in bonaire and cancun. http://palmislandcondos.com/condosearch.asp
Thanks for your help.



BeTheBall -> RE: 3 Drop down search boxes (2/15/2007 21:45:16)

First of all, I would put the Any option as the first option in each dropdown. You then need to build your select statement using IF/THEN statements. For example, you start with this:

mySQL = "SELECT * FROM tableName WHERE Island = '" & Request.Form("Island") &"'"

Then, you analyze the other dropdowns and add them to the SQL if they apply:

If Request.Form("Rooms") <> "Any" Then
mySQL = mySQL & " AND Rooms = '" Request.Form("Rooms") &"'"
End If

Then you move to the next dropdown:

If Request.Form("View") <> "Any" Then
mySQL = mySQL & " AND View = '" Request.Form("View") &"'"
End If

Something like that.



mjmtravel -> RE: 3 Drop down search boxes (2/16/2007 17:53:06)

So I can try that code in the custom query section of the drw?



BeTheBall -> RE: 3 Drop down search boxes (2/16/2007 18:25:01)

Afraid not. You would need to create a simple query, save the page, diet the code and then start customizing.



mjmtravel -> RE: 3 Drop down search boxes (2/16/2007 19:06:08)

I'm using access database because its somewhat simple. I'm not sure I will be able to do what you suggest. I'm not real good with code. I will give it a try and let you know.
Thanks



mjmtravel -> RE: 3 Drop down search boxes (2/17/2007 13:53:34)

Here is the code I have originally. I have tried to install code you have posted but, I can't seem to make it work. I'm not sure if I'm putting it in the right place or not. I changed it back to original. Basically I got no results found.
Code Handycapped I guess.

<!--webbot bot="DatabaseRegionStart" s-columnnames="IslandID,IslandName" s-columntypes="3,202" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="IslandName" s-menuvalue="IslandID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Islands" s-displaycolumns="IslandID,IslandName" s-criteria s-order s-sql="SELECT * FROM Islands" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Sorry no Condos meet that criteria, Please try another Search" i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " b-InForm="TRUE" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Islands"
fp_sDefault=""
fp_sNoRecords="Sorry no Condos meet that criteria, Please try another Search"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="IslandName"
fp_sMenuValue="IslandID"
fp_sColTypes="&IslandID=3&IslandName=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="62231" --><select NAME="IslandID" SIZE="1" style="position: relative">
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc" startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62210" -->
<option VALUE="<%=FP_FieldHTML(fp_rs,"IslandID")%>"><%=FP_FieldHTML(fp_rs,"IslandName")%>
</option>
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62218" -->
</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --><br>
<img border="0" src="images/Numberofrooms.jpg" width="169" height="22"><br>
<!--webbot bot="DatabaseRegionStart" s-columnnames="BedroomsID,Bedrooms" s-columntypes="3,202" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="Bedrooms" s-menuvalue="BedroomsID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Bedrooms" s-displaycolumns="BedroomsID,Bedrooms" s-criteria s-order s-sql="SELECT * FROM Bedrooms" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Sorry no Condos meet that criteria, Please try another Search" i-maxrecords="256" i-groupsize="0" botid="1" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan b-InForm="TRUE" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Bedrooms"
fp_sDefault=""
fp_sNoRecords="Sorry no Condos meet that criteria, Please try another Search"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Bedrooms"
fp_sMenuValue="BedroomsID"
fp_sColTypes="&BedroomsID=3&Bedrooms=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="57857" --><select NAME="BedroomsID" SIZE="1">
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc" startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62210" -->
<option VALUE="<%=FP_FieldHTML(fp_rs,"BedroomsID")%>"><%=FP_FieldHTML(fp_rs,"Bedrooms")%>
</option>
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62218" -->
</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --><br>
<img border="0" src="images/Condoviews.jpg" width="169" height="22">
<br>
<!--webbot bot="DatabaseRegionStart" s-columnnames="ViewID,Views" s-columntypes="3,202" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="Views" s-menuvalue="ViewID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Views" s-displaycolumns="ViewID,Views" s-criteria s-order s-sql="SELECT * FROM Views" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Sorry no Condos meet that criteria, Please try another Search" i-maxrecords="256" i-groupsize="0" botid="2" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan b-InForm="TRUE" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Views"
fp_sDefault=""
fp_sNoRecords="Sorry no Condos meet that criteria, Please try another Search"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Views"
fp_sMenuValue="ViewID"
fp_sColTypes="&ViewID=3&Views=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=2
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="16026" --><select NAME="ViewID" SIZE="1">
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc" startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62210" -->
<option VALUE="<%=FP_FieldHTML(fp_rs,"ViewID")%>"><%=FP_FieldHTML(fp_rs,"Views")%>
</option>
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62218" -->
</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --><br>
<img border="0" src="images/Swimmingpool.jpg" width="169" height="22"><br>
<!--webbot bot="DatabaseRegionStart" s-columnnames="SwimmingPoolID,SwimmingPool" s-columntypes="3,202" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="SwimmingPool" s-menuvalue="SwimmingPoolID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="SwimmingPool" s-displaycolumns="SwimmingPoolID,SwimmingPool" s-criteria s-order s-sql="SELECT * FROM SwimmingPool" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Sorry no Condos meet that criteria, Please try another Search" i-maxrecords="256" i-groupsize="0" botid="3" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM SwimmingPool"
fp_sDefault=""
fp_sNoRecords="Sorry no Condos meet that criteria, Please try another Search"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="SwimmingPool"
fp_sMenuValue="SwimmingPoolID"
fp_sColTypes="&SwimmingPoolID=3&SwimmingPool=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=3
fp_iRegion=BOTID
%>



BeTheBall -> RE: 3 Drop down search boxes (2/17/2007 14:01:53)

Unless I completely don't understand what you are hoping to do, you would only want one DRW with one SQL statement. However, looking at what you have so far makes me a bit nervous about your database structure. Do you really have tables named Islands, Bedrooms, View and Swimming Pool or are those fields all in the same table? Is so, what is the name of that table?



mjmtravel -> RE: 3 Drop down search boxes (2/17/2007 14:05:51)

I do have all those names in a separate tables and I have them in one large table called properties, which has all of the descriptions listed in it. What I'm trying to do is search for a condo on a particular island, with a certain number of bedrooms, with a certain view and swimming pool.



BeTheBall -> RE: 3 Drop down search boxes (2/17/2007 17:16:47)

Ok. I assume then for this you should only need the properties tabel. Create one DRW that simply returns all properties, i.e., SELECT * FROM Properties. Then, diet the code. The term diet simply means a process whereby we get rid of all of the FP webbot stuff so that you can edit the underlying asp code without FP undoing your changes. The best way to perform the diet is to download and run the macro talked about here:

http://www.frontpagewebmaster.com/m-279509/tm.htm

Once you get that far, we can start to modify the SQL.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 9:48:54)

Duane
Ok I downloaded the macro, and ran it. I installed one drw containing the properties table. I did edit the list in the drw to contain my original "island" setting. Is that OK? Now here is the new code after the macro. I did also remove the webbot endspan code per directions but it froze up my program everytime. I thought maybe more should be deleted to prevent this? 

<table width="100%" height="88%"  border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td align="center">
			<div style="padding-left:15 ">
				<form method="POST" action="http://palmislandcondos.com/Resultspage.asp">
					<p>
					<img border="0" src="images/Islandsearch.jpg" width="169" height="22"><nobr><br>
					<!--webbot bot="DatabaseRegionStart" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-columntypes="3,202,202,3,202,202,202,202,202,3,202,202,3,202,202,202,3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="IslandName" s-menuvalue="IslandID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="IslandID,IslandName" s-criteria s-order s-sql="SELECT * FROM Properties" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Sorry no Condos meet that criteria, Please try another Search" i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Properties"
fp_sDefault=""
fp_sNoRecords="Sorry no Condos meet that criteria, Please try another Search"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="IslandName"
fp_sMenuValue="IslandID"
fp_sColTypes="&ContactID=3&FirstName=202&LastName=202&IslandID=3&IslandName=202&PropertyName=202&PropertyDescription=202&Amenities=202&Photograph=202&BedroomsID=3&Bedrooms=202&Bathrooms=202&SwimmingPoolID=3&SwimmingPool=202&LivingRoom=202&Kitchen=202&ViewID=3&Views=202&AirConditioning=202&CeilingFans=202&Balcony=202&Pool=202&DishesUtensils=202&Refrigerator=202&Range=202&DishWasher=202&Microwave=202&WasherDryer=202&CoffeePot=202&Blender=202&CableTV=202&SpaHottub=202&Housekeeping=202&BBQGrill=202&"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="37333" --><select NAME="IslandID" SIZE="1">
					<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc" startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62210" -->
					<option VALUE="<%=FP_FieldHTML(fp_rs,"IslandID")%>"><%=FP_FieldHTML(fp_rs,"IslandName")%>
					</option>
					<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62218" -->
					</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --><br>
					<img border="0" src="images/Numberofrooms.jpg" width="169" height="22"><br>
					<select size="1" name="D1"></select><br>
					<img border="0" src="images/Condoviews.jpg" width="169" height="22">
					<br>
					<select size="1" name="D2"></select><br>
					<img border="0" src="images/Swimmingpool.jpg" width="169" height="22"><br>
					<select size="1" name="D3"></select><br>
					<br>
					</nobr><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
				</form>
			</div></td>
          </tr>
          </table>



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 11:20:40)

Try again, but in this order. Create page, save the page and THEN run the macro. The macro removes the webbot stuff, you should not need to do it manually.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 11:35:12)

OK I have done that and it did not lock up. I have one drw box and 3 other drop down form boxes. How do I start changing the code.



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 12:13:18)

Post the code you have.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 12:46:41)

Here it is 

<table width="100%" height="88%"  border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td align="center">
			<div style="padding-left:15 ">
				<form method="POST" action="http://palmislandcondos.com/Resultspage.asp">
					<p>
					<img border="0" src="images/Islandsearch.jpg" width="169" height="22"><nobr><br>
					<!--webbot bot="DatabaseRegionStart" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-columntypes="3,202,202,3,202,202,202,202,202,3,202,202,3,202,202,202,3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202" s-dataconnection="Database1" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="IslandName" s-menuvalue="IslandID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="IslandID,IslandName" s-criteria s-order s-sql="SELECT * FROM Properties" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Properties"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="IslandName"
fp_sMenuValue="IslandID"
fp_sColTypes="&ContactID=3&FirstName=202&LastName=202&IslandID=3&IslandName=202&PropertyName=202&PropertyDescription=202&Amenities=202&Photograph=202&BedroomsID=3&Bedrooms=202&Bathrooms=202&SwimmingPoolID=3&SwimmingPool=202&LivingRoom=202&Kitchen=202&ViewID=3&Views=202&AirConditioning=202&CeilingFans=202&Balcony=202&Pool=202&DishesUtensils=202&Refrigerator=202&Range=202&DishWasher=202&Microwave=202&WasherDryer=202&CoffeePot=202&Blender=202&CableTV=202&SpaHottub=202&Housekeeping=202&BBQGrill=202&"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="39045" --><select NAME="IslandID" SIZE="1">
					<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc" startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62210" -->
					<option VALUE="<%=FP_FieldHTML(fp_rs,"IslandID")%>"><%=FP_FieldHTML(fp_rs,"IslandName")%>
					</option>
					<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62218" -->
					</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --><br>
					<img border="0" src="images/Numberofrooms.jpg" width="169" height="22"><br>
					<select size="1" name="D1"></select><br>
					<img border="0" src="images/Condoviews.jpg" width="169" height="22">
					<br>
					<select size="1" name="D2"></select><br>
					<img border="0" src="images/Swimmingpool.jpg" width="169" height="22"><br>
					<select size="1" name="D3"></select><br>
					<br>
					</nobr><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
				</form>
			</div></td>
          </tr>
          </table>



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 13:50:34)

OK. Now we can edit the asp code (the red code) to do what you want. Replace this line:

fp_sQry="SELECT * FROM Properties"

with the following: 

myQry="SELECT * FROM Properties WHERE IslandID = "& Request.Form("IslandID")
If Cint(Request.Form("BedroomsID")) > 1 Then
myQry=myQry & " AND BedroomsID =" & Cint(Request.Form("BedroomsID"))
End If
If Cint(Request.Form("ViewID")) > 1 Then
myQry=myQry & " AND ViewID =" & Cint(Request.Form("ViewID"))
End If
If Cint(Request.Form("SwimmingPoolID")) > 1 Then
myQry=myQry & " AND SwimmingPoolID = "& Cint(Request.Form("SwimmingPoolID"))
End If
fp_sQry = myQry


This will build the SQL to include a filter for each form field that has an entry other than "any".



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 14:03:34)

I cannot get it to save the code. I hit safe then refresh, it goes back to the original code.



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 14:30:00)

Looking at the code you posted, I am a little confused. I thought we were working on the results page. However, your code has dropdowns. I was going on the assumption that we use the form on this page:

http://palmislandcondos.com/condosearch.asp

and post it to the results page which we would be editing.

Am I not understanding correctly?



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 14:42:57)

Duane, I'm sorry for being such a pain. The code I posted was from the search page, where I have drop down boxes.
Am I doing this bassackwards. Do I need the regular drw boxes on the search page and post your code on the results page?



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 14:45:41)

It seemed to me the search page was fine, unless you thought it had a problem. I thought the issue was getting the right results based on the choices in the dropdowns which would mean modifying the results page. In posting the code above, I fully intended it as a modification to the results page.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 14:47:17)

Ok let me try that and I'll let you know.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 15:05:47)

I installed my drw boxes on the search page and changed the code to your on the results page to yours but I still cannot get it to save.
I also need other results to post on the results page also like condo description and a photograph. All of this is in the properties table.
Here is the new code. 

<table border="1" width="75%" height="179" id="table1" background="images/$599_13.jpg">
		<tr>
			<td>
			<img border="0" src="images/palmlogo.gif" width="89" height="85" align="left"><font face="Firenze SF" size="5">Palm 
			Island Condos Search Result</font><font face="Firenze SF" size="4">s</font><p> </td>
		</tr>
		<tr>
			<td>
			<table width="100%" border="1">
				<thead>
					<tr>
						<th ALIGN="LEFT"><b>IslandName</b></th>
						<th ALIGN="LEFT"><b>PropertyDescription</b></th>
						<th ALIGN="LEFT"><b>Amenities</b></th>
						<th ALIGN="LEFT"><b>Photograph</b></th>
						<th ALIGN="LEFT"><b>Bedrooms</b></th>
						<th ALIGN="LEFT"><b>SwimmingPool</b></th>
					</tr>
				</thead>
				<tbody>
					<!--webbot bot="DatabaseRegionStart" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-columntypes="3,202,202,3,202,202,202,202,202,3,202,202,3,202,202,202,3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202" s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="5" b-makeform="FALSE" s-recordsource s-displaycolumns="IslandName,PropertyDescription,Amenities,Photograph,Bedrooms,SwimmingPool" s-criteria s-order s-sql="SELECT * FROM Properties " b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Sorry no Condos meet that criteria, Please try another search" i-maxrecords="256" i-groupsize="5" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Properties "
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">Sorry no Condos meet that criteria, Please try another search</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ContactID=3&FirstName=202&LastName=202&IslandID=3&IslandName=202&PropertyName=202&PropertyDescription=202&Amenities=202&Photograph=202&BedroomsID=3&Bedrooms=202&Bathrooms=202&SwimmingPoolID=3&SwimmingPool=202&LivingRoom=202&Kitchen=202&ViewID=3&Views=202&AirConditioning=202&CeilingFans=202&Balcony=202&Pool=202&DishesUtensils=202&Refrigerator=202&Range=202&DishWasher=202&Microwave=202&WasherDryer=202&CoffeePot=202&Blender=202&CableTV=202&SpaHottub=202&Housekeeping=202&BBQGrill=202&"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="2917" --><tr>
						<td>
						<!--webbot bot="DatabaseResultColumn" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-column="IslandName" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>IslandName<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"IslandName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29091" --></td>
						<td>
						<!--webbot bot="DatabaseResultColumn" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-column="PropertyDescription" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>PropertyDescription<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"PropertyDescription")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="57183" --></td>
						<td>
						<!--webbot bot="DatabaseResultColumn" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-column="Amenities" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Amenities<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Amenities")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="19909" --></td>
						<td>
						<!--webbot bot="DatabaseResultColumn" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-column="Photograph" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Photograph<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Photograph")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29352" --></td>
						<td>
						<!--webbot bot="DatabaseResultColumn" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-column="Bedrooms" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Bedrooms<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Bedrooms")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14962" --></td>
						<td>
						<!--webbot bot="DatabaseResultColumn" s-columnnames="ContactID,FirstName,LastName,IslandID,IslandName,PropertyName,PropertyDescription,Amenities,Photograph,BedroomsID,Bedrooms,Bathrooms,SwimmingPoolID,SwimmingPool,LivingRoom,Kitchen,ViewID,Views,AirConditioning,CeilingFans,Balcony,Pool,DishesUtensils,Refrigerator,Range,DishWasher,Microwave,WasherDryer,CoffeePot,Blender,CableTV,SpaHottub,Housekeeping,BBQGrill" s-column="SwimmingPool" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>SwimmingPool<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"SwimmingPool")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="33035" --></td>
					</tr>
					<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="5" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr><TR><TD VALIGN=MIDDLE COLSPAN=64><NOBR><INPUT TYPE=Button VALUE="  |<  "><INPUT TYPE=Button VALUE="   <  "><INPUT TYPE=Button VALUE="  >   "><INPUT TYPE=Button VALUE="  >|  ">  [1/5]</NOBR><BR></td></tr>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
			</table>
			<p align="center"><font face="Tahoma"><a href="condosearch.asp">Back 
			to Search Page</a></font></td>
		</tr>
		</table>



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 18:25:54)

Duane
Isn't there a way to change this query to not only use the "bedroomsID" but also the word "Any", so the results page will show either the number of bedrooms if a quantity is picked or any number of bedrooms if the word any is left in as default on the search page.
This is the actual query from the results page.

SELECT * FROM Properties WHERE (IslandID = ::IslandID:: AND BedroomsID = ::BedroomsID:: AND ViewID = ::ViewID:: AND SwimmingPoolID = ::SwimmingPoolID::)



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 18:37:13)

quote:

ORIGINAL: mjmtravel

Duane
Isn't there a way to change this query to not only use the "bedroomsID" but also the word "Any", so the results page will show either the number of bedrooms if a quantity is picked or any number of bedrooms if the word any is left in as default on the search page.
This is the actual query from the results page.

SELECT * FROM Properties WHERE (IslandID = ::IslandID:: AND BedroomsID = ::BedroomsID:: AND ViewID = ::ViewID:: AND SwimmingPoolID = ::SwimmingPoolID::)


Not really. That is why we are using if statements to decide whether or not to include a particular column in the query. If the value chosen is Any, we don't include that column in the WHERE clause.

If FP is undoing your changes, are you sure you installed and ran the macro I referenced above? When successfully run, the macro will get rid of the webbot stuff and stop FP from undoing things.



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 18:40:26)

The finished code should look something like this: 

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>
<table border="1" width="75%" height="179" id="table1" background="images/$599_13.jpg">
		<tr>
			<td>
			<img border="0" src="images/palmlogo.gif" width="89" height="85" align="left"><font face="Firenze SF" size="5">Palm 
			Island Condos Search Result</font><font face="Firenze SF" size="4">s</font><p> </td>
		</tr>
		<tr>
			<td>
			<table width="100%" border="1">
				<thead>
					<tr>
						<th ALIGN="LEFT"><b>IslandName</b></th>
						<th ALIGN="LEFT"><b>PropertyDescription</b></th>
						<th ALIGN="LEFT"><b>Amenities</b></th>
						<th ALIGN="LEFT"><b>Photograph</b></th>
						<th ALIGN="LEFT"><b>Bedrooms</b></th>
						<th ALIGN="LEFT"><b>SwimmingPool</b></th>
					</tr>
				</thead>
				<tbody>
					<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
                    </SCRIPT>
<% end if %>
<%
myQry="SELECT * FROM Properties WHERE IslandID = "& Request.Form("IslandID")
If Cint(Request.Form("BedroomsID")) > 1 Then
myQry=myQry & " AND BedroomsID =" & Cint(Request.Form("BedroomsID"))
End If
If Cint(Request.Form("ViewID")) > 1 Then
myQry=myQry & " AND ViewID =" & Cint(Request.Form("ViewID"))
End If
If Cint(Request.Form("SwimmingPoolID")) > 1 Then
myQry=myQry & " AND SwimmingPoolID = "& Cint(Request.Form("SwimmingPoolID"))
End If
fp_sQry = myQry
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">Sorry no Condos meet that criteria, Please try another search</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ContactID=3&FirstName=202&LastName=202&IslandID=3&IslandName=202&PropertyName=202&PropertyDescription=202&Amenities=202&Photograph=202&BedroomsID=3&Bedrooms=202&Bathrooms=202&SwimmingPoolID=3&SwimmingPool=202&LivingRoom=202&Kitchen=202&ViewID=3&Views=202&AirConditioning=202&CeilingFans=202&Balcony=202&Pool=202&DishesUtensils=202&Refrigerator=202&Range=202&DishWasher=202&Microwave=202&WasherDryer=202&CoffeePot=202&Blender=202&CableTV=202&SpaHottub=202&Housekeeping=202&BBQGrill=202&"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
						<td>
						<%=FP_FieldVal(fp_rs,"IslandName")%> </td>
						<td>
						<%=FP_FieldVal(fp_rs,"PropertyDescription")%> </td>
						<td>
						<%=FP_FieldVal(fp_rs,"Amenities")%> </td>
						<td>
						<%=FP_FieldVal(fp_rs,"Photograph")%> </td>
						<td>
						<%=FP_FieldVal(fp_rs,"Bedrooms")%> </td>
						<td>
						<%=FP_FieldVal(fp_rs,"SwimmingPool")%> </td>
					</tr>
					<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
			</table>
			<p align="center"><font face="Tahoma"><a href="condosearch.asp">Back 
			to Search Page</a></font></td>
		</tr>
		</table>
</body>

</html>


You can try and paste the above into your page over your existing table and see where that gets you.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 19:27:57)

Duane
I'm not sure what the difference was, but I copied the entire page that you sent and it work. I know it was removing most all of the original code but I did still see a few lines, that spooky had said to remove, in the diet. I must not have gotten all of them. Now that it works, can I change some of the results boxes to accept html, like in the regular drw results. I need to have some breaks and paragraphs in the results.
Thanks for your patience. I have worked on this all day long.



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 19:35:35)

With the code dieted, you should be able to change whatever you need. I think you just need to change FP_FieldVal to just FP_Field and you will be in business.



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 19:55:44)

Duane
Everything is good, except the photograph field. It still shows the text after changing the FP_field.



BeTheBall -> RE: 3 Drop down search boxes (2/18/2007 20:07:29)

You have to include the html to make it display the image. Instead of:

<td><%=FP_FieldVal(fp_rs,"Photograph")%> </td>

something like :

<td><img src = "<%=FP_FieldVal(fp_rs,"Photograph")%>"></td>



mjmtravel -> RE: 3 Drop down search boxes (2/18/2007 20:31:17)

Duane
I can't thank you enough. I changed the text and the photo comes out perfect. If you would email me your address I would like to send you a token of my appreciatioin.
Thank you so very much.
I learn a lot everytime I deal with you.
Jeff Baker



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1386719