...

View Full Version : remove duplicates



Baleric
01-24-2007, 04:36 AM
hello guys,
i have a search string and i would like to remove the duplicates from a dropdown menu,

here is a screen shot to help you show what i mean 5008

here is the code im using for the dropdown menu.


<%
While (NOT rs.EOF)
%>
<option value="<%=(rs.Fields.Item("VARIANT").Value)%>"><%=(rs.Fields.Item("VARIANT").Value)%></option>
<%
rs.MoveNext()
Wend
If (rs.CursorType > 0) Then
rs.MoveFirst
Else
rs.Requery
End If
%>


please help,

cheeers


baleric

dudeshouse
01-24-2007, 09:36 AM
Rather than populating every record in the database with this recurring value, wouldn't it be easier to do a slight databse redesign and have a table storing these values? This would make more effective use of the one to many idea surely.
This way, when you populate the drop-down list, there is simply one table you need to call up.
Failing that, how about populating an array and checking the array each time you add a value to ensure it doesn't already exist, then populating your drop-down from the array...
HTH.
Glenn

Baleric
01-24-2007, 09:51 AM
Rather than populating every record in the database with this recurring value, wouldn't it be easier to do a slight databse redesign and have a table storing these values? This would make more effective use of the one to many idea surely.
This way, when you populate the drop-down list, there is simply one table you need to call up.
Failing that, how about populating an array and checking the array each time you add a value to ensure it doesn't already exist, then populating your drop-down from the array...
HTH.
Glenn

how would i go about making an array?

thanks alot for the help so far...

dudeshouse
01-24-2007, 12:23 PM
How about something like this....

Dim myArray() (put no. in if you know how many items in drop down)
counter = 0
writeflag = true

While (not rs.EOF)
If counter > 0 Then
varItem = rs.Fields.Item("VARIANT").Value
For i=0 to counter
If varItem = myArray(i) Then writeflag = false
Next
End If
If writeflag = true Then
myArray(counter) = rs.Fields.Item("VARIANT").Value
counter = counter + 1
End If
Redim PRESERVE myArray(counter)
rs.MoveNext
Wend

Please note that this is totally untested and I'm kinda guessing as to whether or not it'll work, but it's got to be worth a try, right?

Good luck, let me know how it goes.

Glenn

Baleric
01-24-2007, 08:25 PM
hi dudehouse,

thanks again for the help,

nothing is coming out of the array, i first got a subscript error, but that was resolved by placing a number in
Dim myArray(1)
also, is this script going to only show single records with no duplicates?

and 1 more thing, how do i get the array value? is it using the variable myarray?
<%response.write(myarray)%> ?

cheers mate

baleric

glenngv
01-25-2007, 11:02 PM
Depending on your SQL statement, you might just need to add the keyword DISTINCT to avoid duplicates in the recordset. If you post the SQL statement you are using, then we can look into it. If this doesn't work, you can populate the values in a Dictionary object wherein you will use the field value as the key.

<%
Dim oVariant, arrayKeys, item
Set oVariant= CreateObject("Scripting.Dictionary")
while not rs.EOF and not rs.BOF
oVariant.Add rs("VARIANT"), "anyvalueitdoesntmatter" 'add field value as key
rs.movenext
wend
'store all keys in an array
arrayKeys = oVariant.Keys
%>
<select>
<%
for each item in arrayKeys
%>
<option value="<%=item%>"><%=item%></option>
<%
next
%>
</select>

dudeshouse
01-30-2007, 11:31 AM
hi dudehouse,

thanks again for the help,

nothing is coming out of the array, i first got a subscript error, but that was resolved by placing a number in
Dim myArray(1)
also, is this script going to only show single records with no duplicates?

and 1 more thing, how do i get the array value? is it using the variable myarray?
<%response.write(myarray)%> ?

cheers mate

baleric

Glad you got that working... so far...

To pull the options out to populate your list, add something like:


For Each item In myArray
Response.Write "<option value=" & item & ">" & item & "</option>"
Next

Hope this helps. glenngv's way might be simpler, this was just the first one I though of... that's how I'd do it.

Cheers,

Glenn

degsy
01-30-2007, 01:37 PM
DISTINCT would be the most efficient way.

dudeshouse
01-30-2007, 01:50 PM
You're absolutely right. DISTINCT wasn't a term I'd come across previsouly. Learnt something new today.

Thanks.

Baleric
01-30-2007, 09:36 PM
thanks alot guys,
i couldnt get the code working by my deadline so i jsut made multiple recordsets,
i will definately need this code in future so thanky ou very much for the help :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum