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

Hi Bob,

here is my new modified code as you suggested:

<%
QUERY = "SELECT Count(*) FROM " & TABLESLIDE & " WHERE ZONEID = '" &
ZonesRS("ID") & "' AND DATE_START < " & Date2SQL(Now) & " AND DATE_STOP > " &
Date2SQL(Now) & " ORDER BY NUM"
Set TextPageNumRS = ObjConnection.Execute(QUERY,,1)

If Not TextPageNumRS.EOF Then
arData = TextPageNumRS.GetRows()
End If

TextPageNumRS.Close
Set TextPageNumRS=nothing

If isArray(arData) Then
For i = 0 to Ubound(arData,2)
num = arData(0,i)
%>
<a href="default.asp?lan=<%=LANGUAGE%>&zone=<%=ZONE%>&num=<%=num%>"
onFocus="this.blur()">
<%IF num = Number THEN%><font color="red"><%=num%></font></a>
<%ELSE %><%=num%></a>
<%END IF %>
<%
Next
End If
%>

isArray(arData) always come up as False though there are 5 rows.

Any suggestions.

Thanks for quick reply.

"Bob Barrows [MVP]" wrote:

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"



.