Limiting duplicates in a dynamic dropdown? - Code included

[Kingdom]

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]

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

That ought to do it.


--
William Morris
Product Development, Seritas LLC

"Kingdom" <kingdomof@removehotmail.com> wrote in message
news:Xns9400A036D2F53kingdomofwizdom@194.117.133.1 34...

> 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]

"WIlliam Morris" <seamlyneNO_SP_AM@hotmail.com> wrote in
news:bkscov$54qri$1@ID-205671.news.uni-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]

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>

[Adrienne]

Gazing into my crystal ball I observed Kingdom
<kingdomof@removehotmail.com> writing in
news:Xns9400B0C33CB97kingdomofwizdom@194.117.133.1 34:

> 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
[url]http://www.arbpen.com[/url]

[Kingdom]

Adrienne <arbpen@sbcglobal.net> wrote in
news:Xns94009205982DAarbpenyahoocom@207.115.63.158 :

> Gazing into my crystal ball I observed Kingdom
> <kingdomof@removehotmail.com> writing in
> news:Xns9400B0C33CB97kingdomofwizdom@194.117.133.1 34:
>

>> 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.

[Adrienne]

Gazing into my crystal ball I observed Kingdom
<kingdomof@removehotmail.com> writing in
news:Xns94011209296E8kingdomofwizdom@194.117.133.1 34:

> Adrienne <arbpen@sbcglobal.net> wrote in
> news:Xns94009205982DAarbpenyahoocom@207.115.63.158 :
>

>> Gazing into my crystal ball I observed Kingdom
>> <kingdomof@removehotmail.com> writing in
>> news:Xns9400B0C33CB97kingdomofwizdom@194.117.133.1 34:
>>

>>> 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
[url]http://www.arbpen.com[/url]