Recorset EOF problem

emozley

08-19-2005, 03:30 PM

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
%>

miranda

08-19-2005, 06:54 PM

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.

Grant Palin

08-19-2005, 07:45 PM

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.

Grant Palin

08-19-2005, 10:03 PM

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.

miranda

08-20-2005, 01:35 AM

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

ghell

08-20-2005, 11:29 PM

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)