?
Solved

Adding date field to script - how?

Posted on 2007-07-28
11
Medium Priority
?
174 Views
Last Modified: 2010-04-24
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
0
Comment
Question by:sammySeltzer
  • 6
  • 4
11 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19586445
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
 
LVL 29

Author Comment

by:sammySeltzer
ID: 19586499
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
 
LVL 1

Expert Comment

by:InterGlobal
ID: 19587012
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Author Comment

by:sammySeltzer
ID: 19587993
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
 
LVL 1

Expert Comment

by:InterGlobal
ID: 19592298
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
 
LVL 29

Author Comment

by:sammySeltzer
ID: 19592600
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
 
LVL 1

Expert Comment

by:InterGlobal
ID: 19592620
sorry sorry.....just before the LOOP
add rs.MoveNext
0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 19592670
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
 
LVL 1

Accepted Solution

by:
InterGlobal earned 300 total points
ID: 19592727
Sorry I could not help more...it is hard without seeing the data or stepping through the code....good luck :)
0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 19592752
thanks for trying.
0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 19723993
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question