dependant dropdown using array; no second trip to db

[MikeT]

I have two dropdowns on my page; the contents of the second being dependant on the chosen option of the first. The options for both dropdowns come from an SQL query. I did this by putting the first dropdown in its own form, and submitting it to the same page when the chosen option of the first dropdown has changed. This works great, but requires a second trip to the server. If I have an array of all possible options on the page, would it be possible to not have to make that second trip? I'm thinking of having a subroutine run everytime the onchange event of the first dropdown occurs. This would have to be client side script, so would java be the better choice?

 

[Mighty]

MikeT,

I have a page that does exactly what you are looking for. The best way to do it is to use your VBSCRIPT and query results to create a client side JavaScript function. This function can just be a series of if loops which checks the value of the first dropdown and changes the value of the second dropdown accordingly. In my case, I have a dropdown and a text box. The function just gets fired using the onChange() event for the dropdown. Here is a section of my code. It should give you an idea of what I am doing:

Response.Write &quot;<SCRIPT LANGUAGE=&quot;&quot;JavaScript&quot;&quot;>&quot; & vbcrlf
Response.Write &quot;<!--&quot; & vbcrlf
Response.Write &quot;function showDescription(PartID,Description) {&quot; & vbcrlf
Response.Write &quot; var select = eval(PartID.selectedIndex);&quot; & vbcrlf
Response.Write &quot; var catId = PartID.options[select].value;&quot; & vbcrlf
Response.Write &quot; var d = Description;&quot; & vbcrlf
rs.MoveFirst
Response.Write &quot; if (catId == '&quot; & trim(rs.Fields(&quot;PRTNUM_01&quot) & &quot;'){&quot; & vbcrlf
Response.Write &quot; d.value='&quot; & trim(rs.Fields(&quot;PMDES1_01&quot) & &quot;';&quot; & vbcrlf
Response.Write &quot; }&quot; & vbcrlf
rs.MoveNext
Do While not rs.EOF
Response.Write &quot; else if (catId == '&quot; & trim(rs.Fields(&quot;PRTNUM_01&quot) & &quot;'){&quot; & vbcrlf
Response.Write &quot; d.value='&quot; & trim(rs.Fields(&quot;PMDES1_01&quot) & &quot;';&quot; & vbcrlf
Response.Write &quot; }&quot; & vbcrlf
rs.MoveNext
loop
Response.Write &quot;} // end function&quot; & vbcrlf
Response.Write &quot;//-->&quot; & vbcrlf
Response.Write &quot;</SCRIPT>&quot; & vbcrlf

The call from the dropdown in the resultant HTML file is as follows:

<TD ALIGN=&quot;CENTER&quot;><SELECT NAME = &quot;PartID1&quot; onchange=&quot;showDescription(document.ProdForm.PartID1, document.ProdForm.Description1)&quot;>
<OPTION SELECTED VALUE = &quot;&quot;></OPTION>
<OPTION VALUE = &quot;16902005&quot;>16902005</OPTION>
<OPTION VALUE = &quot;16902007&quot;>16902007</OPTION>
<OPTION VALUE = &quot;16902008&quot;>16902008</OPTION>
<OPTION VALUE = &quot;16902009&quot;>16902009</OPTION>
<OPTION VALUE = &quot;16902010&quot;>16902010</OPTION>
<OPTION VALUE = &quot;16902011&quot;>16902011</OPTION>
<OPTION VALUE = &quot;16902065&quot;>16902065</OPTION>
<OPTION VALUE = &quot;GTB/040/40&quot;>GTB/040/40</OPTION>
</SELECT></TD>

I hope you can understand all that. At the onChange event, I call the showDescription function with the dropdown and the text box as arguments. The function checks the value of the dropdown and assigns a value to the text box accordingly. Mise Le Meas,

Mighty

 

[kingjjx]

HI , I KNOW THIS POST IS QUITE OLD, BUT I AM TRYING TO DO A QUERY PAGE WHERE IN THE 2ND DROPDOWN BOX ISX DEPENDENT ON THE FIRST DROPDOWN BOX AND BOTH ARE GENERATED FROM A DATABASE, CAN YOU PLEASE TELL ME HOW TO DO THIS ?

THANK YOU

 

[MikeT]

YES I CAN BUT YOU WILL HAVE TO STOP YELLING FIRST

Here is a basic overview:
(I'm assuming your using ASP w/ VBScript)

The page will have TWO forms on it, the only element in the first form is the first dropdown. The first element in the second is the other dropdown.
On the first dropdown of the first form, add an OnChange event that submit this first form to its own page. The second dropdown will have code that looks for a value of the first dropdown in ASP's Form Collection. If it finds one, it queries the database based on that value, then puts the results into the dropdown's options. Think about it for a while.
This method requires the user to essentially load the page twice. There is another method where you don't have to do that, but it is very complex, and knowledge of Javascript.
Basically, you read in every posssible option for the dropdown along with qualification criteria. Then use VBScript to create a Javascript array. The elements contain the option itself, and the qualification criteria, separated by a comma. Instead of using the Onchange event of the first dropdown, instead have it point to a javascript function that reads in the array, filters it, and dynamically adds the qualifying options to the second dropdown.

How are those for 'Basic' explainations?
The first method isn't hard to implement at all, its just the reload thing that sucks.
Regardless, if I haven't scared you off, let me know how you're currently connecting to and reading from your database, and I can give you more details.

 

[efrost2]

Remote Scripting is the answer!
when you select an item in the first drop down you can send this to an asp page which querys the db and get back values WITH OUT reloading the page - very cool.

 

[MikeT]

Enlighten us efrost2...
How is this done?