Solved

Problems with ASP dropdowns fed by SQL ADO

Posted on 2011-09-09
16
371 Views
Last Modified: 2012-05-12
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
Comment
Question by:JWHomes
  • 7
  • 6
  • 3
16 Comments
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
 

Author Comment

by:JWHomes
Comment Utility
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
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
 

Author Comment

by:JWHomes
Comment Utility
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
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
 

Author Comment

by:JWHomes
Comment Utility
Awesome, thank you very much
0
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
Comment Utility
>>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
 

Author Comment

by:JWHomes
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Closing Comment

by:JWHomes
Comment Utility
EXCELLENT as usual
0
 

Author Comment

by:JWHomes
Comment Utility
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
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
>> 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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
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
 

Author Comment

by:JWHomes
Comment Utility
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
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
Who is that meant for hielo?
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>Who is that meant for hielo?
JWHomes
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
ok
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now