Re: Looping!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Sorry, McKirahan....you are right........the cause of the script
timeout error is because of the absence of objRS2.MoveNext. Also please
don't mind about excluding the comments. I did it just for brevity.

"strNAM=objRS1(iLoop)" is wrong; it should be "strNAM=objRS2(0)". Since
objRS2 will always have only 1 column at a time (only the DISTINCT
records of 1 column are being populated in each drop-down), using
"strNAM=objRS2(iLoop)" will generate an error; so the records under
that only column can be referenced using objRS2(0) (or
objRS2(0).Value). This is the changed script which is working fine:

<%
Dim strSQL1,strSQL2
strSQL1 = "SELECT * FROM tblRecords"

Dim objRS1,objRS2
Set objRS1 = objConn.Execute(strSQL1)

Dim aSTR()
ReDim aSTR(0)
Dim iSTR
iSTR = 0
Dim sSTR

Sub Append(sSTR)
sSTR = sSTR & ""
If iSTR > UBound(aSTR) Then
ReDim Preserve aSTR(UBound(aSTR) + 1)
End If

aSTR(iSTR)=sSTR & vbCrLf
iSTR=iSTR + 1
End Sub

Function Concat()
ReDim Preserve aSTR(iSTR)
Concat=Join(aSTR,"")
Erase aSTR
ReDim aSTR(0)
iSTR=0
End Function

Dim iLoop,strCOL,strNAM
For iLoop = 0 To objRS1.Fields.Count-2
strSQL2="SELECT DISTINCT(" & objRS1(iLoop).Name & ") FROM
tblRecords WHERE " & objRS1(iLoop).Name & " IS NOT NULL ORDER BY " &
objRS1(iLoop).Name

Set objRS2=objConn.Execute(strSQL2)

strCOL=objRS1(iLoop).Name
Append "<select size='1' name='dd" & strCOL & "'
onChange='gotoURL(this.form.dd" & strCOL & ")' >"
Append "<option>Select</option>"

Do Until objRS2.EOF
strNAM=objRS2(0).Value
Append "<option value='Records.asp?colname=" & strCOL &
"&record=" & strNAM & "'>" & strNAM & "</option> "
objRS2.MoveNext
Loop
Append "</select>"
Next
Response.Write Concat()
%>

but it takes about 30 seconds to execute the script & that's too long a
time! Note that I am working on my local intranet IIS5 server (Win2K
Pro).

There's another doubt I have in my mind. When the code gets executed
for the first time, the "If" condition doesn't get executed because
iSTR=0 & aSTR being empty, UBound(aSTR)=0. Am I right? Then aSTR(0)
becomes equal to "<select size=1 name='ddCol1.......>" (for the 1st
column) & iSTR becomes equal to 1. So

aSTR(0)="<select size=1 name='ddCol1.......>"

but how is aSTR(0) being Preserved? On subsequent code execution, the
"If" condition gets satisfied; so the previous item in aSTR gets
Preserved but how is aSTR(0) getting Preserved? What is the flow of
execution of the code? Please clarify this point.

Actually I have never worked with Preserving & Erasing array items; so
these doubts are coming to my mind!

Thanks once again,

Regards,

Arpan

.

Relevant Pages

  • Re: CreateEventProc for OLE button crashes Excel
    ... doesn't under normal execution, then you have a timing issue. ... > It adds a worksheet with a button, and clicking the button is supposed to do ... > Public Sub BuildWorksheet() ... Dim WSName As String ...
    (microsoft.public.excel.programming)
  • Re: last solution
    ... Michael Bauer - MVP Outlook ... If the loop starts, i.e. if the execution goes on with the last line, ... Dim objInbox As MAPIFolder ... Dim response As String ...
    (microsoft.public.outlook.program_vba)
  • Re: WHERE IN() with parameters
    ... > The code you provided is just laced with performance killers. ... > and recreating stored procedures means updates to system tables. ... > compiled for their first execution so on top of the hit from updating the ... >> Dim bulkstring As String ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Memory problem (I think ???)
    ... no page breaks show after execution. ... Dim rng As Range, rng2 As Range, ar As Range ... Dim ScrArea As String ... Set rng = FormatRegion ...
    (microsoft.public.excel.programming)
  • Re: Dynamic loading of javascript files into web pages
    ... scripting library" into your favorite search engine (it's invariably ... and order of execution doesn't matter? ... mind doing research, but I do mind the "I know, but I won't tell" attitude. ... That attitude is in all in your head. ...
    (comp.lang.javascript)