Eliminate Duplicate Vaules In ASP Combo Box

[mrgulic]

I get duplicate values when recalling a record back to edit it (say from "tbl_A". The combo box shows duplicate values for both the associated record from "tbl_A" and the combos initial source values from say table "tbl_B" database.

For example: tbl_A contain client account records and tbl_B is a table that contain's possible account status's (like Open, Closed, Froze, Etc..)

When I pull up a record to edit it the combo shows what the current saved value was (for example "Closed"), but the combo also show all the possibilities from tbl_B.

Combo in a new state
Combo box: [-- Assign Catagory --]

Combo in a close state (with associated saved record)
Combo box: [Closed]

Combo in an open state
Combo box: [Closed]
[Open ]
[Closed]
[Froze ]

sqlCat = "SELECT * FROM tblCategory;"
rsCat.Open sqlCat, adoCon

sql = "SELECT * FROM tblCategory;"
rs.Open sql, adoCon


<select>
  <option selected value="">-- Assign Catagory --
<%  If rsCat.RecordCount > 0 Then
%>
  <option selected value="<%=rsCat("CID")%>">
    <%=rsCat("txt_Cat_Title")%>
      </option>
<%        Do While Not rsCat.EOF %>	
           <option value="<%=rsCat("CID")%>">
	     <%=rsCat("txt_Cat_Title")%>
	   </option>
<%          rsCat.MoveNext
	  Loop
    End If
%>
</select>

I hope that my crude ASCII representation of a combo box helped in explaining my problem.

Thanks is advance for your time.

 

[mrgulic]

There was a slight typo in my code. Here is the corrected version.

<select>
  <option selected value="">-- Assign Catagory --
<%  If rsCat.RecordCount > 0 Then
%>
  <option selected value="<%=[b][red]rs[/red][/b]("CID")%>">
    <%=[b][red]rs[/red][/b]("txt_Cat_Title")%>
      </option>
<%        Do While Not [b]rsCat[/b].EOF %>    
           <option value="<%=[b]rsCat[/b]("CID")%>">
         <%=[b]rsCat[/b]("txt_Cat_Title")%>
       </option>
<%          rsCat.MoveNext
      Loop
    End If
%>
</select>

Also, I figured out one why to do this. I don't know if its the best way, but another problem has occured.

The selected value shows at the top of the combo box rather then in alphabetical order (BTW: I left ORDER by out in my original example to save space). The other items show in the right order but the saved value is at the top of the combo list.

Here is how i got around the initial problem:

sqlCat = "SELECT * FROM tblCat" & _
" HAVING (((tblCat.CatID)<>" & strCat & "))" & _
" ORDER BY tblCat.txt_Cat_Title;"

"strCat" being the ID associated with the recalled record.

 

[thompom]

sqlCat = "SELECT * FROM tblCategory GROUP BY txt_Cat_Title;"

 

[damber]

What is the difference in the recordsets ? both return all categories in your table (presumably the table B you speak of).

Your use of them indicates that the 'selected' item is in fact just the first you come to in the list.. a little arbitrary no ?

What you need to do is simply create a single recordset for all the categories (SELECT txt_Cat_Title FROM tblCategory).

First, if strCat has no value, create your "--Assign Category--" option with the selected attribute.

Then when you are looping through the recordset to output the <option> elements in your html, conditionally check the current records category against the strCat variable that you use in your second posts query, but not in your first post's code at all. If it is the same, then output the "selected" attribute to the option element otherwise dont. This will make that entry in the drop down box the one selected.



A smile is worth a thousand kind words. So smile, it's easy!

 

[mrgulic]

Sorry for wasting your time. It was late and I entered incorrect information.

I decided to do it a different way.

Thanks.