asp and access 1

tziviak · Dec 6, 2002

I'm trying to create a calender based on access-and I want to loop through the records-and if it finds for a certain date it should insert it in the box
it's not showing all the info
and also if there is more than one record per date it should show that too--I tried looping but I got into and endless loop(that's the way it seemed)I'm a beginner-so would appreciate if someone would help me
also what does <BR> do?
my code so far(just this part)

Dim iDay, iWeek, sFontColor, dictDte(31,2), intCount
strSql = "SELECT * FROM EvantCalendar WHERE month(dte)= " & month(dtCurViewMonth) & " and year(dte) = " & year(dtCurViewMonth) & " order by dte"

set rs = my_conn.Execute (StrSql)

intCount= 0

' populate array with days of month
on Error resume next

do until rs.EOF

if intCount > 31 then exit do
if Day(rs("dte&quot

) = intCount + 1 then
dictDte(intCount, 1) = rs("text_field&quot

else
dictDte(intCount, 1) = " "

End If
rs.Movenext
dictDte(intCount, 2) = intCount + 1
intCount = intCount + 1

loop

Thank you

Tarwn · Dec 6, 2002

Code:

<br>

is the html tag for a line break, same as hitting the enter key when your typing in a text document.

Ok, concerning your code. You will want to move your rs.MoveNext statement into the top portion of your if statement. This way it will only increment the recordset after it has used an entry. Then you will want to move your intCount increment into the else statement so that it will only incrememnt the count on that when there isn't a record available for that day (meaning there never was or you have moved past that date, already recording the values."

Next you should move the line where you are sertting the second part of the array above your if statement, since the incrememnt will be occurring in that else stmt now.

The last change you need to make is to the statement:

Code:

dictDte(intCount, 1) = rs(&quot;text_field&quot;)

Since you want this to hold multiple values, do this:

Code:

If dictDte(intCount,1) <> &quot;&quot; Then
   dictDte(intCount, 1) = dictDte(intCount, 1) & &quot;<br>&quot; & rs(&quot;text_field&quot;)
Else
   dictDte(intCount, 1) = rs(&quot;text_field&quot;)
End if

What this will do is if there is already a value in this (ie another event already placed in it for this date) than it will add a break tag and the new event. If there isn't anything in the array at that location yet then it assumes this is the first event for this date and puts it in there.

Hope this helps,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

tziviak · Dec 9, 2002

I tried the code-and it's not working properly-I'm only getting the 2nd entry on that date(not the first) and the rest of the information of that month-doesn't show up
what is wrong with my code?
thank you for your help

Dim iDay, iWeek, sFontColor, dictDte(31,2), intCount

'get calendar info
'getinfo="select * from main where tablename='" &calendarname &"';"
'set rsinfo=my_conn.execute(getinfo)

strSql = "SELECT * FROM evantcalendar WHERE year(dte) = " & year(dtCurViewMonth) &" AND month(dte)= " & month(dtCurViewMonth) & " ORDER BY Day(dte)"
'response.write strsql &"<BR>"
set rs = my_conn.Execute (StrSql)

intCount= 0
' populate array with days of month
do until rs.EOF or intCount = 31
if Day(rs("dte&quot

) = intCount + 1 then

if dictDte(intCount, 1) <> "" then

dictDte(intCount, 1) = dictDte(intCount, 1)& "<br>" & rs("text_field&quot

else
dictDte(intCount, 1) = rs("text_field&quot

end if

rs.Movenext
End If

dictDte(intCount, 2) = intCount + 1
intCount = intCount + 1
loop
rs.close

Tarwn · Dec 9, 2002

Ok, I think you may have missed the first portion of my post, here were the suggested changes:

Code:

do until rs.EOF
	if intCount > 31 then exit do

'Set the day counter portion of the array first

Code:

	dictDte(intCount, 2) = intCount + 1
	
	if Day(rs(&quot;dte&quot;)) = intCount + 1 then

'If there is already an entry, append the new one

Code:

		If dictDte(intCount,1) <> &quot;&quot; Then

'This appends the value from the current record to the other values already set for this date

Code:

			dictDte(intCount, 1) = dictDte(intCount, 1) & &quot;<br>&quot; & rs(&quot;text_field&quot;)
		Else

'Otherwise this is the first entry for the date

Code:

'Just set it directly, there is no value to append to

Code:

			dictDte(intCount, 1) = rs(&quot;text_field&quot;)
		End if
		rs.Movenext

'Move to next record now that we have used this one

Code:

	else

'the date in recordset is not this one, so it's time to move to the next day, we don't want to incrememnt the recordset because we haven't used the current record yet

Code:

		intCount = intCount + 1
	End If
loop

With those changes it ought to be working correctly, providing I made the right assumptions concerning the structure of your records in your recordset.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

tziviak · Dec 10, 2002

thank you-now it's working well
but now-when I click on an event-if there are more than one in a box-it just goes to the last one-I need that each even should be a separate link to the view_day.asp corresponding to that date-and not that all of the events should be a link to one event
the code that links to the page is: ( I put a little code b-4)

For iDay = 0 To 6
sBGCOLOR = "#003399"
If Month(dtCurViewDay) = Month(dtCurViewMonth) Then
If dtCurViewDay = dtToday Then sBGCOLOR = "#99ccff"
else
sBGCOLOR = "#003399"

End If
Response.Write "<span><a href=view_day.asp?" & "view_date=" & day(dtCurViewday) & "-" & monthname(month(dtCurViewday)) & "-"&Year(dtCurViewMonth)&"><TD HEIGHT=50 bgcolor='" & sBGCOLOR & "' style=""cursor:hand"" >"

If Month(dtCurViewDay) = Month(dtCurViewMonth) Then
If dtCurViewDay = dtToday Then
sFontColor = "#00FF00<i></i>"

Else
sFontColor = "#00FF00"
End If

'---- Write day of month
Response.Write "<FONT FACE=""Arial"" SIZE=""-2"" COLOR=""" & sFontColor & """><B>"
Response.Write "<a name=" &Day(dtCurViewDay) & "><a href=view_day.asp?" & "view_date=" & day(dtCurViewday) & "-" & monthname(month(dtCurViewday)) & "-"&Year(dtCurViewMonth)&">" & Day(dtCurViewDay) & "</B><br><font color='" &sFontColor &"'>" & formatStr(dictDte(Day(dtCurViewDay)- 1, 1)) & "</a>"

End If

Response.Write "&nbsp</TD></a></span>" & vbCrLf
dtCurViewDay = DateAdd("d", 1, dtCurViewDay)
Next
Response.Write "</TR>" & vbCrLf
Next
%>

so I probably need a loop to separate it
thank you

Tarwn · Dec 10, 2002

This probably won't work for you right away,

Code:

'---- Write day of month
Response.Write &quot;<FONT FACE=&quot;&quot;Arial&quot;&quot; SIZE=&quot;&quot;-2&quot;&quot; COLOR=&quot;&quot;&quot; & sFontColor & &quot;&quot;&quot;><B>&quot;
Response.Write &quot;<a name=&quot; &Day(dtCurViewDay) & &quot;>&quot;

Dim events, evnt
'Break up the events again for this date
Set events = Split(dictDte(Day(dtCurViewDay)-1,1),&quot;<br>&quot;)
For each evnt in events
	Response.Write &quot;<a href=view_day.asp?&quot; & &quot;view_date=&quot; & day(dtCurViewday) & &quot;-&quot; & monthname(month(dtCurViewday)) & &quot;-&quot;&Year(dtCurViewMonth)&&quot;>&quot;  & Day(dtCurViewDay) & &quot;</B><br>
	Response.Write &quot;<font color='&quot; &sFontColor &&quot;'>&quot; & formatStr(evnt) & &quot;</a>&quot;
Next

The problem with it is that your not passing anything concerning the event through your querystring, so no matter what event you click on a specific day you will still get the same page because your only passing the date.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

tziviak · Dec 10, 2002

thanks tarwn-you've been very helpful-I'll try this as soon as I can
BTW what does the 2 stand for in:

dictDte(31,2)

the 31-is the the amount of days in a month...(?)
thanx
-tziviak

Tarwn · Dec 10, 2002

The 2 is the upper bound on the second dimension, as an example (because I am lazy) here is what a (2) array looks like and then a (4,2) array:

Code:

tArray(2) structure:
 [0 value]
 [1 value]
 [2 value]

tArray(4,2)
 [0,0 value] [0,1 value] [0,2 value]
 [1,0 value] [1,1 value] [1,2 value]
 [2,0 value] [2,1 value] [2,2 value]
 [3,0 value] [3,1 value] [3,2 value]
 [4,0 value] [4,1 value] [4,2 value]

In most languages the 2 would stand for the number of elements for that dimension, in VB it is the upper bound starting at 0, so declaring an array like this:
Dim tArray(5)
actually gives you 6 slots in the array for data, tArray(0) through tArray(6).

In your case your not using all three parts of your second dimension, only 1 and 2,
so you could visualize this as:

Code:

 _Not Used__ __Event____ _Day of Month__
 [0,0 value] [0,1 value] [0,2 value]
 [1,0 value] [1,1 value] [1,2 value]
 [2,0 value] [2,1 value] [2,2 value]
 [3,0 value] [3,1 value] [3,2 value]
 [4,0 value] [4,1 value] [4,2 value]

except of course you have 31 "rows" not 4.

Of course you can have a 3 dimension, 4 dimension, etc array, but they are much harder to draw, and most people have difficulty visualizing past 3 or 4.

Hope this explained well enough,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

tziviak · Dec 10, 2002

yes, I understand arrays-but what is the 1 and 2 values used here for?
is it for how many values I can insert per day? (because now, I can put in as many events-more than 3)

Tarwn · Dec 10, 2002

Sort of. Basically this means you can store 3 variables (0, 1, 2) in each "row".
In your case, since your putting the values for events in as a single string, that value only counts as one. Think of it like a checkers board, your only allowed to have one piece on a square at a time, but it doesn't matter if that piece is just a simple little piece or a king, or a tower of stacked up checkers (extending the rules of the game a little here),

So for each slot in an array you could put in a single variable. Since VB is loosely typed, these values can be any type of value. In your instance you are putting a numeric value in the (x,2) slots (day of month) and a string in the (x,1) slot. It doesn't matter if this string looks like "a" or "a,a,a,a,a,a,a,a,a,a,a,,a,a,a,a,a,a,a,a,a" or

Code:

&quot;<table><tr><td>cell1</td><td>cell2</td></tr></table>&quot;

it is still a single string and only needs a single cell in the array.

With your code what your doing is putting all the events in the last "column" like this:

Code:

&quot;event1<br>event2<br>event3<br>etc&quot;

so it is considered a single string, and thus will fit in a single slot in the array.

Hope that helped clear the waters rather than muddy them more.
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
FAQ FAQ20-2863
= new Forums.Posting.General.GettingAnswers()

tziviak · Dec 12, 2002

actually I have an ID for each record-so how do I split it according to date-to display and then separating them by records-through ID to view different info?
the full calendar.asp code is this:

<%@ LANGUAGE="VBScript" %>

<%
'Option Explicit
dim calendarname,rsinfo,getinfo,daconn,fn_now
if request.querystring("calendar&quot

<> "" then
calendarname=request.querystring("calendar&quot

response.cookies("calendar&quot

=request.querystring("calendar&quot

else
calendarname=request.cookies("calendar&quot

end if

'response.write request.cookies("calendar&quot

Session("submitted&quot

= True
%>

<%
set my_conn= daconn

Function FormatStr(String)
on Error resume next
String = Replace(String, CHR(13), "&quot

String = Replace(String, CHR(10) & CHR(10), "</P><P>&quot

String = Replace(String, CHR(10), "<BR>&quot

FormatStr = String
End Function

Dim dtToday,my_conn,dbpath,payroll,strsql,rs,rs2,rs3,strsql2,stryear,rsyearly,cnpath
dtToday = Date()

Dim dtCurViewMonth ' First day of the currently viewed month
Dim dtCurViewDay ' Current day of the currently viewed month
Dim frmDate,tmpMonth,tmpYear,tmpDate,mnth ' Date submitted by form

' if the GO button was used, build the date from the month and year

If InStr(1, Request.Form, "subGO", 1) > 0 then
if Request.Form("CURDATE_month&quot

= "" then
tmpMonth = month(now())
else
tmpMonth = Request.Form("CURDATE_month&quot

End If

if Request.Form("CURDATE_year&quot

= "" then
tmpyear = year(now())
else
tmpyear = Request.Form("CURDATE_year&quot

End If

tmpDate = "1 " & tmpMonth & " 2001"

mnth = Month(tmpDate)
frmDate = DateSerial(tmpyear, mnth, 1)
Else

frmDate = Request.Form("CURDATE&quot

end if

if Request("view_date&quot

<> "" then
frmDate= DateSerial(year(Request("view_date&quot

), month(Request("view_date&quot

), 1)
end if

%>
<% REM This section defines functions to be used later on. %>
<% REM This sets the Previous Sunday and the Current Month %>
<%

'--------------------------------------------------
Function DtPrevSunday(ByVal dt)
Do While WeekDay(dt) > vbSunday
dt = DateAdd("d", -1, dt)
Loop
DtPrevSunday = dt
End Function
'--------------------------------------------------
%>
<%REM Set current view month from posted CURDATE, or
' the current date as appropriate.

' if posted from the form
' if prev button was hit on the form
If InStr(1, Request.Form, "subPrev", 1) > 0 Then
dtCurViewMonth = DateAdd("m", -1, frmDate)

' if next button was hit on the form
ElseIf InStr(1, Request.Form, "subNext", 1) > 0 Then
dtCurViewMonth = DateAdd("m", 1, frmDate)
' elseif request.querystring("subprev&quot

=1 then
' dtCurViewMonth = DateAdd("m", -1, frmDate)

' anyother time
Else
' date add in text box
If InStr(1, Request.Form, "subGO", 1) > 0 then
dtCurViewMonth = frmDate
Else
if Request("view_date&quot

<> "" then
dtCurviewMonth = frmDate
else
dtCurViewMonth = DateSerial(Year(dtToday), Month(dtToday), 1)
End If
End If

End If

%>

<% REM --------BEGINNING OF DRAW CALENDAR SECTION-------- %>
<% REM This section executes the event query and draws a matching calendar. %>
<%
Dim iDay, iWeek, sFontColor, dictDte(31,2), intCount

'get calendar info
'getinfo="select * from main where tablename='" &calendarname &"';"
'set rsinfo=my_conn.execute(getinfo)

strSql = "SELECT * FROM evantcalendar WHERE year(dte) = " & year(dtCurViewMonth) &" AND month(dte)= " & month(dtCurViewMonth) & " ORDER BY Day(dte)"
'response.write strsql &"<BR>"
set rs = my_conn.Execute (StrSql)

intCount= 0
' populate array with days of month
do until rs.EOF or intCount = 31

dictDte(intCount, 2) = intCount + 1

if Day(rs("dte&quot

) = intCount + 1 then

if dictDte(intCount, 1) <> "" then

dictDte(intCount, 1) = rs("text_field&quot

& "<br>" & dictDte(intCount, 1)
else
dictDte(intCount, 1) = rs("text_field&quot

end if

rs.Movenext

else
intCount = intCount + 1

End If

loop
rs.close

dim contactrs,consql
dim priconrs,prisql
'consql="select * from contacts where email<>'" &rsinfo("primary_contact&quot

&"' AND email in (select contact from calcontact where visible=yes AND calendar='" &rsinfo("title&quot

&"');"
'response.write con1sql
'set contactrs=my_conn.execute(consql)

'prisql="select * from contacts where email='" &rsinfo("primary_contact&quot

&"';"
'response.write prisql
'set priconrs=my_conn.execute(prisql)

%>
<html>
<head>

<title> - Web Calendar</title>
<style fprolloverstyle>A:hover {color: #00FF00; text-decoration: none}
</style>
</head>
<body text="#000000" link="#FFFFFF" vlink="#FFFFFF" alink="#FFFFFF">

<table border=0 width="100%">
<tr>
<td width="99%"><h1 align="center"><font color="#003399" size="4">Yeled
V'Yalda Event Calendar</font></h1>
</td>
</tr>
</table>
<table CELLPADDING="3" CELLSPACING="0" WIDTH="100%" BORDER="2" BGCOLOR="#000099" bordercolordark="#1C644C" bordercolor="#000099" bordercolorlight="#FFFFFF">
<tr VALIGN="MIDDLE" ALIGN="CENTER">
<form NAME="fmNextPrev" ACTION="index.asp" METHOD="POST"><input type="hidden" name="CURDATE" value="<%=dtCurViewMonth%>">
<td WIDTH="30%" ALIGN="RIGHT">

<input TYPE="image" NAME="subPrev" SRC="previousmonth.gif" BORDER="0" align="right"></span>

</td>
<td WIDTH="40%"><font FACE="Arial" COLOR="white"><b><%=MonthName(Month(dtCurViewMonth)) & " " & Year(dtCurViewMonth)%>  
</td>
<td WIDTH="30%" ALIGN="LEFT" colspan="7"> <input TYPE="image" NAME="subNext" SRC="nextmonth.gif" BORDER="0" HSPACE="0" VSPACE="0" align="absbottom">
</td>
</form>

</tr>
</table>
<table CELLPADDING="3" CELLSPACING="0" WIDTH="100%" BORDER="2" BGCOLOR="#0000FF" bordercolordark="#008000" bordercolor="#000099" bordercolorlight="#FFFFFF" style="border-collapse: collapse">
<tr VALIGN="TOP" ALIGN="CENTER" BGCOLOR="#000099">
<% For iDay = vbSunday To vbSaturday %>
<th WIDTH="14%"><font FACE="Arial" SIZE="-2" COLOR="#FFFFFF"><%=WeekDayName(iDay)%></font> </th>
<%Next %>
</tr>
<%
dtCurViewDay = DtPrevSunday(dtCurViewMonth)

For iWeek = 0 To 5
Response.Write "<TR VALIGN=TOP>" & vbCrLf

Dim sBGCOLOR
sBGCOLOR = "#99ccff"

For iDay = 0 To 6
sBGCOLOR = "#003399"
If Month(dtCurViewDay) = Month(dtCurViewMonth) Then
If dtCurViewDay = dtToday Then sBGCOLOR = "#99ccff"
else
sBGCOLOR = "#003399"

End If
Response.Write "<span><a href=view_day.asp?" & "view_date=" & day(dtCurViewday) & "-" & monthname(month(dtCurViewday)) & "-"&Year(dtCurViewMonth)&"><TD HEIGHT=50 bgcolor='" & sBGCOLOR & "' style=""cursor:hand"" >"

If Month(dtCurViewDay) = Month(dtCurViewMonth) Then
If dtCurViewDay = dtToday Then
sFontColor = "#00FF00<i></i>"

Else
sFontColor = "#00FF00"
End If

'---- Write day of month
Response.Write "<FONT FACE=""Arial"" SIZE=""-2"" COLOR=""" & sFontColor & """><B>"
Response.Write "<a name=" &Day(dtCurViewDay) & "><a href=view_day.asp?" & "view_date=" & day(dtCurViewday) & "-" & monthname(month(dtCurViewday)) & "-"&Year(dtCurViewMonth)&">" & Day(dtCurViewDay) & "</B><br><font color='" &sFontColor &"'>" & formatStr(dictDte(Day(dtCurViewDay)- 1, 1)) & "</a>"

End If

Response.Write "&nbsp</TD></a></span>" & vbCrLf
dtCurViewDay = DateAdd("d", 1, dtCurViewDay)
Next
Response.Write "</TR>" & vbCrLf
Next
%>
<%REM --------END OF DRAW CALENDAR SECTION--------

my_conn.Close
set my_conn = nothing
%></table>
<table width="100%"><tr><td width="33%"> </td><td width="33%">
<center> <p align="center"><form NAME="fmNextPrev2" ACTION="index.asp" METHOD="POST">
<select name="CURDATE_month" size="1">
<option value="<%=monthName(month(dtCurViewMonth))%>" selected><%=monthName(month(dtCurViewMonth))%> </option>
<option value="January">January </option>
<option value="February">February </option>
<option value="March">March </option>
<option value="April">April </option>
<option value="May">May </option>
<option value="June">June </option>
<option value="July">July </option>
<option value="August">August </option>
<option value="September">September </option>
<option value="October">October </option>
<option value="November">November </option>
<option value="December">December </option>
</select> <input TYPE="text" NAME="CURDATE_YEAR" VALUE="<%=year(dtCurViewMonth)%>" size="6">

<input type="submit" value="Click Me" name="subGO"></form> </p></td><td width="33%" valign="top">
<p align="right">
<font color=black>Yearly events are in </font><span style="background-color: #000099"><font color="#FFFF00"> 
yellow  </font></span></p>
</td></tr>
</table>

</body>

Thank you

tziviak · Dec 13, 2002

tarwn, do you have any ideas where I should start from?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

asp and access 1

tziviak

Technical User

Tarwn

Programmer

tziviak

Technical User

Tarwn

Programmer

tziviak

Technical User

Tarwn

Programmer

tziviak

Technical User

Tarwn

Programmer

tziviak

Technical User

Tarwn

Programmer

tziviak

Technical User

tziviak

Technical User

Similar threads

Part and Inventory Search

Sponsor