Populate Dropdown from SQL in Classic ASP

Question

I am trying to force users to only select certain values when adding a record. So naturally I am using a dropdown, but I'd like the options to be populated by a specific field in the database. I figured I'd do a Do/Loop but I am apparently doing something wrong.

Dim dstrSQL
Dim drs
    dstrSQL = "SELECT EventID FROM Events"
    set conn2 = CreateObject("ADODB.Connection")
    conn2.open CONN_STRING
    set drs = conn2.execute(dstrSQL)
    conn2.close: set conn2 = nothing
Do
        Response.Write "<option value=" & drs.Fields(0) & " >" & drs.Fields(0) & "</option>"
        drs.MoveNext
Loop

Answer 1

Hey,

Wow, that's been a while... drs is a reader object? Don't you have to close the object after all the reading has been done, so after the loop?

Answer 2

It's been a long time. Something like this:

conn2.open CONN_STRING
set drs = conn2.execute(dstrSQL)

do while not drs.eof %>

    <option value="<%= drs.Fields(0) %>" ><%= drs.Fields(0) %></option>
    <% drs.MoveNext
Loop
 conn2.close
 set conn2 = nothing %>