putting values from database into a drop down list

Question

hi. my tool is in asp. i am using this code for a query in sql

dim req_id
req_id=Request.Form("Req_id")

if req_id<>"" then
Set conn=server.CreateObject("adodb.connection")
                    conn.Open session("Psrconnect")
                    Set rs=CreateObject("Adodb.Recordset")
                    rs.Open "select * from passwords where REQ_ID='"&req_id&"'", conn

i want to put the results of this query into a drop down list. how do i do it? any help is very much appreciated.

Answer 1

Slightly edited code from my working pages :

function HtmlFormOption( byval psReturnValue, byval psDisplayValue ,byval psCurrentDefault)
   dim x
   if IsNull(psCurrentDefault) then psCurrentDefault = ""
   if IsNull(psReturnValue) then psReturnValue = ""

   if lCase( cStr(psReturnValue) ) = lCase( cStr(psCurrentDefault)) then
      x = "selected "
   else
      x = ""
   end if
   HtmlFormOption = "<option " & x & "value='" & psReturnValue & "'>" & psDisplayValue & "</option>"
end function

   dim Result, sCode, sWaarde
   Result = "<select name='NameCombobox' size='1'>" & vbCrlf
   while not objRecLookup.Eof
      sCode = objRecLookup.Fields(0)    '  first field in result set
      sWaarde = objRecLookup.Fields(1)    ' second field in result set
      if not IsNull(sCode) and not IsNull(sWaarde) then
         Result = Result & HtmlFormOption( sCode, sWaarde , psCurrentDft )
      end if
      objRecLookup.MoveNext
   wend
   objRecLookup.Close
   Result = Result &  "</select>" & vbCrlf

And than Response.Write(Result)

Answer 2

Here's a simple solution:

<%

Dim objCommand, objRS
Set objCommand = Server.CreateObject("ADODB.Command")

with objCommand
    .ActiveConnection = objConn
    .CommandType = adCmdText
    .CommandText = "SELECT * FROM PASSWORDS WHERE REQ_ID= '" & req_id & "'"
    Set objRS = .Execute
end with

%><select name="selectbox"><%   

While NOT objRS.EOF
    %><option value="<%=objRS("COLUMN_NAME")%>"><%=objRS("COLUMN_NAME")%></option><%
    objRS.MoveNext
Wend

%></select><%

objRS.Close
Set objRS = Nothing
Set objCommand = Nothing

%>