Hello!
I’ve been battling with the below for a few days now and cannot reach a simple solution! I’m hoping somebody will be able to point me in the right direction!
I have two tables in my database: A COLOURS table and a PRODUCT table.
The COLOURS table goes along the lines of:
ColourID ColourName
1 Red
2 Green
3 Blue
4 Yellow
In my PRODUCTS table I have a product with the colours listed as comma seperated values, such as:
ProductID ProductName ProductColours
1 Nice Trousers 2,3,4
Ultimately I want to bring back a drop down menu in my form that would show:
2 Green
3 Blue
4 Yellow
for this example.
I’m not sure if I should be retreiving the records and building the above in the SQL (e.g SELECT ColourName FROM ColourTable WHERE ColourID LIKE … etc) or if I can build it in ASP.
I have succeeded in bringing back the drop-down menu if I used the ColourName only in the array (e.g Red,Green,Yellow), but now I’m stuck with having to use ID numbers instead, so I need to present the names based on the IDs!
I’ve searched through Google and this forum and can’t find anything usable - will somebody be able to point me in the right direction?
Thank you for your time.
I would probably execute one SQL to collect the Product information and then execute the second SQL to collect the colour names using an ‘IN’ clause on the current product’s colour names. With the colour names selected, just build your select and option tags.
Many thanks, Karl - this seems to be going in the right direction. However, I think I’ve now hit upon another issue… My ProductColours array (2,3,4) is of course a string, whereas my ColourID is an integer. While the code seems happy, no results are returned. I know this is overblown code (Dreamweaver!) but here it is in it’s entirity:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/cms.asp" -->
<%
Dim RS_Products
Dim RS_Products_cmd
Dim RS_Products_numRows
Set RS_Products_cmd = Server.CreateObject ("ADODB.Command")
RS_Products_cmd.ActiveConnection = MM_cms_STRING
RS_Products_cmd.CommandText = "SELECT product_id, product_name, product_colours FROM ufw.tbl_products WHERE product_id = 5"
RS_Products_cmd.Prepared = true
Set RS_Products = RS_Products_cmd.Execute
RS_Products_numRows = 0
%>
<%
Dim RS_GetColours__varcid
RS_GetColours__varcid = "2,3"
If ((RS_Products.Fields.Item("product_colours").Value) <> "") Then
RS_GetColours__varcid = (RS_Products.Fields.Item("product_colours").Value)
End If
%>
<%
Dim RS_GetColours
Dim RS_GetColours_cmd
Dim RS_GetColours_numRows
Set RS_GetColours_cmd = Server.CreateObject ("ADODB.Command")
RS_GetColours_cmd.ActiveConnection = MM_cms_STRING
RS_GetColours_cmd.CommandText = "SELECT colour_id, colour_name FROM ufw.tbl_colours WHERE colour_id IN (?)"
RS_GetColours_cmd.Prepared = true
RS_GetColours_cmd.Parameters.Append RS_GetColours_cmd.CreateParameter("param1", 5, 1, -1, RS_GetColours__varcid) ' adDouble
Set RS_GetColours = RS_GetColours_cmd.Execute
RS_GetColours_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
RS_GetColours_numRows = RS_GetColours_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<%
While ((Repeat1__numRows <> 0) AND (NOT RS_GetColours.EOF))
%>
<%=(RS_GetColours.Fields.Item("colour_name").Value)%><br /><br />
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RS_GetColours.MoveNext()
Wend
%>
</body>
</html>
<%
RS_Products.Close()
Set RS_Products = Nothing
%>
<%
RS_GetColours.Close()
Set RS_GetColours = Nothing
%>
I would even go so far as to suggest normalising the database to create a ProductsColours table …
ProductID | ColourID
1 | 2
1 | 3
1 | 4
… and removing the ProductColours field from PRODUCTS. Then your (single) SQL command becomes a lot simpler …
sSQL = "SELECT c.ColourID,c.ColourName FROM Colors c " &_
"INNER JOIN ProductsColours p ON c.ColourID=p.ColourID " &_
"WHERE p.ProductID=" & varProductID & " " &_
"ORDER BY c.ColourID"
It also means adding/removing colours for products is a much easier task to manage. 
I like it!
Many thanks for that, siteguru! I’ll make some progress with that idea…
If you have the ability to change the DB schema, then I would go with siteguru’s suggestion, much easier to code and manage a table with the products colors, providing a one to many relationship with your product ID. If not, I would check your ‘while conditional’ loop.
Thank you for everyone’s help - I’ve got everything running just great!