select dropdown restrict duplicate results

[fran7]

Hi, I am having trouble with this dropdown. With it I am passing an id to the next page but displaying a description in the dropdown. Trouble is it displays a description for every postid so I am getting multiple duplicate descriptions in the dropdown. like
drawing
drawing
drawing
ceramics ceramics.

I can see the problem as I am passing a unique postid its populating the dropdown with all the descriptions associated with my postids.

I need it to only populate the list with a description once for each postid. I have looked at group by etc but cannot see how it could be done if at all.

Any help would be great.
Thanks
richard

<select  onChange="window.location='httpp://www.xx.com/directory.asp?postcardid=' + this.options[this.selectedIndex].value">
<OPTION VALUE = 'Genres'>&nbsp;genres</Option>
<OPTION>-----------------------------------------------------</Option>

	
		<%		
		set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("xxx") & ";Persist Security Info=False"
conn.Open connect

	Set oRs=Server.CreateObject("adodb.recordset")
	strSQL = "SELECT DISTINCT Description,postid FROM tblGreetingPostCards Where CategoryID <> " & 60 & " and CategoryID<>" & 61 & " ORDER BY postid"
	oRs.Open strSQL, conn		
		
	Do while not oRs.EOF
		if Request.Form("GreetingPostCards") = oRs("postid") then 'if this is the selected one then display as selected
			Response.Write "<OPTION VALUE = '" & oRS ("Description") & "' SELECTED>"
			Response.Write oRs("postid") & "</Option>"
			oRs.MoveNext 
		else
			Response.Write "<OPTION VALUE = '" & oRs ("postid") & "'>&nbsp;"
			Response.Write oRs("Description") & ",</Option>"
			oRs.MoveNext 
		end if
	loop		
	%>
</SELECT>
</div>
  </form>

[DrBunchman]

Hi Richard,

I think the problem here is not your code but the design of your database.

If I understand correctly you are trying to create a drop down list from which you can select each different type of postcard.

e.g. if there were a total of three different descriptions of postcard then the drop down list would have three options: drawing, ceramic & painted, say.

Is this correct?

[fran7]

Dear Dr Bunchman,
Thanks for the reply.

Actually every postid has only one description. The trouble is some have the same description. I am trying to pass the postid but populate the dropdown with the descriptions but as many postids have the same description, the dropdown is populated with the same number of descriptions as postids and so they appear repeated.
I know its a tricky one. The thing is its easy for me to pass the description to the next page but as the description is often words like "still life", ie two words, I get a % in the address bar between the words and I am in an effort to make the pages more spider friendly trying to get the id in the address bar instead. So there lies the problem, many postids have the same description and they are all populating the dropdown. I was thinking maybe one could group the descriptions in the dropdown with a clause not to repeat twice the same description. A tricky one I think!
Thanks for your help
Richard

[DrBunchman]

But you could have many postid's with the same description - is that correct?

If so, when you create the drop down list how will you know which postid will match the selected description. If there are many postid's for each description then it is impossible to group them.

Is this the case?

[fran7]

Dear Dr Bunchman,
Thats correct. The thing is on the results page all works fine and all the postid for a particular description are returned. Its just getting the list of descriptions to show in the dropdown without repeats is the question. But I agree its prob impossible.
Thanks
richard

[DrBunchman]

To do this correctly you need to add an extra table to the database called Postcard (or something like that) which would have two columns Description and DescriptionID. Where your current tables have the column called Description you would now use DescriptionID to join to the new table.

Now you can fill your drop down properly using the new table. When a user selects a value it would pass the DescriptionID to the next page where you could use it in your query to get all records from tblGreetingPost Cards where they have a matching DescriptionID.

Does this all make sense? Let me know if not and I'll try to explain it a bit better! Of course you may not be able to change your database design in which case we'll have to consider a code solution.

Dr B

[fran7]

Dear Dr Bunchman,
That makes a lot of sense. I have some spare fields in my database which I could use. I think thats the best option. Thanks for your help, much appreciated.
Richard

[DrBunchman]

No problem Richard, glad I could help.