asp/access/sql page timing out

[nick0123]

Newbie-ish here...

I have made a simple database in MS Access 2003 listing sports teams, their playing dates, opposition venues etc. The database has six tables, one for each sport.

Exporting the first table as an asp page lets me view the details for that particular sport.

To view all the other sports on the same page, I copied/pasted the sql code, replacing the names of the sport.

Locally, this page takes about a second to generate. But loaded onto our intranet server, it always times out after loading only the first sport. I am wondering if I needed to change any part of the sql code in case it is trying to 'overwrite' itself to load the next sport?

The code below is a section of the page, taking in two of the six sports:

<%
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\sport\gamedraw.mdb;Persist Security Info=False"%>
<%
    sql = "SELECT basketball.game_date, basketball.game_team, basketball.game_opposition, basketball.game_time, basketball.game_venue, basketball.game_results, basketball.game_comments FROM basketball   ORDER BY basketball.game_team   "
        Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    If rs.eof Then
        rs.AddNew
    End If
    Set Session("gamedraw_rs") = rs
%>
<table width="550" border="0" bgcolor="#000000"><tr><td width="200"><font face="Verdana" size="2" color="#FFE400"><b>Basketball</b></font></td><td width="350" align="right"><a href="/sport/dbeditor/" class="black"><font face="Tahoma" size="1">edit</a></font></td></tr></table>
<TABLE width="550" BORDER="1" bordercolor="#000000" BGCOLOR="#ffffff" CELLPADDING="3" cellspacing="0">
 
<THEAD>
<TR>
 
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Date</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Team</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Opposition</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Time</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Venue</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Results</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Comments</FONT></TH>
 
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
 %>
<TR VALIGN=TOP>
 
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_date").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_team").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_opposition").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_time").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_venue").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_results").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_comments").Value)%><BR></FONT></TD>
 
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
 
<%
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\sport\gamedraw.mdb;Persist Security Info=False"%>
<%
    sql = "SELECT hockey.game_date, hockey.game_team, hockey.game_opposition, hockey.game_time, hockey.game_venue, hockey.game_results, hockey.game_comments FROM hockey   ORDER BY hockey.game_team   "
        Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    If rs.eof Then
        rs.AddNew
    End If
    Set Session("gamedraw_rs") = rs
%>
<p></p><table width="550" border="0" bgcolor="#000000"><tr><td width="200"><font face="Verdana" size="2" color="#FFE400"><b>Hockey</b></font></td><td width="350" align="right"><a href="/sport/dbeditor/" class="black"><font face="Tahoma" size="1">edit</a></font></td></tr></table>
<TABLE width="550" BORDER="1" bordercolor="#000000" BGCOLOR="#ffffff" CELLPADDING="3" cellspacing="0">
 
 
<THEAD>
<TR>
 
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Date</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Team</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Opposition</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Time</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Venue</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Results</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Comments</FONT></TH>
 
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
 %>
<TR VALIGN=TOP>
 
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_date").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_team").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_opposition").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_time").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_venue").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_results").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_comments").Value)%><BR></FONT></TD>
 
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
 

I have searched the forums here, but haven't found anything similar to this.

Suggestions would be welcomed, as I imagine there is a far more economical way to code this. Many thanks.

[jhardman]

Nick,

This times-out even before the second sport is displayed? It sounds like a loop isn't executing correctly. Since it works once then breaks, I would guess that an error is occurring between the two loops which negates your loop advance mechanism. This would normally stop the script, but your "on error" line is keeping it going. Take out the "on error" line and see what errors are coming up.
(oh, and you shouldn't have two "on error" lines. I don't think that is causing the problem, but you never need two)

Jared

[nick0123]

Jared, thanks so much for your help. I did what you suggested and removed the onError lines and the error that appeared was to do with html encoding of the final field - game_comments.

I removed it from the first query and it was fine, but errored on the second sport so had to remove them all.

Now the asp page generates in about a second when on the server - I still don't understand why it worked fine from http://localhost so well but not from the server, but at least the problem is fixed.

Thanks again for the help - much appreciated!!