Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder Baleric's Avatar
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    332
    Thanks
    0
    Thanked 0 Times in 0 Posts

    remove duplicates

    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 remove duplicates-demo.jpg

    here is the code im using for the dropdown menu.

    Code:
    <%
    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

  • #2
    New Coder
    Join Date
    Nov 2006
    Location
    Bristol, UK
    Posts
    85
    Thanks
    6
    Thanked 6 Times in 6 Posts
    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

  • #3
    Regular Coder Baleric's Avatar
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    332
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dudeshouse View Post
    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...

  • #4
    New Coder
    Join Date
    Nov 2006
    Location
    Bristol, UK
    Posts
    85
    Thanks
    6
    Thanked 6 Times in 6 Posts
    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
    Last edited by dudeshouse; 01-24-2007 at 12:26 PM. Reason: Code incorrect

  • #5
    Regular Coder Baleric's Avatar
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    332
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    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?
    Code:
    <%response.write(myarray)%> ?
    cheers mate

    baleric

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    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.
    Code:
    <%
    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>
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #7
    New Coder
    Join Date
    Nov 2006
    Location
    Bristol, UK
    Posts
    85
    Thanks
    6
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Baleric View Post
    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
    Code:
    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?
    Code:
    <%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:

    Code:
    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

  • #8
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    DISTINCT would be the most efficient way.

  • #9
    New Coder
    Join Date
    Nov 2006
    Location
    Bristol, UK
    Posts
    85
    Thanks
    6
    Thanked 6 Times in 6 Posts
    You're absolutely right. DISTINCT wasn't a term I'd come across previsouly. Learnt something new today.

    Thanks.

  • #10
    Regular Coder Baleric's Avatar
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    332
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •