Conditional Code if within Date / Time in Database

[jschrader]

I am trying to write a conditional Statement in ASP based on dates/times in a database entry.

I'm basically a ASP/web designer and try my best to get into the fun coding, so not sure as to the syntax of what I'm trying to do...

The structure I'm working on is

Database Connection
Select DB record occuring "Today" - have this working with BETWEEN statement
DO UNTIL RS.EOF
IF RS(title) < FromTime or >UntilTime Then
<display login HTML code>

ELSEIF RS(title) < UntilTime and > FromTime Then
<display downtime HTML message>

ELSEIF no record for today??
<display login HTML code>

END IF
Loop, close, etc...

This is displaying the maintenance message for the day of the downtime, but when there is none for the day, it displays nothing and I need to specify it to the times of the day specifically

If anyone can think of a better way to do this, I'm all ears, but The current code is below.

'*****************************************************************************
'    block login function
'*****************************************************************************
sub Block_Login
    dim cn, rs, sql
    set cn=server.CreateObject("ADODB.Connection")
    set rs=server.CreateObject("ADODB.Recordset")
Today = Date
Tomorrow = DateAdd("d",1,Today)
sql="select DowntimeDate, FromTime, UntilTime from Downtime where DowntimeDate BETWEEN '" & Today & "' AND '" & Tomorrow & "'"
    cn.Open Application("database")
    rs.Open sql, cn, 3, 3
%>
<%
    'create variables
    Dim strDate, strTime, strBoth
    'Set variable to todays date (and time)
    'strDate = Date
    strTime = Time
    strBoth = Now
%>
  <%
    DO UNTIL RS.EOF
    IF RS("DowntimeDate") = Today Then
  %>
        <p>System is currently undergoing maintenance
          from <%=rs("FromTime")%> EST until <%=rs("UntilTime")%> EST.
        <p>We apologize for any inconvenience.
 
 <% ELSEIF RS("DowntimeDate") <> Today Then%>
 
        <!-- BEGIN MEMBER LOGIN --> 
          <iframe src="signon.html" 
          name="signon" 
          scrolling="no"></iframe>
          <br>
        <!-- END MEMBER LOGIN -->  
  <% 
    End IF
    RS.MoveNext
    Loop
  %>
<%    
    rs.Close
    cn.Close
    set rs=nothing
    set cn=nothing
end sub
 

[DrBunchman]

This is displaying the maintenance message for the day of the downtime, but when there is none for the day, it displays nothing and I need to specify it to the times of the day specifically

If anyone can think of a better way to do this, I'm all ears, but The current code is below.

Hi there,

I'm not sure exactly what you are asking....do you want to only show the maintenance message at certain times of the specified day rather than the whole day?

So you'd store the time that the downtime begins and ends in the database and then display the maintenance message if the current datetime is between those values?

If you can clarify then I'm sure we can help you out.

Dr B

[jschrader]

yes, that's what I'm trying to do...

[DrBunchman]

This is nice and easy if you make a minor change to your database.

Storing FromTime and UntilTime as DateTime datatypes in the database means you don't have to store the day as a separate column. The DateTime datatype stores, as it's name suggests, a date and a time value.

If you do this then you can change your condition to:

 
IF RS("FromTime") <= Now And RS("UntilTime") >= Now Then
 

This means that you're site will correctly display the message even if the down time spans two different days.

Does this make sense? Let us know how you get on.

Dr B

[jschrader]

Thanks much for the time help - got that working great now. changed the datetime formats, now just need to change the backend admin interface so people enter it in the correct format.

There was one more part to this though. This message is replacing the login window when the system is down, so it will need to display it when it is not down. The code is not displaying the login window in the ELSEIF section...

need to account for "no record" or "record outside of range"

 
IF RS("FromTime") <= Now And RS("UntilTime") >= Now Then
  %>
        currently undergoing maintenance message
 
<% ELSEIF RS("FromTime") > Now And RS("UntilTime") < Now Then%>
 
        <!-- BEGIN MEMBER LOGIN --> 
        Login fields here
        <!-- END MEMBER LOGIN -->          
 

[jschrader]

and now added the date/time picker to the backend admin to make sure people put it in the correct format. Quick and easy add-on here: http://www.javascriptkit.com/script/...calendar.shtml

but still working on getting it to display the login window when there is not a downtime active

[danp129]

I would write it more like this:

<%
'*****************************************************************************
'   block login function
'*****************************************************************************
sub Block_Login
    dim cn, rs, sql
    set cn=server.CreateObject("ADODB.Connection")
    set rs=server.CreateObject("ADODB.Recordset")
 
    sql="SELECT FromTime, UntilTime from Downtime WHERE '" & now() & "' BETWEEN FromTime AND UntilTime"
 
    cn.Open Application("database")
    rs.Open sql, cn, 3, 3
 
    If rs.EOF then
        'not during downtime, show your login form
        %><!-- BEGIN MEMBER LOGIN --> 
          <iframe src="signon.html" 
          name="signon" 
          scrolling="no"></iframe>
          <br>
        <!-- END MEMBER LOGIN --><%
    else
        'there's currently downtime show your message
        Response.Write "<p>System is currently undergoing maintenance:<br>"
        While not rs.EOF
            Response.Write rs("FromTime") & " EST until " & rs("UntilTime") & " EST.<br>"
            rs.movenext
        Wend
        Response.Write "<p>We apologize for any inconvenience."
    end if
 
    set rs=nothing
    set cn=nothing
end sub
%>
 

[jschrader]

Thanks Dan - that worked like a charm.

Everything is working fine now!! even thought it seems my server might be 10 min off from my computer's clock, but guess thats minor.

thanks a ton.

Jeremy

[danp129]

Glad to hear you got it working :)