Adding date field to script - how?

hi all,

The code I am pasting below works fine.

However, it is not sorted correctly.

To sort correctly in descending order so that the lasted file posted is on top of the list,  we needed to add a datefield called DatePosted.

RIght now, I am having a hard time getting the datePosted date field to display.

Please help.

Here is the entire code:

<%
Response.buffer=true
Sub DrawPage

 Set conn = Server.CreateObject("ADODB.Connection")
 conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
 "DATA SOURCE=" & server.mappath("../database/wamitab.mdb")
 conn.Open
SQL = "SELECT DISTINCT P.programTitle " _
    & " FROM Students AS S, StudentProgramSelections AS SP, Programs AS P " _
    & " WHERE S.studentID = SP.studentID " _
    & " AND SP.programID = P.programID " _
    & " AND S.studentID = " & Session("studentID") _
    & " AND SP.paid = Yes " _
    & " ORDER BY DatePosted DESC "
'response.write sql
'response.end
Set RS = conn.Execute( SQL )

If RS.EOF Then
      Session("Message") = "<font color='firebrick'>Wait! Something appears to be wrong with your registration. Please check with management.</font>"
    response.redirect "lectureindex.asp"
End If

allPrograms = RS.GetRows( ) ' get all the registered-for titles into an *ARRAY*
RS.Close ' no longer needed

Set FSO = Server.CreateObject("Scripting.FileSystemObject")

For row = 0 To UBound( allPrograms, 2 )
    programTitle = allPrograms( 0, row )
    postedDate = allPrograms(1, row)
%>
<HR width="10%">You viewing&nbsp;files in <font color="firebrick"><%=programTitle%> folder:</font>
<%
    ' show the directory for this program title
    Set fldr = FSO.GetFolder(Server.MapPath("ProgramFiles/" & programTitle ))
    For Each fl In fldr.Files
       pFile = programTitle & "/" & fl.Name
      pDate = fl.PosteDate  <-- I am having problem here
%>
 <center><img src="images/orangearrow.gif" border="0"><A HREF="getFile.asp?fname=<%=Escape(pFile)%>"><%=pFile%></A><%=pDate%></br></center>
<%
    Next
%>
<%
Next

%>
 <%
 End Sub

Problem in 2 areas.

1,      pDate = fl.PosteDate  <-- I am having problem here  
2,    <center><img src="images/orangearrow.gif" border="0"><A HREF="getFile.asp?fname=<%=Escape(pFile)%>"><%=pFile%></A><%=pDate%></br></center>


Thanks for your assistance
LVL 29
sammySeltzerAsked:
Who is Participating?
 
InterGlobalConnect With a Mentor Commented:
Sorry I could not help more...it is hard without seeing the data or stepping through the code....good luck :)
0
 
Patrick MatthewsCommented:
Hello sammySeltzer,

Is DatePosted defined in the database as datetime or as text?  If it is as datetime then
there should be no problem; if as text, try...

SQL = "SELECT DISTINCT P.programTitle " _
    & " FROM Students AS S, StudentProgramSelections AS SP, Programs AS P " _
    & " WHERE S.studentID = SP.studentID " _
    & " AND SP.programID = P.programID " _
    & " AND S.studentID = " & Session("studentID") _
    & " AND SP.paid = Yes " _
    & " ORDER BY CDate(DatePosted) DESC "

Regards,

Patrick
0
 
sammySeltzerAuthor Commented:
hi Patrick and thanks for your prompt response.

datePosted is defined as DateTime

But as stated in my original post, there are 2 problem areas.

1, this line: For row = 0 To UBound( allPrograms, 2 )
    For Each fl In fldr.Files
       pFile = programTitle '     pDate = programTitle & "/" & fl.postedDate& "/" & fl.Name
'     pDate = programTitle & "/" & fl.postedDate   <-- this is the offending line.  I am getting the following error

Object doesn't support this property or method: 'postedDate'

2, then this line:
 <center><img src="images/orangearrow.gif" border="0"><A HREF="getFile.asp?fname=<%=Escape(pFile)%>"><%=pFile%></A><%=fl.postedDate%></br></center>

is not doing anything


0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
InterGlobalCommented:
I know this might sound trivial....check your regional settings....sometimes access likes to display dates mm/dd/yyyy and sometimes dd/mm/yyyy  if it is not what you expect, then the dates might be sorting, but not the way you think?

and fl.PosteDate is spelled wrong...  fl.postedDate and you might want CreatedDate or LastModifedDate??
0
 
sammySeltzerAuthor Commented:
yea,

i changed to CreateDate but it is still not sorting. I am not sure if the reason is because of the time portion of date??
0
 
InterGlobalCommented:
SELECT DISTINCT P.programTitle " _
    & " FROM Students AS S, StudentProgramSelections AS SP, Programs AS P " _
    & " WHERE S.studentID = SP.studentID " _
    & " AND SP.programID = P.programID " _
    & " AND S.studentID = " & Session("studentID") _
    & " AND SP.paid = Yes " _
    & " ORDER BY DatePosted DESC "

  is missing the second column.... if you are going to call

    programTitle = allPrograms( 0, row )
    postedDate = allPrograms(1, row)   <---problem here

so try

SELECT DISTINCT P.programTitle, P.PostedDate " _
    & " FROM Students AS S, StudentProgramSelections AS SP, Programs AS P " _
    & " WHERE S.studentID = SP.studentID " _...................

The time portion of the date will not affect sorting, only grouping, comparison and formatting.  Unless you are doing a secondary sort alphabetical or something, but your query only has one sort column...so not the case either.


anycase, try

If RS.EOF Then
      Session("Message") = "<font color='firebrick'>Wait! Something appears to be wrong with your registration. Please check with management.</font>"
    response.redirect "lectureindex.asp"
End If

Set FSO = Server.CreateObject("Scripting.FileSystemObject")
DO WHILE NOT rs.EOF
    programTitle = rs("ProgramTitle")
    postedDate = rs(1)
%>
<HR width="10%">You viewing&nbsp;files in <font color="firebrick"><%=programTitle%> folder:</font>
<%
    ' show the directory for this program title
    Set fldr = FSO.GetFolder(Server.MapPath("ProgramFiles/" & programTitle ))
    For Each fl In fldr.Files
       pFile = programTitle & "/" & fl.Name
      pDate = fl.CreatedDate  <-- I am having problem here
%>
 <center><img src="images/orangearrow.gif" border="0"><A HREF="getFile.asp?fname=<%=Escape(pFile)%>"><%=pFile%></A><%=pDate%></br></center>
<%
    NEXT
%>
<%
LOOP %>

just a thought and might not be the solution to the issue, but worth a try anyways...
0
 
sammySeltzerAuthor Commented:
thanks very  much for your efforts but it won't run.

I click to run it and it just tries to run but then hangs.
0
 
InterGlobalCommented:
sorry sorry.....just before the LOOP
add rs.MoveNext
0
 
sammySeltzerAuthor Commented:
i have got to try and figure out what I am doing wrong  because it still giving the same un sorted records.

It is not even sorting by program title
0
 
sammySeltzerAuthor Commented:
thanks for trying.
0
 
sammySeltzerAuthor Commented:
i have gotten help somewhere and have it solved exactly the way i want it but I will give you the points for your efforts...thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.