Limiting duplicates in a dynamic dropdown? - Code included

Discussion in 'ASP General' started by Kingdom, Sep 24, 2003.

  1. Kingdom

    Kingdom Guest

    I'm using this script to dynamicaly populate 2 dropdowns and dispaly the
    results. Choose a component type from the first drop down, lets say
    'car' and the second box will list all the car 'manufacturers' and the
    display will then provide all the rest of the info from the other fields.

    I need to eliminate all the duplicates in the First Drop Down as it
    currently displays an entry for every record, many are identical, I might
    have over 2,000 records but only around 15 componet_types, which would
    ideal for this drop down.

    CODE ASP 3
    ========================================

    <!-- Start First Drop Down -->

    <%
    Set objDC = Server.CreateObject("ADODB.Connection")
    objDC.Open Application("Database1_connectionstring")

    Set objRS = objDC.Execute("Select Component_Type FROM Parts_Table")
    %>
    <table border="0" cellpadding="0" cellspacing="0" style="border-
    collapse: collapse" width="100%" id="AutoNumber1">
    <tr>
    <td width="3%">&nbsp;</td>
    <td width="77%">
    <FORM METHOD="POST" NAME="Form1" ACTION="systems.asp">
    <p align="left">
    <SELECT NAME="Component_Type" SIZE="1" ONCHANGE=Form1.submit()>
    <option selected><% = Request.Form("Component_Type") %>
    </option>
    <%
    ' Continue until we get to the end of the recordset.
    Do Until objRS.EOF
    ' For each record we create a option tag and set it's value to
    the Component_Type
    %>
    <OPTION><%= objRS("Component_Type") %></OPTION>
    <%
    ' Get next record
    objRS.MoveNext
    Loop
    %>
    </SELECT> <b><font face="Arial" size="2" color="#000080">Choose a
    Component Type</font></b></p>
    </FORM>
    <%

    ' Close Data Access Objects and free DB variables
    objRS.Close
    Set objRS = Nothing
    objDC.Close
    Set objDC = Nothing
    %>


    <!-- End first Drop Down -->
    <!--Second drop down -->

    <%
    'Some code to hide the second drop down until we make a selection from
    the first
    IF Request.Form("Component_Type") = "" Then
    Else
    'If Component_Type has a value then we get a list of parts for the second
    drop down
    Set objDC = Server.CreateObject("ADODB.Connection")
    objDC.Open Application("Database1_connectionstring")

    Set objRS = objDC.Execute("Select Products FROM Parts_Table WHERE
    Component_Type = '" & Request.Form("Component_Type") & "'")
    %>
    <FORM METHOD="POST" NAME="Form2" ACTION="systems.asp">
    <p align="left">
    <font face="Arial"><font color="#008080"><b>
    <SELECT NAME="Products" SIZE="1" ONCHANGE=Form2.submit()>
    <option selected><% = Request.Form("Products") %></option>
    <%
    ' Continue until we get to the end of the recordset.
    Do Until objRS.EOF
    ' For each record we create a option tag and set it's value to
    the Products
    %>
    <OPTION><%= objRS("Products") %></OPTION>
    <%
    ' Get next record
    objRS.MoveNext
    Loop
    %>
    <%
    'Set a hidden value in the second form for the Component_Type
    'so we can pass it along with the Products to the next query
    %>
    </SELECT></b></font><b><font size="2" color="#008080"> </font>
    <font size="2" color="#000080">Choose a Product</font></b><font
    color="#000080"><b><input type="hidden" name="Component_Type" value="<% =
    Request.Form("Component_Type") %>"></b></font></font></p>
    </FORM>

    <%
    ' Close Data Access Objects and free DB variables
    objRS.Close
    Set objRS = Nothing
    objDC.Close
    Set objDC = Nothing
    End IF
    %>

    <!-- Display the records -->

    </td>
    <td width="20%"><img border="0" src="compsystem200X120.gif"></td>
    </tr>
    </table>
    <table border="0" cellpadding="0" cellspacing="0" style="border-
    collapse: collapse" width="600" id="AutoNumber2">
    <tr>
    <td width="55" valign="top"><b>
    <font face="Arial" size="2" color="#000000">Details: </font><font
    face="Arial" size="2" color="#000080"> <% Response.Write Products & " " &
    Component_Type %> </font>
    </b>
    </td>
    <td width="311">
    <%
    'Make sure we have submitted a Product and don't show results until we do
    IF Request.Form("Products") = "" Then
    Else
    Set objDC = Server.CreateObject("ADODB.Connection")
    objDC.Open Application("Database1_connectionstring")

    Set objRS = objDC.Execute("Select * FROM Parts_Table WHERE Component_Type
    = '" & Request.Form("Component_Type") & "' AND Products = '" &
    Request.Form("Products") & "'")

    'Loop through the database and assign the appropriate values to variables
    'that we will use later

    Do Until objRS.EOF
    Manufacturer = objRS("Manufacturer")
    Description = objRS("Description")
    Model_No = objRS("Model_No")
    Products = objRS("Products")
    Part_Number = objRS("Part_Number")
    Price = objRS("Price")
    Image = objRS("Image")
    Options = objRS("Options")
    Component_Type = objRS("Component_Type")
    objRS.MoveNext
    Loop

    objRS.Close
    Set objRS = Nothing
    objDC.Close
    Set objDC = Nothing
    %>
    </div>
    <p align="left"><b><font face="Arial" size="2" color="#000080"><%
    Response.Write Products & " - " & Component_Type %> </font>
    </b>
    <br>
    <font face="Arial" size="2" color="#000080">
    <%
    'Set up the display of the record
    Response.Write "Manufacturer - " & Manufacturer & "<br>"
    Response.Write Description & "<br>"
    Response.Write "Model No. " & Model_No & "<br>"
    Response.Write Products & " - " & "Part No. " & Part_Number & " Price
    " & Price & "<br>"
    IF Options <> "" Then
    Response.Write "Options:- " & Options & "<br>"
    Response.Write "Image " & Image & "<br>"
    End IF
    End IF
    %>
    Kingdom, Sep 24, 2003
    #1
    1. Advertising

  2. > Set objRS = objDC.Execute("Select DISTINCT Component_Type FROM
    Parts_Table")

    That ought to do it.


    --
    William Morris
    Product Development, Seritas LLC

    "Kingdom" <> wrote in message
    news:Xns9400A036D2F53kingdomofwizdom@194.117.133.134...
    > I'm using this script to dynamicaly populate 2 dropdowns and dispaly the
    > results. Choose a component type from the first drop down, lets say
    > 'car' and the second box will list all the car 'manufacturers' and the
    > display will then provide all the rest of the info from the other fields.
    >
    > I need to eliminate all the duplicates in the First Drop Down as it
    > currently displays an entry for every record, many are identical, I might
    > have over 2,000 records but only around 15 componet_types, which would
    > ideal for this drop down.
    >
    > CODE ASP 3
    > ========================================
    >
    > <!-- Start First Drop Down -->
    >
    > <%
    > Set objDC = Server.CreateObject("ADODB.Connection")
    > objDC.Open Application("Database1_connectionstring")
    >
    > Set objRS = objDC.Execute("Select Component_Type FROM Parts_Table")
    > %>
    > <table border="0" cellpadding="0" cellspacing="0" style="border-
    > collapse: collapse" width="100%" id="AutoNumber1">
    > <tr>
    > <td width="3%">&nbsp;</td>
    > <td width="77%">
    > <FORM METHOD="POST" NAME="Form1" ACTION="systems.asp">
    > <p align="left">
    > <SELECT NAME="Component_Type" SIZE="1" ONCHANGE=Form1.submit()>
    > <option selected><% = Request.Form("Component_Type") %>
    > </option>
    > <%
    > ' Continue until we get to the end of the recordset.
    > Do Until objRS.EOF
    > ' For each record we create a option tag and set it's value to
    > the Component_Type
    > %>
    > <OPTION><%= objRS("Component_Type") %></OPTION>
    > <%
    > ' Get next record
    > objRS.MoveNext
    > Loop
    > %>
    > </SELECT> <b><font face="Arial" size="2" color="#000080">Choose a
    > Component Type</font></b></p>
    > </FORM>
    > <%
    >
    > ' Close Data Access Objects and free DB variables
    > objRS.Close
    > Set objRS = Nothing
    > objDC.Close
    > Set objDC = Nothing
    > %>
    >
    >
    > <!-- End first Drop Down -->
    > <!--Second drop down -->
    >
    > <%
    > 'Some code to hide the second drop down until we make a selection from
    > the first
    > IF Request.Form("Component_Type") = "" Then
    > Else
    > 'If Component_Type has a value then we get a list of parts for the second
    > drop down
    > Set objDC = Server.CreateObject("ADODB.Connection")
    > objDC.Open Application("Database1_connectionstring")
    >
    > Set objRS = objDC.Execute("Select Products FROM Parts_Table WHERE
    > Component_Type = '" & Request.Form("Component_Type") & "'")
    > %>
    > <FORM METHOD="POST" NAME="Form2" ACTION="systems.asp">
    > <p align="left">
    > <font face="Arial"><font color="#008080"><b>
    > <SELECT NAME="Products" SIZE="1" ONCHANGE=Form2.submit()>
    > <option selected><% = Request.Form("Products") %></option>
    > <%
    > ' Continue until we get to the end of the recordset.
    > Do Until objRS.EOF
    > ' For each record we create a option tag and set it's value to
    > the Products
    > %>
    > <OPTION><%= objRS("Products") %></OPTION>
    > <%
    > ' Get next record
    > objRS.MoveNext
    > Loop
    > %>
    > <%
    > 'Set a hidden value in the second form for the Component_Type
    > 'so we can pass it along with the Products to the next query
    > %>
    > </SELECT></b></font><b><font size="2" color="#008080"> </font>
    > <font size="2" color="#000080">Choose a Product</font></b><font
    > color="#000080"><b><input type="hidden" name="Component_Type" value="<% =
    > Request.Form("Component_Type") %>"></b></font></font></p>
    > </FORM>
    >
    > <%
    > ' Close Data Access Objects and free DB variables
    > objRS.Close
    > Set objRS = Nothing
    > objDC.Close
    > Set objDC = Nothing
    > End IF
    > %>
    >
    > <!-- Display the records -->
    >
    > </td>
    > <td width="20%"><img border="0" src="compsystem200X120.gif"></td>
    > </tr>
    > </table>
    > <table border="0" cellpadding="0" cellspacing="0" style="border-
    > collapse: collapse" width="600" id="AutoNumber2">
    > <tr>
    > <td width="55" valign="top"><b>
    > <font face="Arial" size="2" color="#000000">Details: </font><font
    > face="Arial" size="2" color="#000080"> <% Response.Write Products & " " &
    > Component_Type %> </font>
    > </b>
    > </td>
    > <td width="311">
    > <%
    > 'Make sure we have submitted a Product and don't show results until we do
    > IF Request.Form("Products") = "" Then
    > Else
    > Set objDC = Server.CreateObject("ADODB.Connection")
    > objDC.Open Application("Database1_connectionstring")
    >
    > Set objRS = objDC.Execute("Select * FROM Parts_Table WHERE Component_Type
    > = '" & Request.Form("Component_Type") & "' AND Products = '" &
    > Request.Form("Products") & "'")
    >
    > 'Loop through the database and assign the appropriate values to variables
    > 'that we will use later
    >
    > Do Until objRS.EOF
    > Manufacturer = objRS("Manufacturer")
    > Description = objRS("Description")
    > Model_No = objRS("Model_No")
    > Products = objRS("Products")
    > Part_Number = objRS("Part_Number")
    > Price = objRS("Price")
    > Image = objRS("Image")
    > Options = objRS("Options")
    > Component_Type = objRS("Component_Type")
    > objRS.MoveNext
    > Loop
    >
    > objRS.Close
    > Set objRS = Nothing
    > objDC.Close
    > Set objDC = Nothing
    > %>
    > </div>
    > <p align="left"><b><font face="Arial" size="2" color="#000080"><%
    > Response.Write Products & " - " & Component_Type %> </font>
    > </b>
    > <br>
    > <font face="Arial" size="2" color="#000080">
    > <%
    > 'Set up the display of the record
    > Response.Write "Manufacturer - " & Manufacturer & "<br>"
    > Response.Write Description & "<br>"
    > Response.Write "Model No. " & Model_No & "<br>"
    > Response.Write Products & " - " & "Part No. " & Part_Number & " Price
    > " & Price & "<br>"
    > IF Options <> "" Then
    > Response.Write "Options:- " & Options & "<br>"
    > Response.Write "Image " & Image & "<br>"
    > End IF
    > End IF
    > %>
    WIlliam Morris, Sep 24, 2003
    #2
    1. Advertising

  3. Kingdom

    Kingdom Guest

    "WIlliam Morris" <> wrote in
    news:bkscov$54qri$-berlin.de:

    >> Set objRS = objDC.Execute("Select DISTINCT Component_Type FROM

    > Parts_Table")
    >
    > That ought to do it.
    >
    >
    > --
    > William Morris
    > Product Development, Seritas LLC
    >


    Yep! That worked perfectly - thanks
    Kingdom, Sep 24, 2003
    #3
  4. Kingdom

    Kingdom Guest

    -> Still having a problem displaying the image

    As you can see I'm very new to ASP and just can't learn this stuff quick
    enough.

    I have a field with the url to the image folder and would like to display
    the image, but on the right hand side of the page in another cell.

    so the output looks like:-

    details: (the output) IMAGE
    (the output)
    (the output)

    Any help appreciated

    CODE ASP 3 (PARTIAL ONLY)
    -----------------------------------

    <p align="left"><b><font face="Arial" size="2" color="#000080"><%
    Response.Write Products & " - " & Component_Type %> </font>
    </b>
    <br>
    <font face="Arial" size="2" color="#000080">
    <%
    'Set up the display of the record
    Response.Write "Manufacturer - " & Manufacturer & "<br>"
    Response.Write Description & "<br>"
    Response.Write "Model No. " & Model_No & "<br>"
    Response.Write Products & " - " & "Part No. " & Part_Number & " Price
    " & Price & "<br>"
    IF Options <> "" Then
    Response.Write "Options:- " & Options & "<br>"
    Response.Write "Image " & Image & "<br>" <<=I KNOW THIS IS WRONG
    End IF
    End IF
    %>

    </font>
    </p>

    </td>
    <td width="41%">&nbsp;</td>
    </tr>
    </table>


    </td></tr></table></body>
    </html>
    Kingdom, Sep 24, 2003
    #4
  5. Kingdom

    Adrienne Guest

    Re: -> Still having a problem displaying the image

    Gazing into my crystal ball I observed Kingdom
    <> writing in
    news:Xns9400B0C33CB97kingdomofwizdom@194.117.133.134:

    > As you can see I'm very new to ASP and just can't learn this stuff
    > quick enough.
    >
    > I have a field with the url to the image folder and would like to
    > display the image, but on the right hand side of the page in another
    > cell.
    >
    > so the output looks like:-
    >
    > details: (the output) IMAGE
    > (the output)
    > (the output)
    >
    > Any help appreciated
    >
    > CODE ASP 3 (PARTIAL ONLY)
    > -----------------------------------
    >
    ><p align="left"><b><font face="Arial" size="2" color="#000080"><%
    > Response.Write Products & " - " & Component_Type %> </font> </b> <br>
    ><font face="Arial" size="2" color="#000080"> <%
    > 'Set up the display of the record
    > Response.Write "Manufacturer - " & Manufacturer & "<br>"
    > Response.Write Description & "<br>"
    > Response.Write "Model No. " & Model_No & "<br>"
    > Response.Write Products & " - " & "Part No. " & Part_Number & " Price
    > " & Price & "<br>"
    > IF Options <> "" Then
    > Response.Write "Options:- " & Options & "<br>"
    > Response.Write "Image " & Image & "<br>" <<=I KNOW THIS IS WRONG
    > End IF
    > End IF
    > %>
    >
    ></font>
    ></p>
    >
    > </td>
    > <td width="41%">&nbsp;</td>
    > </tr>
    > </table>
    >
    >
    > </td></tr></table></body>
    ></html>
    >


    You really should head on over to one of the client side groups like
    alt.html where they will tell you that:
    A. Tables should be used for tabular data, not for presentation and
    positioning
    B. The FONT element is depreciated
    C. Image elements should have alt attributes

    But here you go:

    <% response.write "Image <img src=" chr(034) & image & chr(034) & "
    height=" & chr(034) & "100" & chr(034) & " width=" & chr(034) & "100" & chr
    (034) & " alt=" & chr(034) & "description of image" & chr(034) & "><br>" %>

    or better:

    Image <img src="<%=image%>" width="100" height="100" alt="description of
    image"><br>

    --
    Adrienne Boswell
    Please respond to the group so others can share
    http://www.arbpen.com
    Adrienne, Sep 24, 2003
    #5
  6. Kingdom

    Kingdom Guest

    Re: -> Still having a problem displaying the image

    Adrienne <> wrote in
    news:Xns94009205982DAarbpenyahoocom@207.115.63.158:

    > Gazing into my crystal ball I observed Kingdom
    > <> writing in
    > news:Xns9400B0C33CB97kingdomofwizdom@194.117.133.134:
    >
    >> As you can see I'm very new to ASP and just can't learn this stuff
    >> quick enough.
    >>
    >> I have a field with the url to the image folder and would like to
    >> display the image, but on the right hand side of the page in another
    >> cell.
    >>
    >> so the output looks like:-
    >>
    >> details: (the output) IMAGE
    >> (the output)
    >> (the output)
    >>
    >> Any help appreciated
    >>
    >> CODE ASP 3 (PARTIAL ONLY)
    >> -----------------------------------
    >>
    >><p align="left"><b><font face="Arial" size="2" color="#000080"><%
    >> Response.Write Products & " - " & Component_Type %> </font> </b> <br>
    >><font face="Arial" size="2" color="#000080"> <%
    >> 'Set up the display of the record
    >> Response.Write "Manufacturer - " & Manufacturer & "<br>"
    >> Response.Write Description & "<br>"
    >> Response.Write "Model No. " & Model_No & "<br>"
    >> Response.Write Products & " - " & "Part No. " & Part_Number & "
    >> Price " & Price & "<br>"
    >> IF Options <> "" Then
    >> Response.Write "Options:- " & Options & "<br>"
    >> Response.Write "Image " & Image & "<br>" <<=I KNOW THIS IS
    >> WRONG End IF
    >> End IF
    >> %>
    >>
    >></font>
    >></p>
    >>
    >> </td>
    >> <td width="41%">&nbsp;</td>
    >> </tr>
    >> </table>
    >>
    >>
    >> </td></tr></table></body>
    >></html>
    >>

    >
    > You really should head on over to one of the client side groups like
    > alt.html where they will tell you that:
    > A. Tables should be used for tabular data, not for presentation and
    > positioning
    > B. The FONT element is depreciated
    > C. Image elements should have alt attributes
    >
    > But here you go:
    >
    > <% response.write "Image <img src=" chr(034) & image & chr(034) & "
    > height=" & chr(034) & "100" & chr(034) & " width=" & chr(034) & "100"
    > & chr (034) & " alt=" & chr(034) & "description of image" & chr(034) &
    > "><br>" %>
    >
    > or better:
    >
    > Image <img src="<%=image%>" width="100" height="100" alt="description
    > of image"><br>
    >


    I still cannot get the image to show, any where with either scripts. The
    page loads the image place holder on the first pass and this may be my
    problem.

    The full code is in the first post in this thread if you have any ideas.

    Thanks for your help in this and advice, it's a steep learning curve but
    fun when things eventually work.
    Kingdom, Sep 25, 2003
    #6
  7. Kingdom

    Adrienne Guest

    Re: -> Still having a problem displaying the image

    Gazing into my crystal ball I observed Kingdom
    <> writing in
    news:Xns94011209296E8kingdomofwizdom@194.117.133.134:

    > Adrienne <> wrote in
    > news:Xns94009205982DAarbpenyahoocom@207.115.63.158:
    >
    >> Gazing into my crystal ball I observed Kingdom
    >> <> writing in
    >> news:Xns9400B0C33CB97kingdomofwizdom@194.117.133.134:
    >>
    >>> As you can see I'm very new to ASP and just can't learn this stuff
    >>> quick enough.
    >>>
    >>> I have a field with the url to the image folder and would like to
    >>> display the image, but on the right hand side of the page in another
    >>> cell.
    >>>
    >>> so the output looks like:-
    >>>
    >>> details: (the output) IMAGE
    >>> (the output)
    >>> (the output)
    >>>
    >>> Any help appreciated
    >>>
    >>> CODE ASP 3 (PARTIAL ONLY)
    >>> -----------------------------------
    >>>
    >>><p align="left"><b><font face="Arial" size="2" color="#000080"><%
    >>> Response.Write Products & " - " & Component_Type %> </font> </b> <br>
    >>><font face="Arial" size="2" color="#000080"> <%
    >>> 'Set up the display of the record
    >>> Response.Write "Manufacturer - " & Manufacturer & "<br>"
    >>> Response.Write Description & "<br>"
    >>> Response.Write "Model No. " & Model_No & "<br>"
    >>> Response.Write Products & " - " & "Part No. " & Part_Number & "
    >>> Price " & Price & "<br>"
    >>> IF Options <> "" Then
    >>> Response.Write "Options:- " & Options & "<br>"
    >>> Response.Write "Image " & Image & "<br>" <<=I KNOW THIS IS
    >>> WRONG End IF
    >>> End IF
    >>> %>
    >>>
    >>></font>
    >>></p>
    >>>
    >>> </td>
    >>> <td width="41%">&nbsp;</td> </tr> </table>
    >>>
    >>>
    >>> </td></tr></table></body> </html>
    >>>

    >>
    >> You really should head on over to one of the client side groups like
    >> alt.html where they will tell you that:
    >> A. Tables should be used for tabular data, not for presentation and
    >> positioning B. The FONT element is depreciated
    >> C. Image elements should have alt attributes
    >>
    >> But here you go:
    >>
    >> <% response.write "Image <img src=" chr(034) & image & chr(034) & "
    >> height=" & chr(034) & "100" & chr(034) & " width=" & chr(034) & "100"
    >> & chr (034) & " alt=" & chr(034) & "description of image" & chr(034) &
    >> "><br>" %>
    >>
    >> or better:
    >>
    >> Image <img src="<%=image%>" width="100" height="100" alt="description
    >> of image"><br>
    >>

    >
    > I still cannot get the image to show, any where with either scripts. The
    > page loads the image place holder on the first pass and this may be my
    > problem.
    >
    > The full code is in the first post in this thread if you have any ideas.
    >
    > Thanks for your help in this and advice, it's a steep learning curve but
    > fun when things eventually work.
    >


    Have you looked at the source? Is the path to the image correct?

    --
    Adrienne Boswell
    Please respond to the group so others can share
    http://www.arbpen.com
    Adrienne, Sep 25, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. vishnu
    Replies:
    1
    Views:
    1,182
    Patrick.O.Ige
    Mar 25, 2006
  2. =?Utf-8?B?V2FyYW4=?=

    Limiting Auto suggest dropdown

    =?Utf-8?B?V2FyYW4=?=, May 17, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    2,704
    =?Utf-8?B?V2FyYW4=?=
    May 17, 2006
  3. =?Utf-8?B?V2FyYW4=?=

    Limiting Auto suggest dropdown

    =?Utf-8?B?V2FyYW4=?=, May 17, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    2,072
    =?Utf-8?B?V2FyYW4=?=
    May 17, 2006
  4. Replies:
    8
    Views:
    488
    sloan
    Jan 31, 2008
  5. Mr B
    Replies:
    7
    Views:
    126
Loading...

Share This Page