asked on

Adding a select box to a record set

I have a MS Access database attached to a webpage, and I'm trying to display a product which is available in a choice of colours. The choice of colours are displayed in a drop down select box. There is also a quantity drop down select box.

The products are listed in the database table named Products and the colours are listed in the database table named Colours.

The Products table has 4 fields:
ProdID = primary key
Price
Description
Image

The Colours table has 3 fields:
ColourID = primary key
ColourDesc
ProdID

The ProdID of both tables equal each other.

In the colours table there are multiple instances of each ProdID for example ProdID 1 is available in two colours and ProdID 2 is available in 4 colours:

ColourID ProdID ColourDesc
20 1 Red
21 1 Blue
22 2 Red
23 2 Blue
24 2 Green
25 2 Yellow

How do I get the correct amount of colours to show for each product in the drop down select box. Also how do I validate the quantity drop down select box so that a quantity of zero or an empty field is not submitted.

Can anyone help? I'm coding with asp and javascript.

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%>
<!--#include file="connection.asp" -->

<!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>
<table width="484" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <%

var rs =  Server.CreateObject("ADODB.Recordset");
sql = "SELECT Prod.ProdID, Prod.Price, Prod.Description, Prod.Image  FROM Products WHERE Active = True  ORDER BY Prod.ProdID" ;
rs.Open( sql, Conn, 3 );
var rownum;
var colnum;
for ( rownum = 1; rownum <= 5; ++rownum )
{
    Response.Write("<tr>\n");
    for ( colnum = 1; colnum <= 2; ++colnum )
    {
%>
    <td width="450" valign="top"><table width="238" height="370" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td height="188" align="center" valign="middle"><a href="products_details.asp?ProdID=<%= rs("ProdID") %>"><img src="<%= prodImages(rs("ProdID"), rs("Image"),"") %>" width="176" height="156" border="0" alt="<%=rs("Description")%>" /></a></td>
      </tr>
      <tr>
        <td valign="top"><%= rs("Description")%></td>
      </tr>
      <tr>
        <td valign="top">Price(rs("Price"))%></td>
      </tr>
      <tr>
        <td valign="top"><form id="qtyCol" name="qtyCol" method="post" action="basket.asp">
          <table width="210" border="0" cellspacing="0" cellpadding="0">
            <tr>
              <td width="50">Qty:</td>
              <td width="160"><label>
                <input name="qty" type="text"id="qty" value="1" size="4" maxlength="4" />
              </label></td>
            </tr>
            <tr>
              <td>Colour:</td>
              <td><label>
                <select name="colour" id="colour">
                  <%
while (!rs.EOF) { 
 %>
                  <option value="<%=rs("ColourID")%>"><%=rs("ColourDesc")%></option>
                  <%
  rs.MoveNext();
}
if (rs.CursorType > 0) {
  if (!rs.BOF) rs.MoveFirst();
} else {
  rs.Requery();
}
%>
                </select>
              </label></td>
            </tr>
            <tr>
              <td height="38" colspan="2" align="center" valign="middle"><table width="100" border="0" cellspacing="0" cellpadding="0">
                <tr>
                  <td height="38"><label>
                    <input name="btn_buy_now" type="submit" class="btn-buy-now" id="btn_buy_now" value="Buy Now" />
                  </label></td>
                </tr>
              </table></td>
            </tr>
          </table>
        </form></td>
      </tr>
      <tr>
        <td height="44" align="center" valign="top">&nbsp;</td>
      </tr>
    </table>
      </td>
    <%
       rs.MoveNext();
       if ( rs.EOF ) break;
    } 
    Response.Write("</tr>\n");
    if ( rs.EOF ) break;
} 
%>
    <td width="20" valign="top">&nbsp;</td>
  </tr>
</table>
</body>
</html>

Open in new window

ASKER CERTIFIED SOLUTION

neeraj523

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LLC0706

ASKER

Hi neeraj523

Thanks for your reply. This works perfectly.

I would like to display an alert box if the user has left the quantity field empty or has entered a value of 0, when the buy now button has been clicked and directed the user to the basket page.

Are you able to help me with this?

SOLUTION