display from populated dropdown list from access database

Sushi_Master

Nov 19th 2005, 7:11 am

Hi all.

I have got a page which has two dropdown boxes. They are both populated from an access database. The second box displays values dependent on the selection of the first box. This works fine!

My question is... can i display information on the page dependent on what is selected in the second dropdown box.

I have a column called "ID" in my database which is set as "autonumber". I wish to display the contents relating to the "ID" number.

Here is the structure of my database "FAQS.mdb";

"tblQuestions" (my table of questions which holds 3 coulmns)
"ID" (which is set as autonumber)
"Question" (which populates the first drowdown box "cboQuestion")
"SubQuestion" (which populated the second dropdown "cboSubQuestion" box dependent on selection of Question)

"tblAnswers" (my table of answers which holds 2 coulmns)
"ID" (which is set as autonumber)
"Answer" (which hold the answers)

So when somebody selects a subquestion which has an ID number of say 3, i want the answer to be displayed on the page which has the ID number 3.

Here is my code;

<%@ language = "Javascript" %>
<%

var strQuestion = "";
if (Request.Querystring("stateChanged") == "true")
{
strQuestion = Request.Form("cboQuestion");
}
var strconnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/WINDOWS/Desktop/database asp/multi_dropdowns/FAQS.mdb";
%>
<script language=Javascript>
function initControls()
{
populateQuestion();
populateType();
}
function populateQuestion()
{
<%
var rs = Server.CreateObject("ADODB.Recordset");
var sql = "select distinct Question from tblQuestions order by Question"
rs.Open(sql,strconnection);
var n=0;
while (!rs.EOF)
{
var stateID = rs.Fields("Question");
var stateDesc = rs.Fields("Question");
Response.Write("document.frmTest.cboQuestion[" + n + "] = new Option('" + stateDesc+ "','" + stateID + "');");

if (new String(strQuestion).search(stateID) != -1)

Response.Write("document.frmTest.cboQuestion[" + n + "].selected = true;");
rs.MoveNext();
n++;
}
rs.Close();
%>
}

function populateType()
{
<%
if (strQuestion != "")
{
var rs2 = Server.CreateObject("ADODB.Recordset");
var strSql = "select distinct SubQuestion from tblQuestions where Question = '" + strQuestion + "' ";
rs2.Open(strSql,strconnection);
var n=1;
while (!rs2.EOF)
{
var strType = rs2.Fields("SubQuestion");
Response.Write("document.frmTest.cboSubQuestion[" + n + "] = new Option('" + strType + "','" + strType + "');");
rs2.MoveNext();
n++;
}
rs2.Close();
}
%>
}
function fillType()
{
document.frmTest.action = "contact-sam3.asp?stateChanged=true";
document.frmTest.submit();
}
function saveData()
{
document.frmTest.action = "./contact-sam3.asp";
return true;
}

</script>

<html>
<head>
<title>Multi Dropdown Selecting Value from one second will Display its corresponding values</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<script language="javascript">

</script>

</head>
<body onload = "initControls();" bgcolor="#ffffff" link="#666666" vlink="#666666" alink="#666666" leftmargin="0" topmargin="0">
<table width="780" border="0" align="center" cellpadding="0" cellspacing="0">

<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="780">
<tr>
<td> </td>
</tr>
</table>
</td>
</tr>
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" width="780">
<tr>
<td valign="top">
<table width="220" border="0" cellpadding="0" cellspacing="0" bgcolor="#D6E6F3">
<tr>
<td valign="top" bgcolor="96C2DD"> <strong style="font-weight: 400">
<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">
Question & SubQuestion</font></strong></td>
</tr>
<tr>
<td valign="top">
<form name="frmTest" method="post" action="contact-sam3.asp">
<table width="220" border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111">
<tr valign="top" bgcolor="#D6E6F3">
<td height="25" colspan="2" width="220">
<select name="cboQuestion" onChange="fillType()">
<option value="Select Question" selected>Select Question</option>
</select>
</td>
</tr>
<tr bgcolor="#D6E6F3">
<td width="153" valign="top" bgcolor="#D6E6F3">
<SELECT name=cboSubQuestion LANGUAGE=javascript onchange="return dept_onchange(frmTest)">
<option value="Select Question" selected>Select Question</option>
</select>
</td>
<td width="67" bgcolor="#D6E6F3">
<INPUT TYPE = "Submit" VALUE = "GO>>>>">
</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</td>
<td align="right">  </td>
</tr>
</table></td>
</tr>
</table>
The following was selected : <%=Request.Form ("cboSubQuestion")%>
</body>
</html>

Many many thanks for any help in advance.

I can provide my database to anyone if it can help you help me.

Best wishes.

vectorgraphx

Nov 21st 2005, 6:24 am

you might try something like this:

http://www.dynamicdrive.com/dynamicindex1/combodescribe.htm

I've used that before - and it works very well for showing alternate content based on what is chosen from a drop-down menu, plus, you can build the javascripts dynamically from your database.

HTH

Sushi_Master

Nov 22nd 2005, 2:32 am

Thanks Vector, that's a nice little script there but not exactly what im after. Infact the Code I originally posted already does that :)

What i need to do is call a response.write based on the ID number when a selection is made from the second dependent dropdown box.

I have done this in the past but only when using a single dropdown box. I can't get it to work now i'm using 2 dependant dropdown boxes :confused: