Need some help with dynamic dropdown in For Loop

[Drew]

I am trying to build a small app that shows a Course Title from the
database, then displays a dropdown full of categories for the user to choose
one...

I thought a loop would be the best way to accomplish this, but since the
dropdown is dynamic, I am having problems. Can someone help me out here?

Here is my code,

For i = 1 to Num
Response.Write("<table width='100%' border='0' cellspacing='0'
cellpadding='0'><tr><td
width='52%'><%=(rsTitles.Fields.Item("Title").Value)%></td><td
width='48%'><select name='select'><option value=''>-- Select a
Category --</option><%While (NOT rsTitleCat.EOF)%><option
value='<%=(rsTitleCat.Fields.Item("CatID").Value)%>'><%=(rsTitleCat.Fields.Item("Category").Value)%></option><%rsTitleCat.MoveNext()
Wend If (rsTitleCat.CursorType > 0) Then rsTitleCat.MoveFirst Else
rsTitleCat.Requery End
If%></select></td></tr><tr><td>&nbsp;</td><td>&nbsp;</td></tr></table>")
Next

Thanks,
Drew

 

[Drew]

Here is some more info... The error I am getting is,

Error Type:
Microsoft VBScript compilation (0x800A03EE)
Expected ')'
/swvtc/DB/Emp/EmpTraining/TitleCategory.asp, line 221, column 126

Line 221 being,

Response.Write("<table width='100%' border='0' cellspacing='0'
cellpadding='0'><tr><td
width='52%'><%=(rsTitles.Fields.Item("Title").Value)%></td><td
width='48%'><select name='select'><option value=''>-- Select a
Category --</option><%While (NOT rsTitleCat.EOF)%><option
value='<%=(rsTitleCat.Fields.Item("CatID").Value)%>'><%=(rsTitleCat.Fields.Item("Category").Value)%></option><%rsTitleCat.MoveNext()
Wend If (rsTitleCat.CursorType > 0) Then rsTitleCat.MoveFirst Else
rsTitleCat.Requery End
If></select></td></tr><tr><td>&nbsp;</td><td>&nbsp;</td></tr></table>")

Column 126 being around,

=(rsTitles.Fields.Item("Title").Value)%

Anyone know what is going on?

Thanks,
Drew

 

[McKirahan]

I am trying to build a small app that shows a Course Title from the
database, then displays a dropdown full of categories for the user to choose
one...

I thought a loop would be the best way to accomplish this, but since the
dropdown is dynamic, I am having problems. Can someone help me out here?

Here is my code,

For i = 1 to Num
Response.Write("<table width='100%' border='0' cellspacing='0'
cellpadding='0'><tr><td
width='52%'><%=(rsTitles.Fields.Item("Title").Value)%></td><td
width='48%'><select name='select'><option value=''>-- Select a
Category --</option><%While (NOT rsTitleCat.EOF)%><option

Wend If (rsTitleCat.CursorType > 0) Then rsTitleCat.MoveFirst Else
rsTitleCat.Requery End
If%></select></td></tr><tr><td>&nbsp;</td><td>&nbsp;</td></tr></table>")
Next

Thanks,
Drew

Will this help? Watch for word-wrap.

<%
Dim s
s = ""
For i = 1 to Num
s = s & "<table width='100%' border='0' cellspacing='0'
cellpadding='0'>" & vbCrLf
s = s & "<tr>" & vbCrLf
s = s & " <td width='52%'>" & rsTitles("Title") & "</td>" & vbCrLf
s = s & " <td width='48%'>" & vbCrLf
s = s & " <select name='select'>" & vbCrLf
s = s & " <option value=''>-- Select a Category --</option>"
Do Until rsTitleCat.EOF
s = s & " <option value='" & rsTitleCat("CatID") & "'>"
s = s & rsTitleCat("Category") & "</option>" & vbCrLf
rsTitleCat.MoveNext
Loop
If rsTitleCat.CursorType > 0 Then
rsTitleCat.MoveFirst
Else
rsTitleCat.Requery
End If
s = s & " </select>" & vbCrLf
s = s & " </td>" & vbCrLf
s = s & "</tr>" & vbCrLf
s = s & "<tr>" & vbCrLf
s = s & " <td colspan='2'>&nbsp;</td>" & vbCrLf
s = s & "</tr>" & vbCrLf
s = s & "</table>"
s = s & "<br>"
Next
Response.Write(s)
%>

 

[Drew]

That got me on the right track... I see what you have done there!

Thanks a bunch!
Drew

 

[Bob Barrows [MVP]]

Drew,
Better yet, convert this to using a GetRows array. There is no reason to do
all the requerying etc. Recordset loops are very inefficient. See
www.aspfaq.com for an example (search using the keywords: recordset
iteration ).

Bob Barrows

 

[Drew]

It should be alright... this app is going to be used for this morning
only... just to get some Titles categorized... nothing else.

Thanks for your reply and I understand what you are saying, I just needed to
throw something together really quickly to save a little time.

Thanks,
Drew

 

[Bob Barrows [MVP]]

Bob,

With all due respect, I understand your point about recordsets...but
I dont like arrays - it makes the code almost unreadable and it

Maybe so (I've gotten used to it - it's not really that much harder), but
you can't beat it for pure performance. Only GetString is faster. The
bottleneck is looping through the recordset, which is a very complex
structure. Especially referring to the fields by name instead of by ordinal
position, which can slow things down quite a bit.

xpath queries are slow as well. I only use xml documents for passing data
across process boundaries (see http://www.davidpenton.com/testsite/tips/).
In the scenario you describe, you may as well disconnect your recordset,
close your connection and utilize your recordset object. IMO, you're not
really gaining anything by converting the recordset to xml and utilizing the
xml document in the same process. I've never compared the performance, so
YMMV.

becomes a bitch to sort out if you ever add/delete a column in the
middle of select statement.

As for new columns, I'm not sure why you'd ever put a new one in the middle
(there is nothing forcing anyone to consume the data in the order in which
they appear in a select statement), but the use of constants can mitigate
the pain (although I use dynamic sql for this illustration, do not take this
as an endorsement of its use).

sSQL = "select colQ, colE, colJ FROM ... "

const colQ = 0
const colE = 1
const colJ = 2

....
arData = rs.GetRows
rs.Close: set rs=nothing
conn.close: set conn = nothing

for iRow = 0 to ubound(arData,2)
response.write "colE contains " & arData(colE, iRow) & "; " & _
response.write "colQ contains " & arData(colQ, iRow) & ...
next

Then, when te statement changes to :

sSQL = "select colQ, colE,colB, colJ FROM ... "

All you need to do is revise the constants:

const colQ = 0
const colE = 1
const colB = 2
const colJ = 3


Bob Barrows