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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with loops

Status
Not open for further replies.
jamiecottonuk

jamiecottonuk

Technical User
Oct 29, 2007
12
GB
Hi all,

I am trying to display data from a database. I want the page to be displayed as

January
event 1

Febraury

March
event 2
event 3

April

etc etc etc.

So if there is an event in that month then display the event

Code: 
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="connection.asp"-->
<%
	Dim searchRecordset,searchSQL
	Set searchRecordset = Server.CreateObject("ADODB.Recordset")
	searchSQL="SELECT * FROM month, events WHERE month.id = events.monthId"
	Set searchRecordset= DBConnection.Execute(searchSQL)
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<title>Untitled Document</title>
	</head>
	<body>
		<p>
		<%
			dim i
			i = 1
			Do while Not i = 13
				Response.Write("<table>")
				Response.Write("<tr>")
				Response.Write("<td>"& MonthName(i) &"</td>")
				Response.Write("</tr>")
				Response.Write("<tr>")
				Do while Not searchRecordset.eof
					IF searchRecordset("month") = MonthName(i) THEN
						Response.Write("<td>"&searchRecordset("event")&"</td>")
					END IF
					searchRecordset.MoveNext
				LOOP
				Response.Write("</tr>")
				Response.Write("<tr>")
				Response.Write("<td>&nbsp;</td>")
				Response.Write("</tr>")
				Response.Write("<tr>")
				Response.Write("<td>&nbsp;</td>")
				Response.Write("</tr>")
				Response.Write("</table>")
				i=i+1
			Loop
			searchRecordset.close
			Set searchRecordset = nothing
			'closing the connection
			DBConnection.close
			Set DBConnection = nothing
		%>
		</p>
	</body>
</html>

I thought this would do it the months are displayed but no events are being displayed under the correct months and I dont understand. Could someone help?
 
Sort by date Sort by votes
I think there is a problem with your SQL but I don't know enough about your table structure to say exactly what to do about it.

One thing that is clearly wrong is that the inner loop will only be entered for the first month because, after the first month, the recordset will be at EoF. There should probably be a MoveFirst at the top of the outer loop to ensure the entire inner loop is evaluated every time.

Another approach... probably a better approach... what if you move the query itself into the outter loop and only query for one month at a time? Wouldn't that be more simple?


On final tip is that the test for continuing the outer loop is better as [tt]i <= 12[/tt] instead of [tt]i = 13[/tt]. This kind of thing is done not so much for you, but for the next guy that might need to maintain your code in a few years. The reason is suppose it somehow skipped 13 and went on to 14... now you have an infinate loop! It shouldnt happen but suppose the next guy made a mistake and incremented the variable [tt]i[/tt] inside the loop... it would keep growing indefinately. Its a little thing but worth changing.
 
Upvote 0 Downvote
Thanks for the quick reply. Sorry but I dont get the following statement

"Another approach... probably a better approach... what if you move the query itself into the outter loop and only query for one month at a time? Wouldn't that be more simple?"

This is the results from the SQL query

Code: 
id month id eventName monthId date 
2 February 1 Go Karting 2 20/02/2007 09:00:00 
2 February 2 Alton Towers 2 02/02/2007 09:00:00 
3 March 3 Pleasure Beach 3 06/03/2007 10:00:00 
4 April 4 Crazy Golf 4 15/04/2007 09:00:00 
4 April 5 Cinema 4 15/04/2007 11:00:00 
4 April 6 Blackpool 4 20/04/2007 08:00:00 
4 April 7 Chessington Park 4 25/04/2007 15:00:00

The first 2 columns are from the table "month" the next 4 are from the table "events
 
Upvote 0 Downvote
Given that the value in the MonthID field is equal to the numeric month of the year, and furthermore given the existance of VBScript's MonthName function, there does not appear to be any need to the months table.

Actually, since your events table has a DATE field, the MonthID field is also kinda superflous.

 
Upvote 0 Downvote
This example uses a separate query for each month:
Code: 
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="connection.asp"-->
<%
    Dim searchRecordset,searchSQL
    Set searchRecordset = Server.CreateObject("ADODB.Recordset")
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
        <title>Untitled Document</title>
    </head>
    <body>
        <p>
        <%
            dim i
            For i = 1 to 12
                Response.Write("<table>")
                Response.Write("<tr>")
                Response.Write("<td>"& MonthName(i) &"</td>")
                Response.Write("</tr>")

                searchSQL="SELECT * FROM events WHERE monthId = " & i
                Set searchRecordset= DBConnection.Execute(searchSQL)

                if searchRecordset.eof then
                    Response.Write "<tr><td>&nbsp;</td></tr>" & vbCrLf
                else
                    Do while Not searchRecordset.eof                    
                        Response.Write "<tr><td>"&searchRecordset("event")&"</td></tr>" & vbCrLf
                        searchRecordset.MoveNext
                    LOOP
                    searchRecordset.close
                end if

                Response.Write "<tr><td>&nbsp;</td></tr>" & vbCrLf
                Response.Write "<tr><td>&nbsp;</td></tr>" & vbCrLf

                Response.Write("</table>")
            Next
            
            Set searchRecordset = nothing
            'closing the connection
            DBConnection.close
            Set DBConnection = nothing
        %>
        </p>
    </body>
</html>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
259
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
187
JScannell
JScannell
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
147
leifoet
leifoet
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
321
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
278
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top