Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 17th, 2008, 10:40 AM
Authorized User
 
Join Date: Feb 2008
Location: london, , United Kingdom.
Posts: 17
Thanks: 3
Thanked 0 Times in 0 Posts
Default "Either BOF or EOF is True" need help!

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.
Reply With Quote
  #2 (permalink)  
Old March 17th, 2008, 06:11 PM
Authorized User
 
Join Date: Feb 2008
Location: london, , United Kingdom.
Posts: 17
Thanks: 3
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #3 (permalink)  
Old March 17th, 2008, 08:53 PM
Friend of Wrox
Points: 2,077, Level: 18
Points: 2,077, Level: 18 Points: 2,077, Level: 18 Points: 2,077, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 594
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old March 18th, 2008, 07:31 AM
Authorized User
 
Join Date: Feb 2008
Location: london, , United Kingdom.
Posts: 17
Thanks: 3
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #5 (permalink)  
Old March 18th, 2008, 03:43 PM
Friend of Wrox
Points: 2,077, Level: 18
Points: 2,077, Level: 18 Points: 2,077, Level: 18 Points: 2,077, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 594
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Random record from objRS - why is EOF or BOF True? jfrizelle Classic ASP Databases 0 February 5th, 2008 06:24 AM
Re-try - BOF-EOF error JpaulH Access VBA 2 May 4th, 2006 02:16 PM
Access to Excel - BOF or EOF error JpaulH Access VBA 1 May 3rd, 2006 04:30 PM
Either BOF or EOF is True, or the current record h gilgalbiblewheel Classic ASP Databases 3 March 9th, 2005 06:07 PM
RecordCount Query returns EOF and BOF jigs_bhavsar Pro VB Databases 3 November 11th, 2004 08:32 AM



All times are GMT -4. The time now is 03:02 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.