Populating drop down options with a DB field

[Kris912]

I am trying to populate dropdown options in a form so the user can make the selection and when they hit submit the jobs the contractor is involved with will show in another section of the page. I have 13 records in the database. As of now it is populating the options with one record 13 times. I guess my logic is off. Also, ultimately I would like to do a string comparison on the field I'm querrying so that when the dropdown gets populated there are no duplications.

Here is what I have so far...

<form action="Contractor_Job_Search2.asp" method="get">
<table width="700" border="0" cellspacing="0" cellpadding="0">
<tr><td><img src="img/spacer.gif" width="100" height="10"></td></tr>
<tr>
<td align="right">
<%
set connection = server.createobject("ADODB.Connection")
connection.open session("DBConnString")


sql = "select * from Projects"
sql = sql & " order by Contractor DESC"

set rs = connection.execute(sql)

Contractor=rs("Contractor")
%>

<%
response.Write "<select name=Contractor size=1 class=FieldValue>"
response.Write "<option value= selected>" & "Select Contractor" & "</option>"
While Not rs.EOF
response.Write "<option value=" & Contractor & ">" & Contractor & "</option>"
counter = counter +1
rs.MoveNext
Response.Flush()
WEND
response.write "</select>"
%>
</td>
</tr>
<tr><td><img src="img/spacer.gif" width="100" height="10"></td></tr>
<tr>
<td colspan="2" align="right"><input type="submit" name="submit" value="Submit" class="button">&nbsp;&nbsp;
<input type="reset" name="reset" value="Reset" class="button"></td>
</tr>
</table>
</form>

Here is the code that is supposed to show all the job titles for the selected contractor...

<%
Contractor = request.form("Contractor")

if request.form("Contractor") = "" then
response.Write("Please select a General Contractor and press enter!")
else
response.write "<font color=#000000 size=2>" & Contractor & "</font>" & "<br><br>"
While Not rs.EOF
Title=rs("Title")
response.Write Title & "<br>"
counter = counter +1
rs.MoveNext
Response.Flush()
WEND
if Contractor = "" then
response.Write"<strong>"& ("There are no projects involving") & " " & request.form("Contractor") & " " & ("at this time") & "<strong>"
end if
end if
%>

I would really appreciate any help you could give me
Thanks

 

[DotNetGnat]

you have Contractor=rs("Contractor") setting only once...you need to set it inside your loop...

-DNG

 

[Kris912]

Thanks DNG!

It is populating correctly now BUT the second half of the code that is supposed to be displaying the job titles for the selection isn't happening. I wrote the code very similar to what I have here and it works great for a keyword search. But this doesn't seem to be getting the selection passed to it. Can you spot what's wrong? Also, do you know where I might find a string comparison function that only populates the drop down menu with the unique entries in my contractor field?

I really appreciate your help, you always respond so quickly! Thanks

 

[Sheco]

Perhaps the problem is that, after the first loop, your recordset is already at EOF so it never entered the second loop?

Or maybe you have a .MoveFirst in there and I didnt see it...