I am trying to update the contents of a particular dropdown box, based on the selection of another dropdown box. The contents of these boxes is coming from tables in an MSSQL database. Basically, I have a list of “Principals” (aka companies) in one box, and a “Description” box (aka different items) in a second box. My goal is that one you select a specific “Principal” (aka company), that only the selected Principal’s items populate the “Description” box. As it stands now, every item in the “Description” box displays, regardless of the “Principal” (aka company) selected. Just to give you an idea, below is a screenshot of the boxes I am referring to, as well as the relevant code currently being used. As you can obviously see, it’s written mainly in asp-classic/vbs, but Javascript is used as well. I am open to using ANY language/method necessary to accomplish this. Any help is greatly appreciated, if any other details are required I am happy to give them. Thank you very much.
And here is the relevant code for the page currently being used:
<%
Dim Conn, Rs, strPrincipleOptions, DistributorOptions, SalesRepOptions, DescriptionOptions, strSQL
Dim rsclient, OperatorNumber, oRequest
OperatorNumber = Request("Operator_Num") '12600 'test operator number
ProcessMe = Cbool(Request("ProcessMe"))
If (OperatorNumber = "") then
Call TryAgain
ElseIf Cbool(ProcessMe) Then
Set Conn = Application("Conn")
Set Rs = Server.CreateObject("ADODB.RecordSet")
For each oRequest in Request.Form
If (oRequest <> "ProcessMe") AND (oRequest <> "submit") AND (Left(oRequest,4) <> "temp") then
If (Request.Form(oRequest) <> "") Then
strNames = strNames & oRequest & ","
If (Left(oRequest,9) = "Principle") OR (Left(oRequest,11) = "Distributor") then
strValues = strValues & Request.Form(oRequest) & ","
Else
strValues = strValues & "'" & Replace(Request.Form(oRequest),"'","") & "',"
End if
End If
End If
Next
strNames = Left(strNames,Len(strNames)-1) & ",[date]"
strValues = Left(strValues,Len(strValues)-1) & ",'" & date() & "'"
strSQL = "INSERT INTO Product (" & strNames & ") VALUES (" & strValues & ")"
'Response.Write strSQL
'Response.End
Conn.Execute (strSQL)
Set Conn = nothing
Call SubmittedReport
'+INSERT INTO "Product" ("Operator_Num", "Operator", "Address", "City", "State", "Zip", "Contact1", "Phone", "creation_date", "Type_Call", "Acc_Rep", "Sales_Rep", "Mgmt_Co", "Segment", "DSR_Pri", "DSRFullName_Pri", "Concept_Recipe", "Product1", "Product2", "Product3", "Product4", "Product5", "Product6", "Description1", "Description2", "Description3", "Description4", "Description5", "Description6", "Principle1", "Principle2", "Principle3", "Principle4", "Principle5", "Principle6", "Result1", "Result2", "Result3", "Result4", "Result5", "Result6", "Cases1", "Cases2", "Cases3", "Cases4", "Cases5", "Cases6", "Distributor1", "Distributor2", "Distributor3", "Distributor4", "Distributor5", "Distributor6", "Comments1", "Comments2", "Comments3", "Comments4", "Comments5", "Comments6", "FollowUp_Date", "FollowUp_Necessary")
'+VALUES ('%Operator_Num%', '%Operator%', '%Address%', '%City%', '%State%', '%Zip%', '%Contact1%', '%Phone%', '%creation_date%', '%Type_Call%', '%Acc_Rep%', '%Sales_Rep%', '%Mgmt_Co%', '%Segment%', '%DSR_Pri%', '%DSRFullName_Pri%', '%Concept_Recipe%', '%Product1%', '%Product2%', '%Product3%', '%Product4%', '%Product5%', '%Product6%', '%Description1%', '%Description2%', '%Description3%', '%Description4%', '%Description5%', '%Description6%', '%Principle1%', '%Principle2%', '%Principle3%', '%Principle4%', '%Principle5%', '%Principle6%', '%Result1%', '%Result2%', '%Result3%', '%Result4%', '%Result5%', '%Result6%', '%Cases1%', '%Cases2%', '%Cases3%', '%Cases4%', '%Cases5%', '%Cases6%', '%Distributor1%', '%Distributor2%', '%Distributor3%', '%Distributor4%', '%Distributor5%', '%Distributor6%', '%Comments1%', '%Comments2%', '%Comments3%', '%Comments4%', '%Comments5%', '%Comments6%', '%FollowUp_Date%', '%FollowUp_Necessary%');
Else
Set Conn = Application("Conn")
Set rsclient = Conn.execute ("select * from FSA where Operator_Num = "& OperatorNumber)
If rsclient.eof then
Call TryAgain
End If
Call getDropdowns
Call CallReport
End If
Sub getDropdowns
Set Conn = Application("Conn")
Set Rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT DISTINCT(Principle), ID FROM Principal ORDER BY Principal.Principle"
Rs.Open strSQL, Conn
Do while not Rs.EOF
strPrincipleOptions = strPrincipleOptions & "<option value=" & Rs.Fields("ID") & ">" & Rs.Fields("Principle") & "</option>" & vbcrlf
Rs.MoveNext
Loop
Rs.Close
strSQL = "SELECT DISTINCT(Distributor), ID FROM Distributors ORDER BY Distributor"
Rs.Open strSQL, Conn
Do while not Rs.EOF
DistributorOptions = DistributorOptions & "<option value=" & Rs.Fields("ID") & ">" & Rs.Fields("Distributor") & "</option>" & vbcrlf
Rs.MoveNext
Loop
Rs.Close
strSQL = "SELECT DISTINCT(FullName) FROM sales_rep ORDER BY FullName"
Rs.Open strSQL, Conn
Do while not Rs.EOF
SalesRepOptions = SalesRepOptions & "<option value=" & chr(34) & Rs.Fields("FullName") & chr(34) & ">" & Rs.Fields("Fullname") & "</option>" & vbcrlf
Rs.MoveNext
Loop
Rs.Close
strSQL = "SELECT DISTINCT(Description) FROM Item_Desc ORDER BY Description"
Rs.Open strSQL, Conn
Do while not Rs.EOF
DescriptionOptions = DescriptionOptions & "<option value=" & chr(34) & Rs.Fields("Description") & chr(34) & ">" & Rs.Fields("Description") & "</option>" & vbcrlf
Rs.MoveNext
Loop
Rs.Close
End Sub
Sub CallReport
%>
<html>
<head>
<title>FoodService Call Report</title>
<script language=javascript>
function on_load() {
window.scroll(0,0);
document.update.Sales_Rep.focus();
}
function getCreation_Date() {
if (document.update.Sales_Rep.value == "") {
alert ("You must select a Sales Rep before submitting document.");
window.scroll(0,0)
document.update.Sales_Rep.focus();
return;
}
//date1 must be a 0 or 1
if (parseInt(document.update.temp2.value) > 12) {
alert ("You did not enter a valid MONTH.");
document.update.temp2.value = "";
window.scroll(0,0)
document.update.temp2.focus();
return;
}
//date3 must be a 0 or 1 or 2 or 3
var temp_date = document.update.temp2.value + "/" +
document.update.temp4.value + "/" +
document.update.temp5.value +
document.update.temp6.value +
document.update.temp7.value +
document.update.temp8.value
if (temp_date.length < 8) {
alert ("You did not enter a correct Call Date. i.e. 01/01/2003");
window.scroll(0,0)
document.update.temp2.focus();
return;
} else {
var creation_date = temp_date;
document.update.creation_date.value = temp_date;//creation_date
document.update.submit();
}
}
// <input type="button" value="Insert Record" name=jerry onClick="getCreation_Date();">
// <input TYPE="reset" VALUE="Clear" id=reset1 name=reset1></p>
</script>
</head>
<body BACKGROUND="../tabbkgnd.jpg" TEXT="#000000" LINK="#4E7F6E" VLINK="#1B4E3B">
<b><font size="5" color="blue">
<p align="center">FoodService Associates</font><br>
<font size="3" color="black">Call Report Information (Use TAB key to move to next field)</font></b>
<b>
<table border="1" cellpadding="4" Width="95%" align="center">
<!-- HIDE THE FORM SPACE -->
<form action="updateclientA.asp" method="post" name="update">
<input type="hidden" name="ProcessMe" value=1>
<!--<input type="hidden" name="[Date]" value="<%=Date()%>">-->
<input type="hidden" name="Operator_Num" value="<%response.write(rsclient("Operator_num"))%>">
<input type="hidden" name="Acc_Rep" value="<%response.write UCASE(rsclient("Acc_Rep"))%>">
<input type="hidden" maxlength=100 name="Operator" value="<%response.write UCASE(rsclient("Operator"))%>">
<input type="hidden" maxlength=100 name="Address" value="<%response.write UCASE(rsclient("Address"))%>">
<input type="hidden" maxlength=100 name="City" value="<%response.write UCASE(rsclient("City"))%>">
<input type="hidden" maxlength=100 name="State" value="<%response.write UCASE(rsclient("State"))%>">
<input type="hidden" maxlength=100 name="Zip" value="<%response.write UCASE(rsclient("Zip"))%>">
<input type="hidden" maxlength=100 name="Contact1" value="<%response.write UCASE(rsclient("Contact1"))%>">
<input type="hidden" maxlength=100 name="Phone" value="<%response.write UCASE(rsclient("Phone"))%>">
<input type="hidden" name="Mgmt_Co" value="<%response.write UCASE(rsclient("Mgmt_Co"))%>">
<input type="hidden" name="Segment" value="<%response.write UCASE(rsclient("Segment"))%>">
<tr bgcolor="yellow">
<td align="right">
<b>Call Date:
mm<input type="text" value="<%=Month(date())%>" size="2" maxlength="2" name="temp2" onKeyUp="">/
dd<input type="text" value="<%=day(date())%>" size="2" maxlength="2" name="temp4" onKeyUp="">/
yy<input type="text" value="2" size="1" maxlength="1" name="temp5">
<input type="text" value="0" size="1" maxlength="1" name="temp6">
<input type="text" value="2" size="1" maxlength="1" name="temp7">
<input type="text" value="4" size="1" maxlength="1" name="temp8">
<input type="hidden" value="" name="creation_date">
</b></td>
<td align="right"><b>Operator/ Mgmt Co #:</b></td>
<td><%=rsclient("Operator_num")%> </td>
<td align="right"><b>Class:</b></td>
<td><%=rsclient("A_Class")%> </td>
</tr>
</table>
<table border="1" cellpadding="1" Width="95%" align="center">
<tr>
<td align="right"><font size="2"><b>Assigned Rep:</b></font></td>
<td><%response.write UCASE(rsclient("Acc_Rep"))%> </td>
<td align="right"><font size="2"><b>Sales Rep:</b></font></td>
<td><select name="Sales_Rep" size="1">
<option selected value="">Select</option>
<%response.write SalesRepOptions%>
<option value="Other">Other</option>
</select></td>
</tr>
<tr>
<td align="right"><font size="2"><b>Operator:</b></font></td>
<td><%response.write UCASE(rsclient("Operator"))%> </td>
<td align="right"><font size="2"><b>Mgmt Co.:</b></font></td>
<td><%response.write UCASE(rsclient("Mgmt_Co"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>Address:</b></font></td>
<td><%response.write UCASE(rsclient("Address"))%> </td>
<td align="right"><font size="2"><b>City:</b></font></td>
<td><%response.write UCASE(rsclient("City"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>State:</b></font></td>
<td><%response.write UCASE(rsclient("State"))%> </td>
<td align="right"><font size="2"><b>Zip Code:</b></font></td>
<td><%response.write UCASE(rsclient("Zip"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>Phone:</b></font></td>
<td><%response.write UCASE(rsclient("Phone"))%> </td>
<td align="right"><font size="2"><b>Fax:</b></font></td>
<td><%response.write UCASE(rsclient("Fax"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>1st Contact:</b></font></td>
<td><%response.write UCASE(rsclient("Contact1"))%> </td>
<td align="right"><font size="2"><b>2nd Contact:</b></font></td>
<td><%response.write UCASE(rsclient("Contact2"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>1st Contact Title:</b></font></td>
<td><%response.write UCASE(rsclient("Contact_Title1"))%> </td>
<td align="right"><font size="2"><b>2nd Contact Title:</b></font></td>
<td><%response.write UCASE(rsclient("Contact_Title2"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>1st Contact Phone:</b></font></td>
<td><%response.write UCASE(rsclient("Contact_P1"))%> </td>
<td align="right"><font size="2"><b>2nd Contact Phone:</b></font></td>
<td><%response.write UCASE(rsclient("Contact_P2"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>1st Contact Email:</b></font></td>
<td><a href = mailto:><%response.write (rsclient("Contact1_Mail"))%> </td></a>
<td align="right"><font size="2"><b>2nd Contact Email:</b></font></td>
<td><a href = mailto:><%response.write (rsclient("Contact2_mail"))%> </td></a>
</tr>
<td align="right"><font size="2"><b>Segment:</b></font></td>
<td><%response.write UCASE(rsclient("Segment"))%> </td></a>
<td align="right"><font size="2"><b>Affiliation:</b></font></td>
<td><%response.write UCASE(rsclient("Affiliation"))%> </td>
</tr>
<tr>
<td align="right"><font size="2"><b>Pri. DSTR:</b></font></td>
<td><%response.write UCASE(rsclient("DSR_Pri"))%> </td>
<td align="right"><font size="2"><b>DSR Name:</b></font></td>
<td><%response.write UCASE(rsclient("DSRFullName_Pri"))%> </td>
</tr>
</table>
<font color="red"><b><div align="center"><center><p>CALL REPORT<font color="black">
</center></div><table border="1" cellpadding="4" Width="95%" align="center">
<tr bgcolor="yellow">
<td><div align="center"><center><p><b> <font color="#FF0000">Is
this an "<i>issued</i>" SALES LEAD</font><font color="#FF00FF">
</font> <input type="radio"
name="Type_Call" Value="YES"> YES <input
type="radio" name="Type_Call" Value=""> NO <font color="#FF0000">
("issued" defined as a formal Lead recorded with the Sales
Administrator in the office)</font></b></td>
</tr>
<tr align="center">
<td> </td>
</tr>
<tr bgcolor="yellow">
<td><div align="center"><center><p><b> </font></font><font color="#FF0000">Check Only If a NOW OBJECTIVE Operator Call:</font> <font color="red"> <input type="radio"
name="Type_Call2" Value="NOW"> </font>NOW Objective Call <font color="red"> <input
type="radio" name="Type_Call2" Value=""> </font><font color="#FF0000">
<u>Not</u>
</font>a<font color="#FF0000"> </font>NOW Objective</b>
</div>
</center></td>
</tr><tr align="center"><td> <b> </font></b><font color="RED"><span style="font-style: normal; font-weight: 700">MARKED
FIELDS ARE REQUIRED ENTRIES ***</span></td></tr>
</table>
<font color="red">
<div align="center"><center>
<table border="1" width="95%" cellpadding="0" align="center">
<tr bgcolor="yellow">
<td width="23"> </td>
<td width="95"><b><font size="2">Product#<font color="#FF0000"> </font></font>
</b></td>
<td width="211"><b><font size="2">Description<font color="#FF0000">
</font></font><font color="#FF0000">***</font></b></td>
<td width="117"><b><font size="2">Principal<font color="#FF0000">
</font></font><font color="#FF0000">***</font></b></td>
<td width="95"><b><font size="2">Result<font color="#FF0000"> </font></font>
<font color="#FF0000">***</font></b></td>
<td width="70"><font size="2"><b>Cases</b></font></td>
<td width="115"><font size="2"><b>Distributor</b></font></td>
<td width="293"><font size="2"><b>Comments</b></font></td>
</tr>
<tr>
<td width="23"><font size="2"><b>1:</b></font></td>
<td width="95"><input type="text" size="6" maxlength="30" name="Product1"></td>
<td width="211"><select name="Description1" size="1">
<option selected value="0">select</option>
<%response.write DescriptionOptions%>
<option value="">55</option>
</select></td>
<td width="117"><select name="Principle1" size="1">
<option selected value="0">select</option>
<%response.write strPrincipleOptions%>
<option value="">55</option>
</select></td>
<td width="250"><select name="Result1" size="1">
<option selected value="Select">Select</option>
<option value="Sold">Sold</option>
<option value="Testing">Testing</option>
<option value="Interest">Interest</option>
<option value="Using">Using</option>
<option value="Intent To Use">Intent To Use</option>
<option value="No Taste">No Taste</option>
<option value="No Price">No Price</option>
<option value="No Pend Dist">No Pend Dist</option>
<option value="No Nutrition">No Nutrition</option>
<option value="No Scratch">No Scratch</option>
<option value="No Menu Fit">No Menu Fit</option>
<option value="Follow Up Needed">Follow Up Needed</option>
<option value="re-Order (sold)">re-Order (sold)</option>
</select></td>
<td width="70"><input type="text" size="4" maxlength="30" name="Cases1"></td>
<td width="115"><select name="Distributor1" size="1">
<option selected value="0">select</option>
<%response.write DistributorOptions%>
<option value="67">Other</option>
</select></td>
<td width="290">
<input type="text" size="36" maxlength="100" name="Comments1"></td>
</tr>
<tr>