Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Recorset EOF problem

    Hi,

    I am attempting to write a page which creates an HTML table 1 column wide and with enough rows in to cover the time period 8am to 6pm divided into half hour slots. I also have a database that has 4 main columns - StartTime, EndTime, RoomID and Date. It contains information about whether a boardroom has been booked for use. The idea is the table is being drawn if the room is in use at that point the background is yellow and if it's not the background is white. If a boardroom is in use for say 2 hours I would want all 4 rows to be yellow - not just the first and last ones.

    I am running into two problems - 1 how to handle a case where it looks up something in the database and there's nothing there. Either it crashes out or it doesn't complete the table. Even if the room isn't being used all day I would still want to show the table. The second is more of a semantics problem - when I try to compare the time in the row of the table with the time in the recordset it doesn't seem to work properly. Maybe it's because I have inadvertently converted them to strings rather than time values...I'm not sure!

    Anyway this is what I've got so far - would be very grateful for any assistance! Thanks:

    <%

    Set DB = Server.CreateObject("ADODB.Connection")
    Set TBL = Server.CreateObject("ADODB.RecordSet")

    DB.Mode = adModeReadWrite
    DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("../directory/userdb.mdb"))

    TBL.Open "SELECT StartTime, EndTime FROM Bookings WHERE Date=#" & Date & "# AND RoomID=" & Request.QueryString("Room") & " ORDER BY StartTime ASC", DB

    %>

    <HTML>
    <BODY>
    <font face="verdana" size="3">
    <b>Schedule for Today - Boardroom <% = Request.QueryString("Room")+1 %></b><br><br>
    <table width="100%" cellspacing="5" cellpadding="5" border="1">

    <%

    Clock=#08:00#

    Do While Clock < #18:30#

    If Not TBL.EOF Then

    StartTime = "#" & TBL("StartTime") & "#"
    EndTime = "#" & TBL("EndTime") & "#"

    If Clock >= StartTime AND Clock < EndTime Then
    Do While Clock < EndTime
    Response.Write("<tr>")
    Response.Write("<td")
    Response.Write(" bgcolor='FFFFCC'>")
    Response.Write("</td>")
    Response.Write(Clock)
    Response.Write("</tr>")
    Clock = Clock + #0:30#
    Loop
    Else
    Response.Write("<tr><td>")
    Response.Write(Clock)
    Response.Write("</td></tr>")
    Clock=Clock+#0:30#
    End If

    End If

    TBL.MoveNext

    Loop


    %>

    </table>
    </BODY>
    </HTML>

    <% TBL.Close
    Set TBL=Nothing
    Set DB=Nothing
    %>

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Please put code into code blocks it makes it much easier to read with the indenting in place.

    It isn't completing the table because you have an if statement that says
    If Not TBL.EOF Then
    and you do not have any Else option. So you need to have an else option.

    Why do you have the # signs outside of the query?? It shouldn't even work at all with them outside the query.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    I think you don't even need that extra clock variable. Inside the loop, get the current start/end times from your recordset, and automatically drop them into the table. Then, check to see if there is anything for the room at the current time. If there is, add a (HTML) table cell with whatever content you need. Otherwise, add an empty table cell.

    You also shouldn't need to use the clock variable as your loop counter, simply do while TBL...Wend. That way, you just loop through each row in the recordset anyway.
    Last edited by Grant Palin; 08-19-2005 at 07:47 PM.

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by miranda
    Why do you have the # signs outside of the query?? It shouldn't even work at all with them outside the query.
    I think that is VBScript syntax for representing time in hours and minutes.

  • #5
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by Grant Palin
    I think that is VBScript syntax for representing time in hours and minutes.
    Nope. It pertains to SQL statements in regards to dates in Access only. To test try the following

    Dim Clock : Clock=#08:00#
    Response.Write(Clock)

    you will get nothing printed to the screen. It has nothing to do with VB or VB Script

  • #6
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    yup the CDate() function should be used instead. this can be used with dateadd () or just +(1/48) (that would be +0.5 hour i think)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •