By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,884 Members | 1,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,884 IT Pros & Developers. It's quick & easy.

Conditional Code if within Date / Time in Database

jschrader
P: 8
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.


Expand|Select|Wrap|Line Numbers
  1. '*****************************************************************************
  2. '    block login function
  3. '*****************************************************************************
  4. sub Block_Login
  5.     dim cn, rs, sql
  6.     set cn=server.CreateObject("ADODB.Connection")
  7.     set rs=server.CreateObject("ADODB.Recordset")
  8. Today = Date
  9. Tomorrow = DateAdd("d",1,Today)
  10. sql="select DowntimeDate, FromTime, UntilTime from Downtime where DowntimeDate BETWEEN '" & Today & "' AND '" & Tomorrow & "'"
  11.     cn.Open Application("database")
  12.     rs.Open sql, cn, 3, 3
  13. %>
  14. <%
  15.     'create variables
  16.     Dim strDate, strTime, strBoth
  17.     'Set variable to todays date (and time)
  18.     'strDate = Date
  19.     strTime = Time
  20.     strBoth = Now
  21. %>
  22.   <%
  23.     DO UNTIL RS.EOF
  24.     IF RS("DowntimeDate") = Today Then
  25.   %>
  26.         <p>System is currently undergoing maintenance
  27.           from <%=rs("FromTime")%> EST until <%=rs("UntilTime")%> EST.
  28.         <p>We apologize for any inconvenience.
  29.  
  30.  <% ELSEIF RS("DowntimeDate") <> Today Then%>
  31.  
  32.         <!-- BEGIN MEMBER LOGIN --> 
  33.           <iframe src="signon.html" 
  34.           name="signon" 
  35.           scrolling="no"></iframe>
  36.           <br>
  37.         <!-- END MEMBER LOGIN -->  
  38.   <% 
  39.     End IF
  40.     RS.MoveNext
  41.     Loop
  42.   %>
  43. <%    
  44.     rs.Close
  45.     cn.Close
  46.     set rs=nothing
  47.     set cn=nothing
  48. end sub
  49.  
Mar 26 '08 #1
Share this Question
Share on Google+
8 Replies


DrBunchman
Expert 100+
P: 979
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
Mar 27 '08 #2

jschrader
P: 8
yes, that's what I'm trying to do...
Mar 27 '08 #3

DrBunchman
Expert 100+
P: 979
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. IF RS("FromTime") <= Now And RS("UntilTime") >= Now Then
  3.  
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
Mar 27 '08 #4

jschrader
P: 8
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"

Expand|Select|Wrap|Line Numbers
  1.  
  2. IF RS("FromTime") <= Now And RS("UntilTime") >= Now Then
  3.   %>
  4.         currently undergoing maintenance message
  5.  
  6. <% ELSEIF RS("FromTime") > Now And RS("UntilTime") < Now Then%>
  7.  
  8.         <!-- BEGIN MEMBER LOGIN --> 
  9.         Login fields here
  10.         <!-- END MEMBER LOGIN -->          
  11.  
Mar 27 '08 #5

jschrader
P: 8
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
Mar 27 '08 #6

danp129
Expert 100+
P: 321
I would write it more like this:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. '*****************************************************************************
  3. '   block login function
  4. '*****************************************************************************
  5. sub Block_Login
  6.     dim cn, rs, sql
  7.     set cn=server.CreateObject("ADODB.Connection")
  8.     set rs=server.CreateObject("ADODB.Recordset")
  9.  
  10.     sql="SELECT FromTime, UntilTime from Downtime WHERE '" & now() & "' BETWEEN FromTime AND UntilTime"
  11.  
  12.     cn.Open Application("database")
  13.     rs.Open sql, cn, 3, 3
  14.  
  15.     If rs.EOF then
  16.         'not during downtime, show your login form
  17.         %><!-- BEGIN MEMBER LOGIN --> 
  18.           <iframe src="signon.html" 
  19.           name="signon" 
  20.           scrolling="no"></iframe>
  21.           <br>
  22.         <!-- END MEMBER LOGIN --><%
  23.     else
  24.         'there's currently downtime show your message
  25.         Response.Write "<p>System is currently undergoing maintenance:<br>"
  26.         While not rs.EOF
  27.             Response.Write rs("FromTime") & " EST until " & rs("UntilTime") & " EST.<br>"
  28.             rs.movenext
  29.         Wend
  30.         Response.Write "<p>We apologize for any inconvenience."
  31.     end if
  32.  
  33.     set rs=nothing
  34.     set cn=nothing
  35. end sub
  36. %>
  37.  
Mar 27 '08 #7

jschrader
P: 8
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
Mar 27 '08 #8

danp129
Expert 100+
P: 321
Glad to hear you got it working :)
Mar 27 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.