Using Date and Time to auto post recordset

pablohoney · March 6th, 2005, 04:19 PM

Hi,
I have a database driven site with an admin section. Currently when i add new press releases, they are automatically reflected on the public site page press release. Here are the fields for the DB

pressID
pressDate
pressTitle
pressRelease

<input value="<%=hour(pressDate)%>">
<input value="<%=day(pressDate)%>">
<input value="<%=month(pressDate)%>">
<input value="<%=year(pressDate)%>">

The imput page in the admin lets you add a press release by the fields listed. I would like to be able to set a time and date for when that recordset is published. Meaning that i enter the time and date (3 days from now at 11:00 pm) i can post the press release and when the time and date arrive, the recordset will show. Prior to that, the recordset will show the previous recordset.

currently the output page code looks like this:

set rs=getlastPressRelease(3,"EN")

And the inline page recordset output looks like this:
<%for i=1 to 4%>
<a href="press_releaseInfo.asp?pressID=<%=rs("pressID ")%>">
<a href="press_releaseInfo.asp?pressID=<%=rs("pressID ")%>">
<%=frmDate(rs("pressdate"))%></b><br>
<%=left(rs("pressTitle"),128)%><%if len(rs("pressTitle"))>80 then Response.Write "..."%>
<%rs.movenext
next%>

I'm trying to create the if then statement for this,

Thanks
Pablo

mat41 · March 6th, 2005, 06:17 PM

I didnt understand fully what you are trying to achieve. You would like to publish your press relases in three days time at 11pm from when they are entered?

FYI I have found it handy to add an expiryDate field to news/bulletin(etc) boards - this way the 'stop showing my item' issue is resolved.

Wind is your friend
Matt

pablohoney · March 6th, 2005, 09:21 PM

Yes - the idea is that i can add these press releases prior to the date and they will automatically publish when the date and time arrive.
Thanks
Pablo

mat41 · March 6th, 2005, 09:45 PM

I would add an extra field 'publishDate' to your table structure, enter the date to be published. Then in your sql get only the records you need based on this field. This way is good because you have the date of entry (pressDate field?) and the date published (new field) for future analysis and a historical lookup.

Wind is your friend
Matt

pablohoney · March 7th, 2005, 04:01 AM

That's great, how would i setup the statement in my code?
Thanks
Paul

mat41 · March 7th, 2005, 06:58 PM

To illustrate these t and d values:
a..May be converted to a date type (isDate function)
b..Have been converted (cDate function)

-------------------------cutn n paste-----------
Dim t,d
d = date() + 3
t = " 11:00:00 AM"
IF (IsDate(d)) AND (IsDate(t)) then
response.write(CDate(d & t)) & " =converted to date type<br>"
END IF
-----------------------------------------------

To use in your statement:

sql = "INSERT INTO tbleName (postedDate,publishedDate) VALUES ('" & now() & "','" & cdate((date() + 3) & " 11:00:00 PM") & "');"

How do your press releases know when to stop showing? An expiry date is the method I use for news and bulletin boards. This way the sql that pulls the records on the press release page maintains its self.

Wind is your friend
Matt

pablohoney · March 7th, 2005, 09:46 PM

I dont use an expiry date. The INSERT I get although t="string" should be time function on a 24:00 clock.
Here is the code for the output page and the associated include file with some functions on it
Thanks for your help
Pablo

-----------------
pressrelease.asp - public output page
-----------------

<%dim rs,yy,i
if request("year")<>"" then
    yy=request("year")
    set rs=getPressListByYear(yy,"EN")
else
    set rs=getLastPressRelease(50,"EN")
end if
'set rs=getlastPressRelease(50,"EN")
'set rs=getPressListByYear(yy, "EN")
%>
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<title></title>
<link rel="stylesheet" href="../css/pli.css" type="text/css">
</head>
<body bgcolor="#ffffff" topmargin="0" leftmargin="0" marginheight="0" marginwidth="0" background="#">
<tr>
</p>
<table border="0" cellpadding="4" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="650" id="AutoNumber1" height="547">
  <tr>
    <td width="817" height="533" valign="top" background="../images/vline.gif">
      <a name="top"><img border="0" src="img/headers/press_releases_hd.gif"></a>
      <br>
      Sort press releases by year  -  <a href="press_releases.asp?year=2005">2005</a>  
      <a href="press_releases.asp?year=2004">2004</a>  
      <a href="press_releases.asp?year=2003">2003</a>  
      <a href="press_releases.asp?year=2002">2002</a>   <a href="press_releases.asp?year=2001">2001</a>  
      <a href="press_releases.asp?year=2000">2000</a><p>
            <%for i=1 to rs.recordCount%>
            <br><%=frmDate(rs("pressDate"))%><br>
            <a target="main" href="press_releaseInfo.asp?pressID=<%=rs("pressID ")%>">
            <b><%=rs("pressTitle")%></b></a> <br>
            <%rs.moveNext
            next%>
            <br>
             <p> </p>
    <p> <p align="right"><p align="right">
      <a href="#top">
      <img height="9" src="img/top_buttonE.gif" width="25" border="0"></a></td>
  </tr>
  </table>

</body>
</html>

-------------------
Server Side Include
-------------------

<SCRIPT LANGUAGE=vbscript RUNAT=Server>
Dim adoConn,connState

function openConn()
dim ConnectionString
    ' Be sure that the source parameter of connectionString if the valid path of the mdb file
    if Request.ServerVariables ("SERVER_NAME")="localhost" then
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Inetpub\wwwroot\database\database.mdb"
    else
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\InetPub\bmlon\database\database.mdb"
    end if
'Response.Write ConnectionString
'Response.Flush
    Set adoConn = server.CreateObject ("ADODB.Connection")
    adoConn.Open ConnectionString
    connState=1
end function

function closeConn()
    adoConn.Close
    Set adoConn = Nothing
end function

Function executeQuery(strSQL)
'On Error GoTo ErrorHandler

' Response.Write strSQL
' Response.Flush
Dim adoRS
    set adoRs = server.CreateObject ("ADODB.Recordset")
    If connState<>1 Then
        openConn
    End If

    strSQL = Replace(strSQL, "`", "''")
    'Set execute = adoConn.Open(strSQL)

    With adoRS
         Set .ActiveConnection = adoConn
         .CursorType = 3 ' adOpenStatic
         .LockType = 1 ' adLockReadOnly
         .CursorLocation = 3 ' adUseClient
         .Open strSQL
    End With

    Set executeQuery = adoRS
    Set adoRS = Nothing
End Function

Function getLang(lang)
    If Not IsNumeric(lang) Then
        getLang = getLangID(lang)
    Else
        getLang = lang
    End If
End Function

Function getLangID(langAB)
Dim rs
    Set rs = executeQuery("SELECT langID FROM lstDescriptionLang WHERE langAB = '" & langAB & "'")
    getLangID = rs(0)
    Set rs = Nothing
End Function

Function dbExec(strSQL)
'Response.Write strsql &""
'Response.Flush
    Dim adoRS
    set adoRs = server.CreateObject ("ADODB.Recordset")
    If connState<>1 Then
        openConn
    End If
    strSQL = Replace(strSQL, "`", "''")
    With adoRS
         Set .ActiveConnection = adoConn
         .CursorType = 3 ' adOpenStatic
         .LockType = 1 ' adLockReadOnly
         .CursorLocation = 3 ' adUseClient
         .Open strSQL
    End With
    Set dbExec = adoRS
    Set adoRS = Nothing
End Function

Function dbExecRW(strSQL)
'Response.Write strsql &""
'Response.Flush
    Dim adoRS
    set adoRs = server.CreateObject ("ADODB.Recordset")
    If connState<>1 Then
        openConn
    End If
    strSQL = Replace(strSQL, "`", "''")
    With adoRS
         Set .ActiveConnection = adoConn
         .CursorType = 2 ' adOpenDynamic
         .LockType = 4 ' adLockBatchOptimistic
         .CursorLocation = 2 ' adUseServer
         .Open strSQL
    End With
    Set dbExecRW = adoRS
    Set adoRS = Nothing
End Function

Function getStrDate2(dd,mm,yyyy)
    getStrDate2 = "#" & Right("0" & mm, 2) & "/" & Right("0" & dd, 2) & "/" & yyyy & "#"
End Function

Function getStrDate(strDate)
    getStrDate = "#" & Right("0" & Month(strDate), 2) & "/" & Right("0" & Day(strDate), 2) & "/" & Year(strDate) & "#"
End Function

Function getStrTS(strDate)
    getStrTS = "#" & Right("0" & Month(strDate), 2) & "/" & Right("0" & Day(strDate), 2) & "/" & Year(strDate) & " " & Hour(strDate) & ":" & Minute(strDate) & ":" & Second(strDate) & "#"
End Function
</SCRIPT>

------------
------------

mat41 · March 7th, 2005, 09:56 PM

;;;I dont use an expiry date

This was a suggestion - As I previously asked:
"How do your press releases know when to stop showing?"

Anyhow;

;;;The INSERT I get although t="string" should be time function on a 24:00 clock

As you can see - the time is hard coded in the example, and the insert statement. You orginaly said "3 days from now at 11:00 pm" - this doesnt look like a 24 hour clock format. Change 11 to 23 maybe...

Wind is your friend
Matt