hi,
I've put together a little piece of code to allow a user to search a website for properties to rent or buy against a set of locations (pulled from the database).

what I'm finding is that I have error handling issues when say a search is made for rental properties against a town for which I don't have a rental property (but I do have a property in the database, for sale).

because the locations are pulled from the database to populate the select option box, they are all genuine records but, the locations don't always have properties for sale and for rent so that occasionally I get a "EOF or BOF" error.

I'm just looking for some ideas as to how I could possibly handle the situation gracefully. I was thinking of using some AJAX techniques to get the select option box to re-populate after the user changes the selection on the 'rent or buy' drop-down box, but this seems quite complicated and I'm sure there is an easier ASP solution to this.

anyway, here is the relevant asp code:

from the search form:
<%
Dim QSFobj
Dim SQLdata

SQLdata = "SELECT boroughs.boroughName, areas.town, prop_type.property_type, properties.*" & _
"FROM boroughs INNER JOIN (areas INNER JOIN (prop_type INNER JOIN properties ON prop_type.typeID = properties.type) ON areas.ldn_townsID = properties.area) ON boroughs.boroughID = areas.borName;"

Set QSFobj=Server.CreateObject("ADODB.recordset")
QSFobj.open SQLdata, conObj
QSFobj.MoveFirst
%>

            <form id="QSform" class="genForm" name="QSform" method="post" action="catcher.asp">
            <fieldset>
            <legend>QuickSearch Form</legend>
            <p><label>looking&nbsp;to:</label><select name="RentOrBuy" id="RentOrBuy">
                <option selected="selected" value="buy">buy</option>
                <option value="rent">rent</option>
            </select></p>
            <p><label>in&nbsp;area:</label><select name="selected_area" id="selected_area" label="Select Area: ">
                <OPTION SELECTED VALUE="">Select area</option>
                <%
                dim selArea
                QSFobj.MoveFirst

                While NOT QSFobj.EOF
                selArea=QSFobj.Fields.Item("town").Value
                response.write "label for dropdown<br />"
                response.Write "<option value='" & selArea & "'>" & selArea & "</option>"
                QSFobj.MoveNext
                WEND
                %>
            </select></p>
            <p><input type="submit" name="search" id="search" value="submit" /></p>
            </fieldset>
            </form>
            <%
            QSFobj.close
            set QSFobj=nothing
            %>

from the 'catcher.asp' page:
<%
Dim rentbuy, areavar, priceType, rntBool
    rentbuy= request.Form("RentOrBuy")

    IF request.Form("selected_area") <> "" THEN
    areavar = request.Form("selected_area")
    ELSE
    areavar = "Stratford"
    END IF

    IF rentbuy = "rent" THEN
    priceType = ", monthly: "
    rntBool = TRUE
    ELSE
    priceType = ", asking price: "
    rntBool = FALSE
    END IF
%>



<%
    'remember include file above has connection object set as "conObj"
    'dim the recordset and SQL variable
    Dim RSobj
    Dim SQLdat
    Dim SQLComplex

    'build SQL string

    'SQLdat = "SELECT * from properties;"
    SQLComplex = "SELECT prices.price, prices.monthly, prices.deposit, prices.conditions, properties.*, tube.station, areas.town, boroughs.boroughName "
    SQLComplex = SQLComplex & "FROM (tube INNER JOIN ((boroughs INNER JOIN areas ON boroughs.boroughID = areas.borName) INNER JOIN properties ON areas.ldn_townsID = properties.area) ON tube.tubeID = properties.nearest_tube) INNER JOIN prices ON properties.PID = prices.ID WHERE areas.town = '" & areavar & "' AND properties.rent = " & rntBool & " ;"

    'SQLdat = "SELECT boroughs.boroughName, areas.town, prop_type.property_type, properties.*" & _
'"FROM boroughs INNER JOIN (areas INNER JOIN (prop_type INNER JOIN properties ON prop_type.typeID = properties.type) ON areas.ldn_townsID = properties.area) ON boroughs.boroughID = areas.borName WHERE areas.town = '" & areavar & "';"

    'instantiate the connection and open up a database
    set RSobj = Server.CreateObject("ADODB.recordset")
    RSobj.open SQLComplex, conObj
    RSobj.MoveFirst

    'handle eof situations where no records exist for the search
        IF RSobj.EOF THEN
        response.Write("Sorry, there are no properties matching your criteria at the moment.")
        response.End()
        END IF
%>

<%
            response.write "you are looking to <strong>" & rentbuy & "</strong> a property in the <strong>" & areavar & "</strong> area. <br />"
            If (RSobj.CursorType >0) Then
            RSobj.MoveFirst
            End If
            %>
            <%WHILE (NOT RSobj.EOF)%>
            <%
Dim propID, brief, pstcode, addLine1, addLine2, proparea, proptype, bedNo, RecepNo, rentval, borName, tstn, dep, conds, mthlys, valPrice

propID = RSobj.Fields.Item("PID").Value
brief = RSobj.Fields.Item("brief_desc").Value
pstcode = RSobj.Fields.Item("postcode").Value
addLine1 = RSobj.Fields.Item("street1").Value
addLine2 = RSobj.Fields.Item("street2").Value
proparea = RSobj.Fields.Item("Town").Value
borName = RSobj.Fields.Item("boroughName").Value
tstn = RSobj.Fields.Item("station").Value
dep = RSobj.Fields.Item("deposit").Value
conds = RSobj.Fields.Item("conditions").Value
mthlys = RSobj.Fields.Item("monthly").Value
mthlys = FormatCurrency(mthlys)
valPrice = RSobj.Fields.Item("price").Value
valPrice = FormatCurrency(valPrice)
'proptype = RSobj.Fields.Item("property_type").Value
bedNo = RSobj.Fields.Item("bedrooms").Value
RecepNo = RSobj.Fields.Item("reception_rooms").Value
rentval = RSobj.Fields.Item("rent").Value

IF rentval <> TRUE THEN rentval="for sale" ELSE rentval="for rent" END IF
%>
            <br />Ref: <%=propID%>, postcode: <%=pstcode%><br />Town: <%=proparea%>, <%=borName%><br />nearest tube: <%=tstn%><br />Bedrooms: <%=bedNo%><br />Reception Rooms: <%=RecepNo%><br />This property is <strong><%=rentval%></strong><%=priceType%>&nbsp;<strong><% IF mthlys <> 0 THEN response.Write(mthlys) END IF %><% IF valPrice <> 0 THEN response.write(valPrice) END IF %></strong>
            <% RSobj.MoveNext %>
            <%WEND%>
            <%
            RSobj.close
            set RSobj=nothing
            %>



murshed.

solved/fixed it.
I was too focused on the idea of "Error handling", should have concentrated on the actual nature of the error in question, the BOF/EOF thing.

just set up an IF THEN ELSE to capture the rs before it executed and give a graceful error message and some links back to the site, in the event of there not being a record in existence matching the criteria set by the user.

Hi,

I was faced with the same issue on a site that had equipment for sale and for rent. What I did was only populate the drop-down if there was equipment for rent or sale in that category. So, in your case, with this logic, the town just wouldn't show up. As long as you got it fixed and its working. That's good. I like it when stuff works.

Richard

hi,
I thought about doing that, just adding another condition onto the sql behind the drop box but, I needed to have all the offerings available via the quicksearch.

by creating an bof|eof handling variable, I'm able to inform the user about the reason why the particular search combination failed as well as present them with other options.

I was thinking about putting together a series of LIKE "x%" tests against the recordset, then presenting them as potentially satisfying alternatives, because in most cases a person will desire a property to be in either a specific area and to a less specific size ... so if I had a 3 bedroom property and not a 4 bedroom one... it might be of benefit to the user to be shown it.

Hi,

I guess everyone has their own way. I just wouldn't like to keep getting dead end search results after choosing my filters... too frustrating. Let me know how you're LIKE statements work out. Good luck with it.

Richard