• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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

0
LLC0706
Asked:
LLC0706
  • 2
  • 2
2 Solutions
 
neeraj523Commented:
Hello

I coded complete solution for you by editing the code posted.. let me know if still facing issue..
<%@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");
var sql = "SELECT *  FROM Products WHERE Active = True  ORDER BY ProdID" ;
rs.Open( sql, Conn);

var count = 0;
while (!rs.EOF) 
{
	if(count % 2 == 0)
	{
		Response.Write ("</tr><tr>");
	}
%>
    <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">
<%
	sql = "SELECT * FROM Colours WHERE ProdID=" + rs("ProdID");
	var rs1 =  Server.CreateObject("ADODB.Recordset");
	rs1.Open( sql, Conn);
	while (!rs1.EOF) 
	{ 
 %>
                  <option value="<%=rs1("ColourID")%>"><%=rs1("ColourDesc")%></option>
                  <%
		rs1.MoveNext();
	}
	rs1.Close();

%>
                </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>
    <%
		count++;
       rs.MoveNext();
}
%>
  </tr>
</table>
</body>
</html>

Open in new window

0
 
LLC0706Author Commented:
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?
0
 
neeraj523Commented:
Here you go

now form will be submitted only if qty is greater than 0
<%@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>
<script>
function validate(obj)
{
	if(isNaN(obj.qty.value) ||  obj.qty.value <= 0)
	{
		alert("Invalid quantity.");
		obj.qty.focus();
		return false;
	}
}
</script>
</head>

<body>
<table width="484" border="0" cellspacing="0" cellpadding="0">
  <tr>
<%
var rs =  Server.CreateObject("ADODB.Recordset");
var sql = "SELECT *  FROM Products WHERE Active = True  ORDER BY ProdID" ;
rs.Open( sql, Conn);

var count = 0;
while (!rs.EOF) 
{
	if(count % 2 == 0)
	{
		Response.Write ("</tr><tr>");
	}
%>
    <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<%=rs("ProdID")%>" name="qtyCol<%=rs("ProdID")%>" method="post" action="basket.asp" onSubmit="return validate(this)">
          <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">
<%
	sql = "SELECT * FROM Colours WHERE ProdID=" + rs("ProdID");
	var rs1 =  Server.CreateObject("ADODB.Recordset");
	rs1.Open( sql, Conn);
	while (!rs1.EOF) 
	{ 
 %>
                  <option value="<%=rs1("ColourID")%>"><%=rs1("ColourDesc")%></option>
                  <%
		rs1.MoveNext();
	}
	rs1.Close();

%>
                </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>
    <%
		count++;
       rs.MoveNext();
}
%>
  </tr>
</table>
</body>
</html>

Open in new window

0
 
LLC0706Author Commented:
This works perfectly, just what I needed. hank you so much for your help.

This page leads to the basket and checkout pages, which I may need help with. I will post these as new questions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now