how to loop through the values of dynamically generated dropdown lists in classic ASP

[goodamr]

I have a HTML FORM, inside it,I retrieve multiple records from a table in the database. I populate each record in a row. Each row of the table has a checkbox (its value is the student ID of the record) and a dropdown list that contains 5 different form versions of an exam (A,B,C,D,E). I want the administrator to check the students he wants and choose the form for each student and when click submit button, it updates the form version for the selected students.

Here is a picture that demonstrate my form:


Here is my code which populates the table:

std_sql = "select ID, SSN, STD_NAME, COLL_DESC, MAJOR_DESC, FORM, CITY, NOTES from kfuban.el_exam_absence"
set std_rs = conntemp.execute(std_sql)

Dim mrecord_count
mrecord_count = 0
DO WHILE NOT std_rs.EOF

std_id = std_rs(0)
std_ssn = std_rs(1)
std_name = std_rs(2)
std_coll = std_rs(3)
std_major = std_rs(4)
std_form = std_rs(5)
std_city = std_rs(6)
std_notes = std_rs(7)

<tr>
<td align="center" width="35"><b><input type="checkbox" name="id_chk" value="<%=std_id%>" style="font-weight: 700"></b></td>
<td align="center" width="35"><b><%response.write mrecord_count+1%></b></td>
<td align="center" width="123"><b><%=std_id%></b></td>
<td align="center" width="124"><b><%=std_ssn%></b></td>
<td align="center" width="240"><b><%=std_name%></b></td>
<td align="center" width="100"><b>

<select dir="rtl" name="the_form" style="font-weight: 700">
<option value="" <%if std_form = "" then response.write "selected"%>>Choose Form</option>
<option value="A" <%if std_form = "A" then response.write "selected"%>> &nbsp; - A - &nbsp; </option>
<option value="B" <%if std_form = "B" then response.write "selected"%>> &nbsp; - B - &nbsp; </option>
<option value="C" <%if std_form = "C" then response.write "selected"%>> &nbsp; - C - &nbsp; </option>
<option value="D" <%if std_form = "D" then response.write "selected"%>> &nbsp; - D - &nbsp; </option>
<option value="E" <%if std_form = "E" then response.write "selected"%>> &nbsp; - E - &nbsp; </option>
</select>
</td>
</tr>
<%
std_rs.MoveNext
mrecord_count = mrecord_count + 1
Loop
%>

And here is the code when submit the form:

for i=1 to Request.form("id_chk").Count

ids = Request.form("id_chk")(i)
up_form = Request.form("the_form")(i)

upSQL="update kfuban.el_exam_absence set FORM = '"&up_form&"' where ID = '"&std_id&"'"
set RS=conntemp.execute(upSQL)

next

Sometimes, it works perfectly, but sometimes, the value of the submitted form value "up_form" is empty. Anyone know why? Please help me.

[jhardman]

So basically, correct me if I misunderstand, if you have five students, your form will have a list of 5 checkboxes that list the student ID, and 5 drop-down selects that list options A-E. Then if the admin wants to update this, he checks the checkbox corresponding to the student he wants to change, and changes the drop-down box and hits "submit".

There are two problems with this approach. first, if you have multiple inputs with the same name, then the data is sent as a comma-delimited text stream like this: st_id = "1022, 1023, 1020, 1056" Now if you can guarantee that those will always be in the right order, and will always include all of the data for which you are looking, then your approach of looping through that text as an array would be OK. But as I describe the second problem, maybe you will see what's wrong with that.

The second problem is that if a particular checkbox is left unchecked, then that value is not sent. In other words, if I use that same list I had above, but I only checked the second and fourth checkbox, then the response I get is st_id = "1023, 1056". Do you see the problem? When you loop through these and update your db, you are comparing the first section of this value to the first of the selects, but the first of the checkbox values that is sent is not the first checkbox on your form, it is the first checkbox that was checked. Does that make sense? So your form is submitted with perhaps two student IDs, but all of the selects, and your method has no way of showing which st_id matches which select.

The obvious solution is to number the inputs. Now this works best if you have a fixed number, or a max number of students per form, but even if you assigned an arbitrary number of 200 as the max number of students you should still be able to loop through these very quickly. Here's how I would code it:

<input type="checkbox" name="id_chk<%=mrecord_count%>" value="<%=std_id%>" style="font-weight: 700">
...
<select dir="rtl" name="the_form<%=mrecord_count%>" style="font-weight: 700">

then when I go to loop through the inputs and update the db, I loop like this:

for i=0 to 200
  
   if request.form("id_chk"&i) <> "" then
      ids = Request.form("id_chk" & i)
      up_form = Request.form("the_form" & i) 
      'include the rest of the update code
      'here, no reason to update if the row
      'wasn't checked
   end if
next

I suppose you could try counting the inputs so you didn't just pick an arbitrary number like 200 like I did, but I've never bothered.

And your approach would work for any input types besides checkboxes. You could probably modify your code to use radio buttons fairly easily.

Let me know if this helps.

Jared

[goodamr]

Hi Jared,

Thanks alot for your response. It works perfectly.

But the only problem is when we have a big list of records (e.g, more than 500 records), it takes some time to be executed.

[jhardman]

yes, this is a significant issue. The step that takes the longest is updating the db, so as long as not too many records are being updated per form submitted, then it should go really fast. The problem comes when you try to update 50-200 records with one submit. This is exactly the reason that many forms you see online use paged views (Here are the top 20 records, click here to see the next 20). But there is another potential solution:

When I update my db, I usually use the update function of the ADODB.recordset object.When you use this, you don't build an update statement. You load the table into a local "recordset" , then make changes to the local recordset, then call update(). The final update call performs all of the updates in one step. I still usually call update() after each row, just so a single error doesn't stop the whole thing, but a single update call after looping through the whole recordset should work much faster than updating each row separately. Here's how the code looks (I've changed the form a little, hopefully it should be obvious).

'the form code 
<input type="checkbox" name="id_chk<%=std_id%>" value="<%=std_id%>" style="font-weight: 700">
...
<select dir="rtl" name="the_form<%=std_id%>" style="font-weight: 700">

'the form handler
set objConn = server.createobject("adodb.connection")
objConn.open connectionString

set objRS = server.createobject("adodb.recordset")
objRS.open "select * from studentFormTable", objConn, adOpenDynamic, adLockOptimistic
'those last two arguments are constant 
'integers, if you haven't linked to the 
'right include file you can look those up, I 
'think they are 3 and 4 respectively

do until objRS.eof 'loop to the end of the table
   if request.form("id_chk"&objRS("std_id")) <> "" then
      objRS("the_form") = request.form("the_form" & objRS("std_id"))
   end if
   objRS.moveNext
loop
objRS.update()

Let me know if this makes sense.

Jared