Re: WHILE LOOP NOT WORKING IN INCLUDE ASP FILE - LOOPING FOREVER

simba wrote:
Hi ALL,

I have an issue with the while loop in include file. The main asp
template
works fine if I remove the while loop from the include file.

When I check db connections, it has many connection opens when it
executes
code in include file. Here is the code from my include file:

<%
QUERY = "SELECT * FROM " & TABLESLIDE & " WHERE ZONEID = '" &

Nothing to do with your problem but:
http://www.aspfaq.com/show.asp?id=2096

Looking at your loop, I see that the ONLY field you are using is the NUM
field ... there is no need to bring back ALL the fields!!! Change the query
to:

QUERY = "SELECT NUM FROM " & TABLESLIDE & ...

ZonesRS("ID") & "' AND DATE_START < " & Date2SQL(Now) & " AND
DATE_STOP > " & Date2SQL(Now) & " ORDER BY NUM"
Set TextPageNumRS = ObjConnection.Execute(QUERY)

Const adOpenStatic = 3
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open QUERY, ObjConnection, adOpenStatic

IF rs.RecordCount > 1 THEN
While Not TextPageNumRS.EOF

Nothing to do with your problem but, why are you opening an expensive static
cursor (rs) if you're not planning to do anything with it other than to
check if it contains records? Incredibly wasteful of resources. A more
efficient way to do this would be:

Set rs = ObjConnection.Execute(QUERY,,1)
if not rs.eof then
While ....

An even more efficient way would be to change your query to:
QUERY = "SELECT Count(*) FROM " & TABLESLIDE & _
" WHERE ZONEID = '" & ZonesRS("ID") & _
"' AND DATE_START < " & Date2SQL(Now) & _
" AND DATE_STOP > " & Date2SQL(Now) & _
" ORDER BY NUM"
Set rs = ObjConnection.Execute(QUERY,,1)
if rs(0) > 0 then
While ...

Oh my god!!! I just realized that you are attempting to open TWO recordsets
on the SAME query statement! Even if the OLE DB provider you are using
allows this to happen, it's insane to do this! (OK, "insane" is a bit
harsh - try "unnecessary" instead). Were these lines involving "rs" an
attempt to diagnose your problem? I hope so. Your code is the equivalent of:

Set TextPageNumRS = ObjConnection.Execute(QUERY,,1)
While Not TextPageNumRS.EOF

Again, nothing to do with your problem but "While" has been deprecated. You
should use:

do until TextPageNumRS.EOF
...
loop

Given that with ASP, the idea is to get in and out of your database as
quickly as possible, an even more efficient technique suggests itself: you
are not using any of the cursor functionality of the recordset container
(Sort, Filter, Move, etc.), and you are not intersted in any of the metadata
provided by the recordset, Bottom line: you do not need a cursor. It is more
efficient to loop through an array than through a recordset so in this
situation, the use of GetRows is indicated:

Set TextPageNumRS = ObjConnection.Execute(QUERY,,1)
Dim arData, i, num
If not TextPageNumRS.EOF then
arData=TextPageNumRS.GetRows
end if
TextPageNumRS.Close
set TextPageNumRS=nothing
'***close the connection now unless you have
'***further use for it
ObjConnection.Close:set ObjConnection=Nothing

If isArray(arData) then
for i = 0 to Ubound(arData,2)
num = arData(0,i)
'use num to build your html here
next
end if

%>
<a
href="default.asp?lan=<%=LANGUAGE%>&zone=<%=ZONE%>&num=<%=TextPageNumRS("NUM")%>"
onFocus="this.blur()"> <%IF TextPageNumRS("NUM") = Number
THEN%><font
color="red"><%=TextPageNumRS("NUM")%></font></a>
<%ELSE %><%=TextPageNumRS("NUM")%></a>

Get out of the habit of calling the same property of an object multiple
times. Assign the property value to a variable and re-use it (see above)

<%END IF %>
<%
TextPageNumRS.MoveNext
Wend
END IF

Other than the lack of efficiency, I don't see the problem (unless your
provider does not allow a second recordset to be opened while it is still
busy with the first recordset that was opened). Do you have "On Error Resume
Next" anywhere in the page? If so, comment it out and see if your page
generates any errors.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: fiter records in class module
    ... You can run a temporary parameter query from a module. ... allows you to keep changing criteria in a loop. ... For help with creating an SQL string for your database, ... ' See if recordset contains records: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Can I refer to a local recordset after the FROM clause ?
    ... LOOP until rstQ.EOF ... As the query takes too much time now because it has to access tblEmpoyee, ... The queries within the loop should now refer to the local opened recordsets ... an existing recordset rather than a table as normal. ...
    (microsoft.public.vb.database.ado)
  • Re: create function problem
    ... Actually I would like the calculated filed show on the query. ... "tina" wrote: ... record in the second recordset, within each loop of the first recordset. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Sending Email to multiple recipients
    ... I am using a query to gather the recipients info ... with the account number and email addresses of my report recipients. ... Then I loop through the recordset, ...
    (microsoft.public.access.formscoding)
  • Re: WHILE LOOP NOT WORKING IN INCLUDE ASP FILE - LOOPING FOREVER
    ... Date2SQL& " ORDER BY NUM" ... I have an issue with the while loop in include file. ... Set TextPageNumRS = ObjConnection.Execute ... provided by the recordset, Bottom line: you do not need a cursor. ...
    (microsoft.public.data.ado)