469,898 Members | 2,259 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

asp/access/sql page timing out

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:

Expand|Select|Wrap|Line Numbers
  1. <%
  2.     Set conn = Server.CreateObject("ADODB.Connection")
  3.     conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\sport\gamedraw.mdb;Persist Security Info=False"%>
  4. <%
  5.     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   "
  6.         Set rs = Server.CreateObject("ADODB.Recordset")
  7.     rs.Open sql, conn, 3, 3
  8.     If rs.eof Then
  9.         rs.AddNew
  10.     End If
  11.     Set Session("gamedraw_rs") = rs
  12. %>
  13. <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>
  14. <TABLE width="550" BORDER="1" bordercolor="#000000" BGCOLOR="#ffffff" CELLPADDING="3" cellspacing="0">
  15.  
  16. <THEAD>
  17. <TR>
  18.  
  19. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Date</FONT></TH>
  20. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Team</FONT></TH>
  21. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Opposition</FONT></TH>
  22. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Time</FONT></TH>
  23. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Venue</FONT></TH>
  24. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Results</FONT></TH>
  25. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Comments</FONT></TH>
  26.  
  27. </TR>
  28. </THEAD>
  29. <TBODY>
  30. <%
  31. On Error Resume Next
  32. rs.MoveFirst
  33. do while Not rs.eof
  34.  %>
  35. <TR VALIGN=TOP>
  36.  
  37. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_date").Value)%><BR></FONT></TD>
  38. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_team").Value)%><BR></FONT></TD>
  39. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_opposition").Value)%><BR></FONT></TD>
  40. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_time").Value)%><BR></FONT></TD>
  41. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_venue").Value)%><BR></FONT></TD>
  42. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_results").Value)%><BR></FONT></TD>
  43. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_comments").Value)%><BR></FONT></TD>
  44.  
  45. </TR>
  46. <%
  47. rs.MoveNext
  48. loop%>
  49. </TBODY>
  50. <TFOOT></TFOOT>
  51. </TABLE>
  52.  
  53. <%
  54.     Set conn = Server.CreateObject("ADODB.Connection")
  55.     conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\sport\gamedraw.mdb;Persist Security Info=False"%>
  56. <%
  57.     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   "
  58.         Set rs = Server.CreateObject("ADODB.Recordset")
  59.     rs.Open sql, conn, 3, 3
  60.     If rs.eof Then
  61.         rs.AddNew
  62.     End If
  63.     Set Session("gamedraw_rs") = rs
  64. %>
  65. <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>
  66. <TABLE width="550" BORDER="1" bordercolor="#000000" BGCOLOR="#ffffff" CELLPADDING="3" cellspacing="0">
  67.  
  68.  
  69. <THEAD>
  70. <TR>
  71.  
  72. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Date</FONT></TH>
  73. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Team</FONT></TH>
  74. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Opposition</FONT></TH>
  75. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Time</FONT></TH>
  76. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Venue</FONT></TH>
  77. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Results</FONT></TH>
  78. <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000>Comments</FONT></TH>
  79.  
  80. </TR>
  81. </THEAD>
  82. <TBODY>
  83. <%
  84. On Error Resume Next
  85. rs.MoveFirst
  86. do while Not rs.eof
  87.  %>
  88. <TR VALIGN=TOP>
  89.  
  90. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_date").Value)%><BR></FONT></TD>
  91. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_team").Value)%><BR></FONT></TD>
  92. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_opposition").Value)%><BR></FONT></TD>
  93. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_time").Value)%><BR></FONT></TD>
  94. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_venue").Value)%><BR></FONT></TD>
  95. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_results").Value)%><BR></FONT></TD>
  96. <TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Verdana" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("game_comments").Value)%><BR></FONT></TD>
  97.  
  98. </TR>
  99. <%
  100. rs.MoveNext
  101. loop%>
  102. </TBODY>
  103. <TFOOT></TFOOT>
  104. </TABLE>
  105.  
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.
May 17 '07 #1
2 1323
jhardman
3,406 Expert 2GB
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
May 18 '07 #2
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!!
May 18 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Xenophobe | last post: by
14 posts views Thread by JK Peck | last post: by
2 posts views Thread by Chuck Ritzke | last post: by
9 posts views Thread by Anders K. Jacobsen [DK] | last post: by
2 posts views Thread by julie.siebel | last post: by
reply views Thread by champagne.supernova1974 | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.