Problem with drop down list using data from database

[michellerobbins56]

Hi there

I have created a drop down list which is called User Type. The data in this drop down list is populated by data in a database. This is all fine. However my problem is not the saving to the database (this works fine), my problem is displaying the 'selected' value and all the other items in the list. Here is my code (I've copied and pasted it from notepad, not sure how to make it more readable I'm afraid):


'-----------------------------------------
Dim rsUserType, LstUserType

Set rsUserType = Server.CreateObject("ADODB.Recordset")
strSQL = "Select user_type_id, user_type from tuUserType ORDER BY user_type"
rsUserType.Open strSQL, cnnConn, 1, 4
rsUserType.MoveFirst

If strUserType="0" then 'nothing selected in register form
LstUserType = LstUserType & "<option value='0' SELECTED>Please select</option>"
While not rsUserType.EOF
LstUserType = LstUserType & "<option value='" & rsUserType("user_type") & "'>" & rsUserType("user_type") & "</option>"
rsUserType.MoveNext
WEND
else 'something was selected
LstUserType = LstUserType & "<option value='" & strUserType & "' selected>" & strUserType & "</option>"
While not rsUserType.EOF
LstUserType = LstUserType & "<option value='" & rsUserType("user_type") & "'>" & rsUserType("user_type") & "</option>"
rsUserType.MoveNext
WEND
end if


rsUserType.Close
ListUserType = ListUserType & "<option value='Other'>Other...</option>"

'------------------------------------

strUserType represents the value in the database for usertype.

There are three values in the database: "end user", "manufacturer" and "trade". It works fine if nothing has been selected in the database: the text "please select" is displayed. However if a value has been selected I find that value is displayed (because it is the 'selected' value) but it is also displayed a second time when I display the rest of the items in the list. My question is how can I display the rest of the items in the list APART from the selected value? For example if "manufacturer" was selected this would be displayed as the selected value (which is what I want) but then the rest of the list has "end user", "manufacturer" and "trade" so you can see "manufacturer" is displayed twice which is not what I want.

Thank you very much for any help with this problem.

 

[emozley]

You are better off putting your IF something has been selected inside the loop rather than outside:

<select name="UserType">
<option value="0">Please Select</option>
<%
Do While Not rsUserType.EOF
%>
<option <%
If strUserType=rsUserType("user_type") Then Response.Write("SELECTED")
%> value="<% = sUserType("user_type") %>">

<%
Response.Write(strUserType)
%>

</option>

<%
TBL.MoveNext
Loop
%>

 

[michellerobbins56]

Thanks for your reply. I have tried something similar to that but it didn't work unfortunately.

Is there another way of making sure I can display all other items in the drop down list while ensuring the value that is selected is only displayed once as well as the other items in the list?

 

[emozley]

There might be other ways but I am fairly certain the approach I have suggested is the easiest. Do you get any errors when you try it or does it look wrong? If it looks wrong please can you click View...Source and then paste the HTML produced into this forum.

cheers

 

[Tarwn]

I, to, would support emozleys method of output. The only difference between that method and your original method (from the user's viewpoint) is that with your method the "Please Select" doesn't show up after something is selected and the selected option is always at the top. We can do that though:

<%
LstUserType = "<select name=""UserType"">"

'if the "please select" was selected or nothing has been selected, show the "Please select"
If Request("UserType") = 0 Or Request("UserType") = "" Then
   LstUserType = LstUserType & "<option value=""0"" selected> Please Select </option>"
Else
   'display the previously selected item at the top ofthe list
   LstUserType = LstUserType & "<option value=""" & Request("UserType") & """>" & Request("UserType") & "</option>"
End If

'display the rest of the options in a loop
Do Until rsUserType.EOF
   'make sure this wasn't the selected value before displaying
   If rsUserType("user_type") <> Request("UserType") Then
      LstUserType = LstUserType & "<option value=""" & rsUserType("user_type") & """>" & rsUserType("user_type") & "</option>"
   End If

   rsUserType.MoveNext
rsUserType.Loop

'display the "other" option
LstUserType = LstUserType & "<option value=""other""> Other </option>"

This is the same concept as emozleys, centralizing the loop logic, except I have also included your logic of displaying the "Please select" or previously selected item at the top of the list. It also does an if check inside the loop to make sure it doesn't output the previously selected value a second time, which will be ignored if the previous value was the "Please Select" or this is the first load since none of your database values will match the one from the Request object.

Hope that helps,
-T