Question on Dynamic Array/Nested Loop approach

[John Wilson]

Hello,

I have the following code which populates as table data from a SQL
Server 2000 stored proc (RSByDemoID2). Below that is the view and
stored procedure which takes @DemoID as input to match to the
event_id. For Q? and Comments I am viewing/updating in a different
table than I am question and how_to_answer. The stored proc is
populated by a view that I'm using to get all these values from two
tables. My quandry is, I am getting the question and how_to_answer
records fine, but can only get Q1, because in that table the Q1 - Q10
and Q11_Text(which is Comments) is stored in one row per event_id. So,
should I use a nested loop to get these values out or do it in memory
in the ASP, perhaps using the Display Order matched against the Q1,
Q2, etc. In either case, could someone show me an example of how I
would do this. I would really appreciate a push in the right
direction. Thanks in advance, John.

ASP code:
<table border=1>
<tr>
<td><font face="Verdana, Arial, Helvetica"
size="1">Question</font></td>
<td><font face="Verdana, Arial, Helvetica" size="1">How to
Answer</font></td>
<td><font face="Verdana, Arial, Helvetica" size="1">Answer</font></td>
<td><font face="Verdana, Arial, Helvetica"
size="1">Comments</font></td>
</tr>

<%
questionCount = 0
Do While Not RSByDemoID2.Eof
questionCount = questionCount + 1
displayOrder = displayOrder
question = "question" & questionCount
how_to_answer = "how_to_answer" & questionCount
Q1 = "Q1" & questionCount
Q2 = "Q2" & questionCount
Q3 = "Q3" & questionCount
Q4 = "Q4" & questionCount
Q5 = "Q5" & questionCount
Q6 = "Q6" & questionCount
Q7 = "Q7" & questionCount
Q8 = "Q8" & questionCount
Q9 = "Q9" & questionCount
Q10 = "Q10" & questionCount
Q11_Text = "Comments" & questionCount
eventId = "event_id" & questionCount
%>
<!-- BEGIN QUESTIONS -->
<tr>
<input type="hidden" name="<%=eventId%>"
value="<%=RSByDemoID2("event_id")%>" >
<td><font face="Verdana, Arial, Helvetica"
size="1"><%=questionCount%>.&nbsp;<%=RSByDemoID2(" question")%></font></td>
<%
If Session("legalQ1") = True Then
Response.Write("<font color=""red"">(Answer to Question 1 must be
numeric)</font>")
End If
%>
<td><font face="Verdana, Arial, Helvetica" size="1"><input
type="text" size="35" name="<%=how_to_answer%>"
value="<%=RSByDemoID2("how_to_answer")%>" ></font></td>

<!-- BEGIN ARRAY (IPO HASHTABLE) FOR THE Q1 - Q11_Text. Match Q1 -
Q11_Text to "Display Order" -->
<!-- Maybe use Nested Loop -->
<td><font face="Verdana, Arial, Helvetica" size="1"><input
type="text" size="2" name="<%=Q?%>" value="<%=RSByDemoID2("Q?")%>"

</font></td>

<td><font face="Verdana, Arial, Helvetica" size="1"><input
type="text" size="20" name="<%=Comments%>"
value="<%=RSByDemoID2("Comments")%>" > </font></td>
<!-- END ARRAY OR NESTED LOOP FOR THE Q1 - Q11_Text -->
</tr>
<!-- END QUESTIONS -->

<%
RSByDemoID2.MoveNext
Loop
VIEW code:
CREATE VIEW dbo.ELRWebComputerLinkView
AS
SELECT
cl.event_id,
vq.version_question_id,
vq.display_order,
vq.question,
vq.how_to_answer,
cl.Q1,
cl.Q2,
cl.Q3,
cl.Q4,
cl.Q5,
cl.Q6,
cl.Q7,
cl.Q8,
cl.Q9,
cl.Q10,
cl.Q11_Text AS Comments

FROM
tblComputerLink cl,
Events e,
VersionQuestions vq

WHERE
e.version_id = vq.version_id and
cl.event_id = e.event_number and cl.resource = 0
Stored Proc code:
CREATE procedure spELRGetComputerLinkDataByDemoID @DemoID as
nvarchar(255)
as
select * from ELRWebComputerLinkView where event_id = @DemoID
GO