looping question

Domenico · August 8th, 2003, 03:34 AM

I want to make an agenda with items that are ordered by date. Data is coming from an access database. I would like to order the data by month and by year. Example:
september 2003
cursus 1
cursus 2
november 2003
cursus 3
cursus 4
january 2004
cursus 5...and so on.

I want to update the data with a web based update form that I make so everything must be so flexible possible.

Any suggestions?

Thanks,
Domenico

sporkman43 · August 8th, 2003, 10:49 AM

I'd recommend using three columns for the date. One for year, and two for month (int and string). Assign a numeric value for month, then assign it to call the name when you print it out. Start with an order by year. Order descending so the largest is on top. Next compare records where the year is the same (just use a simple boolean check, if equal then), and order the records based on the month. Do a print of all the sorted records with a loop command that ends at EOF, replacing the number of the month with the word. That is your display.

For the updates make a page with a form that posts to whatever your ASP page is, and on the ASP page put something like this in:
<html>
<body>
' this is your connection string to the database

<%
'set up recordset and boolean variable
Dim objRS, bolFound
'set variable to true
bolFound = True
'if the entry form is blank then they must go back and enter again
If (Request.Form("year") = "") Then
bolFound = False
Response.Write "<a href='adddate.html'>You must enter the year.<p></a>"
End If
If (Request.Form("month") = "") Then
bolFound = False
Response.Write "<a href='adddate.html'>You must enter the month.<p></a>"
End If
'if they made it thru the error check they add the date
If bolFound = True then
'set the recordset up
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "Agendatable", objConn, , 3, 2
'add new record
objRS.AddNew
objRS("year") = Request.Form("year")
objRS("month") = Request.Form("month")
objRS.Update
End If
'confirmation
Response.Write "Date has been entered"
Response.Write "<a href='adddate.html'> Add another date. </a>"
Response.Write "<a href='index.html'> Return Home. </a>

%>
</body>
</html>

sporkman43 · August 8th, 2003, 10:56 AM

Quick note: The post form is just a simple html form page-
<HTML>
  <HEAD><TITLE>Add date</TITLE></HEAD>
  <BODY>
    <FORM METHOD=POST ACTION="Adddate.asp">
      <TABLE>
        <TR>
          <TD ALIGN=RIGHT>Year:</TD>
          <TD ALIGN=LEFT><INPUT TYPE=INT NAME="year">
          </TD>
        </TR>
        <TR>
          <TD ALIGN=RIGHT>Month:</TD>
          <TD ALIGN=LEFT><INPUT TYPE=INT NAME="month">
          </TD>
        </TR>
<INPUT TYPE=RESET><INPUT TYPE=SUBMIT VALUE="Add Date">
</body>
</html>

sporkman43 · August 8th, 2003, 11:01 AM

I just realized that the month should be type=text, and you need to add a little script in the asp page that says something like
dim varmonth
If month="January"
then varmonth = "1"

then set the add month call from
objRS("month") = Request.Form("month")
to
objRS("month") = varmonth

Domenico · August 8th, 2003, 02:28 PM

this is great!!! Thank you for you help. I will try this.

kilika · August 8th, 2003, 03:49 PM

maybe I am not understanding, but can't you just ORDER BY DATE in the query?

If you get a field back with the date, you can access the text versions from it.

:)
If rs("TheDate").Value = #6/5/2004"

If you do
Variable = Month(rs("TheDate").Value)
then Variable then = 6

If you do
Variable = MonthName(Month(rs("TheDate").Value))
then Variable = "June"

If you do
Variable = Day(rs("TheDate").Value)
then Variable = 5

If you do
Variable = Year(rs("TheDate").Value)
then Variable = 2004

If you do
Variable = WeekDay(rs("TheDate").Value)
then Variable = 6

If you do
Variable = WeekDayName(rs("TheDate").Value)
then Variable = "Friday"

You can run through the loop ordered by the date and have variables that you load the month name into. When you look at a new record, compare it to the previous month name. If different, you can print a heading using the above samples to get the month and years from the new date.

Chris