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

Problems with ASP dropdowns fed by SQL ADO

I have a page that works almost perfectly except for one problem.  When you select an item from the first drop down it populates the second.  When selecting an item from the second drop down it should populate the third.  This doesnt happen, it will only happen if you re-choose an item from the first drop down, at that point it runs the query on the third.  I know my sloppy syntax is the problem, I am still new to programming these types of pages.  Could someone please help with the Activity drop down?  here is the code -

<script type="text/javascript">
 function fetchItems(sel){
sel.form.action="<%=Request.ServerVariables("SCRIPT_NAME")%>";
sel.form.onsubmit=function(){return true};
//this is so you know if the for was the result of an "onchange"
//Or if the user clicked submit
document.getElementById("formCompleted").value=0;
 sel.form.submit();
}
</script>



<form action="websiteupdate_send.asp" METHOD="POST" name="frmData" onsubmit="return verifyData(this)">
<table border="0">
      <%      Dim rs
            Dim rt
            Dim ra
            Dim sNhood
            Dim sLots
            Dim sActivity
            Dim sRegion
            Dim sSQL
            Dim MyConn
            Dim sHTML
            Dim sHTNH
            Dim sHTL
            Dim sHTA
            Dim NTLogin, sSender, sSentDate, sText
            NTLogin = Request.ServerVariables("AUTH_USER")
            if Left(NTLogin,7) = "JWHOMES" then
                  sSender = Right(NTLogin, len(NTLogin)-8) & "@jwhomes.com"
            else
                  sSender = NTLogin & "@jwhomes.com"
            end if
            Set oJWI = Server.CreateObject("JWIntranet.clsRWDB")
            //sSQL = "select sPlan_Name from BuilderSuite..Plans where sPlan_Name not like 'XX%' order by sPlan_Name"
            
            sNhood = "select distinct neighborhoodname from homelinkii..neighborhoods where active = 1"
                    
            
            
            Set rt = oJWI.RunSQLReturnRS(sNhood)
            Do while not rt.eof
            sHTNH = sHTNH & "<option value='" & Server.HTMLEncode(rt("neighborhoodname").value) & "'"
            If Trim(CStr(Request.Form("tNhood"))) = Trim(CStr(rt("neighborhoodname").value)) Then
            sHTNH = sHTNH & " selected='selected' "
             End If
            sHTNH = sHTNH & ">" & Server.HTMLEncode(rt("neighborhoodname").value) & "</option>"
             rt.movenext
            Loop

            

            
            
            %>

      
      <%
            set rs = nothing
            
            %>
      <tr><td height=25></td></tr>
            <tr>
            <td align="right">Neighborhood:</td>
      <td><input type="hidden" id="formCompleted" name="formCompleted" value="1" /><select name="tNhood" ID="Select1" onchange="fetchItems(this);"><option value="" selected>Select Neighborhood</option><%=sHTNH%></select></td>

</tr>


            <% sHTL=""
            If "" <> CStr(Request.Form("tNhood")) AND "1"<>CStr(Request.Form("formCompleted")) Then
             sLots = "select 'Amenity' as lotnumber union select 'Multi' union select isnull(fastlotnumber,'') + '/' + isnull(fastlotblock,'') from homelinkii..lots l join homelinkii..neighborhoods n on l.neighborhoodid = n.neighborhoodid where neighborhoodname = '" & Request.Form("tNhood") & "'"
            Set rt = oJWI.RunSQLReturnRS(sLots)
            If rt.EOF Then
            Response.Write( "No results found for " & Server.HTMLEncode(sLots) )
            Else
            Do while not rt.eof
            sHTL = sHTL & "<option value='" & Server.HTMLEncode( rt("lotnumber").value) & "'"
            sHTL = sHTL & ">" & Server.HTMLEncode( rt("lotnumber").value ) & "</option>"
            rt.movenext
             Loop
             End If
             End If
             %>




<tr>
      <td align="right">Lot:</td>
      <td><select name="tlot" id="formCompleted" value="1"  ><option value="" selected>Select Lot</option><%=sHTL%></select></td>
</tr>

            <% sHTA=""
            If "" <> CStr(Request.Form("tlot")) Then
            sActivity = "select distinct p.activity_no_ + ' - ' + act_descr as activity_no_ from hbs_pohd p join homelinkii..lots l on l.fastlotnumber = p.unit_id_ and l.fastlotblock = p.building_id_ and l.fastprojectid = p.project_id_ join homelinkii..neighborhoods n on n.neighborhoodid = l.neighborhoodid join hbs_actv a on a.activity_no_ = p.activity_no_ where neighborhoodname  =  '" & Request.Form("tNhood") & "' and fastlotnumber + '/' + fastlotblock = '" & Request.Form("tLot") & "'"
            Set ra = oJWI.RunSQLReturnRS(sActivity)
            If ra.EOF Then
            Response.Write( "No results found for " & Server.HTMLEncode(sActivity) )
            Else
            Do while not ra.eof
            sHTA = sHTA & "<option value='" & Server.HTMLEncode( ra("activity_no_").value) & "'"
            sHTA = sHTA & ">" & Server.HTMLEncode( ra("activity_no_").value ) & "</option>"
            ra.movenext
             Loop
             End If
             End If
             %>


<tr>
            <td align="right">Activity:</td>
            <td><select name="tActivity" ID="Select2"><option value="" selected>Select Activity</option><%=sHTA%></select></td>
      </tr>

0
JWHomes
Asked:
JWHomes
  • 7
  • 6
  • 3
1 Solution
 
Wayne BarronCommented:
JWHomes
For future reference, when adding code to your post, use the CODE button
To wrap you code, this will make it easy to copy out, and make the page less cluttered.

#1:
You had another post about connecting 1 drop to the 2nd drop.
And that thread is completed and awarded to hielo.

#2:
You are now wanting to connect the 2nd drop to a 3rd drop.

What else are you going to need after this is completed?
Reason I am asking, is that it will make it a LOT easier to help in your project if we know exactly what it is that you are needing to accomplish.

Carrzkiss
0
 
JWHomesAuthor Commented:
This really should be all that is needed.  I wanted a form that would allow a user to have a set of cascading drop downs to submit a form.  This third and final drop down would complete the form and the project.  

The 3rd drop is taking items from the first two, at least it should be.  Once that works correctly the user can submit the form and i have completed code on a second page to push into SQL table and generate an email.  Those are working fine.
<script type="text/javascript">
 function fetchItems(sel){ 
sel.form.action="<%=Request.ServerVariables("SCRIPT_NAME")%>";
sel.form.onsubmit=function(){return true}; 
//this is so you know if the for was the result of an "onchange" 
//Or if the user clicked submit 
document.getElementById("formCompleted").value=0;
 sel.form.submit(); 
} 
</script>



<form action="websiteupdate_send.asp" METHOD="POST" name="frmData" onsubmit="return verifyData(this)">
<table border="0">
	<%	Dim rs
		Dim rt
		Dim ra
		Dim sNhood
		Dim sLots
		Dim sActivity
		Dim sRegion
		Dim sSQL
		Dim MyConn
		Dim sHTML
		Dim sHTNH
		Dim sHTL 
		Dim sHTA
		Dim NTLogin, sSender, sSentDate, sText
		NTLogin = Request.ServerVariables("AUTH_USER")
		if Left(NTLogin,7) = "JWHOMES" then
			sSender = Right(NTLogin, len(NTLogin)-8) & "@jwhomes.com"
		else
			sSender = NTLogin & "@jwhomes.com"
		end if
		Set oJWI = Server.CreateObject("JWIntranet.clsRWDB")
		//sSQL = "select sPlan_Name from BuilderSuite..Plans where sPlan_Name not like 'XX%' order by sPlan_Name"
		
		sNhood = "select distinct neighborhoodname from homelinkii..neighborhoods where active = 1"
        		
		
		
		Set rt = oJWI.RunSQLReturnRS(sNhood) 
		Do while not rt.eof 
		sHTNH = sHTNH & "<option value='" & Server.HTMLEncode(rt("neighborhoodname").value) & "'" 
		If Trim(CStr(Request.Form("tNhood"))) = Trim(CStr(rt("neighborhoodname").value)) Then 
		sHTNH = sHTNH & " selected='selected' "
		 End If 
		sHTNH = sHTNH & ">" & Server.HTMLEncode(rt("neighborhoodname").value) & "</option>"
		 rt.movenext 
		Loop

		

		
		
		%>

	
	<%
		set rs = nothing
		
		%>
	<tr><td height=25></td></tr>
		<tr>
		<td align="right">Neighborhood:</td>
	<td><input type="hidden" id="formCompleted" name="formCompleted" value="1" /><select name="tNhood" ID="Select1" onchange="fetchItems(this);"><option value="" selected>Select Neighborhood</option><%=sHTNH%></select></td> 

</tr>


		<% sHTL="" 
		If "" <> CStr(Request.Form("tNhood")) AND "1"<>CStr(Request.Form("formCompleted")) Then
 		sLots = "select 'Amenity' as lotnumber union select 'Multi' union select isnull(fastlotnumber,'') + '/' + isnull(fastlotblock,'') from homelinkii..lots l join homelinkii..neighborhoods n on l.neighborhoodid = n.neighborhoodid where neighborhoodname = '" & Request.Form("tNhood") & "'" 
		Set rt = oJWI.RunSQLReturnRS(sLots) 
		If rt.EOF Then 
		Response.Write( "No results found for " & Server.HTMLEncode(sLots) ) 
		Else 
		Do while not rt.eof 
		sHTL = sHTL & "<option value='" & Server.HTMLEncode( rt("lotnumber").value) & "'" 
		sHTL = sHTL & ">" & Server.HTMLEncode( rt("lotnumber").value ) & "</option>" 
		rt.movenext
		 Loop
 		End If
 		End If
 		%>




<tr>
	<td align="right">Lot:</td>
	<td><select name="tlot" id="formCompleted" value="1"  ><option value="" selected>Select Lot</option><%=sHTL%></select></td>
</tr>

		<% sHTA="" 
		If "" <> CStr(Request.Form("tlot")) Then
		sActivity = "select distinct p.activity_no_ + ' - ' + act_descr as activity_no_ from hbs_pohd p join homelinkii..lots l on l.fastlotnumber = p.unit_id_ and l.fastlotblock = p.building_id_ and l.fastprojectid = p.project_id_ join homelinkii..neighborhoods n on n.neighborhoodid = l.neighborhoodid join hbs_actv a on a.activity_no_ = p.activity_no_ where neighborhoodname  =  '" & Request.Form("tNhood") & "' and fastlotnumber + '/' + fastlotblock = '" & Request.Form("tLot") & "'" 
		Set ra = oJWI.RunSQLReturnRS(sActivity) 
		If ra.EOF Then 
		Response.Write( "No results found for " & Server.HTMLEncode(sActivity) ) 
		Else 
		Do while not ra.eof 
		sHTA = sHTA & "<option value='" & Server.HTMLEncode( ra("activity_no_").value) & "'" 
		sHTA = sHTA & ">" & Server.HTMLEncode( ra("activity_no_").value ) & "</option>" 
		ra.movenext
 		Loop
 		End If
 		End If
	 	%>


<tr>
		<td align="right">Activity:</td>
		<td><select name="tActivity" ID="Select2"><option value="" selected>Select Activity</option><%=sHTA%></select></td>
	</tr>

Open in new window

0
 
Wayne BarronCommented:
Yea, I tried this before years ago, and was unsuccessful.
I will do some checking and see what I can find on 3 drops, and see if maybe
There is something in AJAX that will do it.

Do yourself a favor, and search google for:
AJAX 3 dropdowns
And see what you can come up with.
If you can find it, either myself or someone else can assist in making it a reality
With your code.

Good Luck.
Carrzkiss
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JWHomesAuthor Commented:
What I dont understand is in the initial selection, you pick the first drop down and the second is populated, no problem.  You pick from the second and nothing happens to the third.  BUT if you change the first the third will update to whatever it is changed to.  That tells me (at least it appears) that it is working on some strange level, just not in the order I need it to.  That is why I thought my syntax was jsut wrong, it appears the refresh on the query for the third isn't happening in the correct order
0
 
Wayne BarronCommented:
There is a LOT involved in doing this, then what you may think.
That is why it is not working.

I just did a search (The search I suggested you do)
And I found a PHP script, and am in the process of converting it over to ASP Classic
Right now.
Give me a little while and I will be right back with you.

Now.
The code that I am converting over, will work in your project.
BUT, I am not going to convert your code over to it.
It will come with a sample database and information on how to make it work for you.
You will then need to swap your database for it, and then change coding around to make it work.
AND, the code is about 1/16 the amount of code that you have for just the 2 drops that you are using right now.

Be back with you in a few.
0
 
JWHomesAuthor Commented:
Awesome, thank you very much
0
 
hieloCommented:
>>There is a LOT involved in doing this,
I respectfully disagree.  He just needs to call the onchange on the second dropdown and the whole form will repost, giving him access to the values of both the first and second list so that he can generate the list for the third.  The javascript I gave him is generic enough that he can just keep adding/chaining more selects and it will work as long as any of the "parent" select is coded with the onchange event and calls fetchItems

Thus, regarding post ID 36512661:
Line 93:
>>  <select name="tlot" id="formCompleted" value="1"  >

should be:
     <select name="tlot" id="tlot" onchange="fetchItems(this);">

Line 98:
...and fastlotnumber + '/' + fastlotblock = '" & Request.Form("tLot") & "'"

add parentheses:
...and (fastlotnumber + '/' + fastlotblock) = '" & Request.Form("tLot") & "'"
0
 
JWHomesAuthor Commented:
hielo you are once again my absolute hero.  Thank you again for all your help, that worked perfectly.  I am finally done with this project!!!!
0
 
JWHomesAuthor Commented:
EXCELLENT as usual
0
 
JWHomesAuthor Commented:
Just wanted to follow up with a final concern, everything works great except the second drop down isn't maintaining its selection.  It is losing it as soon as it is selected, BUT working absolutely correctly to populate drop down three.  I know I probably moved something slighly that I should not have...
<script type="text/javascript">
 function fetchItems(sel){ 
sel.form.action="<%=Request.ServerVariables("SCRIPT_NAME")%>";
sel.form.onsubmit=function(){return true}; 
//this is so you know if the for was the result of an "onchange" 
//Or if the user clicked submit 
document.getElementById("formCompleted").value=0;
 sel.form.submit(); 
} 
</script>




<form action="websiteupdate_send.asp" METHOD="POST" name="frmData" onsubmit="return verifyData(this)">
<table border="0">
	<%	Dim rs
		Dim rt
		Dim ra
		Dim sNhood
		Dim sLots
		Dim sActivity
		Dim sRegion
		Dim sSQL
		Dim MyConn
		Dim sHTML
		Dim sHTNH
		Dim sHTL 
		Dim sHTA
		Dim NTLogin, sSender, sSentDate, sText
		NTLogin = Request.ServerVariables("AUTH_USER")
		if Left(NTLogin,7) = "JWHOMES" then
			sSender = Right(NTLogin, len(NTLogin)-8) & "@jwhomes.com"
		else
			sSender = NTLogin & "@jwhomes.com"
		end if
		Set oJWI = Server.CreateObject("JWIntranet.clsRWDB")
		//sSQL = "select sPlan_Name from BuilderSuite..Plans where sPlan_Name not like 'XX%' order by sPlan_Name"
		
		sNhood = "select distinct neighborhoodname from homelinkii..neighborhoods where active = 1"
        		
		
		
		Set rt = oJWI.RunSQLReturnRS(sNhood) 
		Do while not rt.eof 
		sHTNH = sHTNH & "<option value='" & Server.HTMLEncode(rt("neighborhoodname").value) & "'" 
		If Trim(CStr(Request.Form("tNhood"))) = Trim(CStr(rt("neighborhoodname").value)) Then 
		sHTNH = sHTNH & " selected='selected' "
		 End If 
		sHTNH = sHTNH & ">" & Server.HTMLEncode(rt("neighborhoodname").value) & "</option>"
		 rt.movenext 
		Loop

		

		
		
		%>

	
		<%
		set rs = nothing
		
		%>
			<tr><td height=25></td></tr>
			<tr>
			<td align="right">Neighborhood:</td>
			<td><input type="hidden" id="formCompleted" name="formCompleted" value="1" /><select name="tNhood" ID="Select1" onchange="fetchItems(this);"><option value="" selected>Select Neighborhood</option><%=sHTNH%></select></td> 

			</tr>


		<% sHTL="" 
		If "" <> CStr(Request.Form("tNhood")) AND "1"<>CStr(Request.Form("formCompleted")) Then
 		sLots = "select 'Amenity' as lotnumber union select 'Multi' union select isnull(fastlotnumber,'') + '/' + isnull(fastlotblock,'') from homelinkii..lots l join homelinkii..neighborhoods n on l.neighborhoodid = n.neighborhoodid where neighborhoodname = '" & Request.Form("tNhood") & "'" 
		Set rt = oJWI.RunSQLReturnRS(sLots) 
		If rt.EOF Then 
		Response.Write( "No results found for " & Server.HTMLEncode(sLots) ) 
		Else 
		Do while not rt.eof 
		sHTL = sHTL & "<option value='" & Server.HTMLEncode( rt("lotnumber").value) & "'" 
		sHTL = sHTL & ">" & Server.HTMLEncode( rt("lotnumber").value ) & "</option>" 
		rt.movenext
		 Loop
 		End If
 		End If
 		%>




<tr>
	<td align="right">Lot:</td>
	<td> <input type="hidden" id="formCompleted" name="formCompleted" value="1" /><select name="tlot" ID="tlot" onchange="fetchItems(this);" ><option value="" selected>Select Lot</option><%=sHTL%></select></td>
</tr>

		<% sHTA="" 
		If "" <> CStr(Request.Form("tlot")) AND "1"<>CStr(Request.Form("formCompleted")) Then
		sActivity = "select distinct p.activity_no_ + ' - ' + act_descr as activity_no_ from hbs_pohd p join homelinkii..lots l on l.fastlotnumber = p.unit_id_ and l.fastlotblock = p.building_id_ and l.fastprojectid = p.project_id_ join homelinkii..neighborhoods n on n.neighborhoodid = l.neighborhoodid join hbs_actv a on a.activity_no_ = p.activity_no_ where neighborhoodname = '" & Request.Form("tNhood") & "' and (fastlotnumber + '/' + fastlotblock) = '" & Request.Form("tLot") & "'" 
		Set ra = oJWI.RunSQLReturnRS(sActivity) 
		If ra.EOF Then 
		Response.Write( "No results found for " & Server.HTMLEncode(sActivity) ) 
		Else 
		Do while not ra.eof 
		sHTA = sHTA & "<option value='" & Server.HTMLEncode( ra("activity_no_").value) & "'" 
		sHTA = sHTA & ">" & Server.HTMLEncode( ra("activity_no_").value ) & "</option>" 
		ra.movenext
 		Loop
 		End If
 		End If
	 	%>


<tr>
		<td align="right">Activity:</td>
		<td><select name="tActivity" ID="Select3" ><option value="" selected>Select Activity</option><%=sHTA%></select></td>
	</tr>

Open in new window

0
 
Wayne BarronCommented:
>> I respectfully disagree

That is the great thing about "YOU" writing the code.
You know what needs to be done in order to make it work.

Glad you have it working.
0
 
hieloCommented:
This is exactly the same problem that you had for the first list. The fix for the first list is on lines 47-49.  Copy those three lines BETWEEN 81 and 82 (82 will shift down three lines) and adjust the Request.Form() argument to reflect the name of the second list.
0
 
JWHomesAuthor Commented:
Of course that worked, your advice has been spot on.  Thanks again for all your help and your extreme patience.  This has been an eye opening learning experience.  
0
 
Wayne BarronCommented:
Who is that meant for hielo?
0
 
hieloCommented:
>>Who is that meant for hielo?
JWHomes
0
 
Wayne BarronCommented:
ok
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now