Dynamic dropdown partial page loads ;) 2

[MikeT]

I have two dropdowns on a page, each within their own form. When the user selects a value from the first dropdown, I use the onchange event in the <SELECT> tag to submit this first form to the same page, thereby giving the second dropdown a value to query a db on, and get 'dynamic' options. Until the first form is submitted, the contents of the page stop loading after the second dropdown. Why? I've seen examples of this same technique load all the way even before the first dropdown was specified. That example was actually javascript, which I'll migrate to if i have to...
But anyway, is this possible w/ VBScript? Why is this happening?

Thanks!

 

[link9]

Certainly it's possible with vbscript -- Kind of hard to know why your page would be doing that without seeing some code, though. It has to be a coding issue.

 

[MikeT]

<%
varState=request.form(&quot;drpState&quot
varCity=&quot;Select City&quot;
if varState=&quot;&quot; then
varState=&quot;Select State&quot;
end if
%>

<form name=&quot;form1&quot; method=&quot;post&quot; action=&quot;samepage.asp&quot;>
<select name=&quot;drpState&quot; onchange=&quot;submit()&quot; size=&quot;1&quot;>
<%
sql = &quot;SELECT State FROM myTable&quot;
set rs = server.createobject(&quot;adodb.recordset&quot
rs.open sql, conn
%>

<option selected value=&quot;<%=varState%>&quot;><%=varState%></option>

<%Do while not rs.eof%>
<option value=&quot;<%=rs(&quot;State&quot%>&quot;><%=rs(&quot;State&quot%></option>
<%rs.movenext%>
<%loop%>
<%rs.close%>

</select>
</form>


<form name=&quot;form2&quot; method=&quot;post&quot; action=&quot;nextpage.asp&quot;>
<input type=&quot;hidden&quot; name=&quot;varState&quot; value=&quot;<%=varState%>&quot;>
<select name=&quot;drpCity&quot; size=&quot;1&quot;>
<option selected value=&quot;<%=varName%>&quot;><%=varName%></option>

<%if not varState = &quot;Select State&quot; then
set rs = Server.CreateObject(&quot;adodb.Recordset&quot
sql2 = &quot;SELECT City FROM myTable WHERE State = &quot;&varState
rs.open sql2, conn
varCity = rs(&quot;City&quot
rs.close
end if%>

<%Do while not rs.eof%>
<option value=&quot;<%=varCity%>&quot;><%=varCity%></option>
<%rs.movenext%>
<%loop%>
<%rs.close%>
</select>

<input type='submit' value='Next>>'>


If you have the patience to wallow through all that I commend you. I changed the concept here to reflect a simple State/City scenario, but the code is the same, albeit abridged. Given this code, the Submit button will not appear, nor anything else after it, until a value for the first(State) dropdown has been specified. As you can see, it pulls the options for both dropdowns from the db.
One thing I dont like about this setup is having to make another trip back to the server to get the 2nd dropdown options. It is not feasible to send all the possible options on the initial load, so I think I am just SOL. I'm starting to think the problem might be with the IF statement in the 2nd form.

I'm interested to hear your thoughts!

 

[link9]

Ok, sorry it took me so long to get back on this one...

I see a couple things --

First, I don't see where you are declaring or assigning &quot;varName&quot; for the first option in your drpCity dropdown box - Don't think that would cause the problem, but who knows -- maybe you have declared it earlier --

Second, you are assigning the values of the second drop down the variable, 'VarCity', which you only declare one time (up there in your if statement). See what I mean? You assign it the first value from your recordset, and then you assign that to the first option -- movenext on your recordset, assign the second one the same value, and so on... Should that assignment (to the option value & text) not be rs(&quot;city&quot ?? --

I think that may very well be confusing it...

Let me know if this helps, and if not, we'll dig a little deeper --

Paul Prewett

 

[MikeT]

Both instances of varName in the second dropdown should be varCity. I just forgot to change it over to the State/City scenario. And the loop that fills the second dropdown should indeed be rs(&quot;City&quot. The actual code is that way, I must have jumbled it up during the abridging process.
Oops.

Rest assured, those two discrepancies resolved, the code above is the same as in my asp.

Thanks for your time.

 

[link9]

One other thing (that you probably have right in the real code) -- you are closing your recordset up there before you go into your loop --

Then, howabout this -- check to make sure that the recordset was created before you do your loop, and then, if not, skip the do until rs.eof part -- maybe it's hanging because it's looking for something you haven't yet created:

<%if not varState = &quot;Select State&quot; then
set rs = Server.CreateObject(&quot;adodb.Recordset&quot
sql2 = &quot;SELECT City FROM myTable WHERE State = &quot;&varState
rs.open sql2, conn
varCity = rs(&quot;City&quot
created = true
end if%>

<%if created then
Do while not rs.eof%>
<option value=&quot;<%=varCity%>&quot;><%=varCity%></option>
<%rs.movenext%>
<%loop%>
<%rs.close
end if
%>
</select>

<input type='submit' value='Next>>'>

Notice the new variable, 'created', and how you evaluate whether or not it's true before you try to loop through an object (that may or may not have been created yet, due to that if statement above) --

See if that works, and let me know --
Paul Prewett

 

[MikeT]

Yes, I opened another recordset before the loop.

I hope you are paid well;
Worked like a charm!
This will help make the site look more professional, I cannot thank you enough for your generosity and patience.
You are the reason sites like tek-tips exist.
God Bless!

Mike

 

[JrClown]

I agree MikeT, Lik9 is one if not the best.
QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[JrClown]

Link9 QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>