AJAX / ASP Dependent Dropdown

ASP Free Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

October 17th, 2006, 06:04 AM

degsy

Contributing User

ASP Free God 2nd Plane (6000 - 6499 posts)

Join Date: Aug 2005

Location: North East, UK

Posts: 6,191

Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 120

AJAX / ASP Dependent Dropdown

Here is an example of a Dependent Dropdown using Pure ASP.
The addition here is that it also implements AJAX (xmlHTTP) to call the script dynamically without submitting/refreshing the page.

Here is the basic script without any major validation, error messages or other form elements.
http://computer-helpforum.com/asp/a...nt_dropdown.asp

The example uses car manufacturers and models

Here is the the first dropdown using a standard recordset loop to popuplate the dropdown.

asp Code:

Original - asp Code

<form name="form1" method="get" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
  <select name="sMan" id="sMan" onchange="ajaxcombo('sMan','content_model')">
  <option value="-1">Choose Manufacturer</option>
    <%
    Set rsMan=Server.CreateObject("ADODB.recordset")
    rsMan.Open "SELECT manufacturer_id, manufacturer_name FROM manufacturer", conn
        While Not rsMan.EOF
            opt = "<option value=""" & rsMan("manufacturer_id") & """"
            If CStr(rsMan("manufacturer_id")) = CStr(sMan) Then
                opt = opt & " selected"
            End If
            opt = opt & ">" & rsMan("manufacturer_name") & "</option>" & vbCRLF
            Response.Write opt
            rsMan.MoveNext
        Wend
    rsMan.Close
    %>
  </select>
  <noscript><input type="submit" name="GetModels" value="Get Models"></noscript>
 
<span id="content_model"><%Call getModels(sMan)%></span>

Three main features of this code are

Code:

onchange="ajaxcombo('sMan','content_model')

This is the Ajax code that calls the dynamic script to populate the second dropdown.

Code:

<noscript><input type="submit" name="GetModels" value="Get Models"></noscript>

This code shows a submit button if Javascript (AJAX) is disabled.

Code:

<span id="content_model"><%Call getModels(sMan)%></span>

This calls the Sub that populates the second dropdown.

asp Code:

Original - asp Code

Sub getModels(manID)
    sql = "SELECT model.model_id, model.model_name"
    sql = sql & " FROM model INNER JOIN (manufacturer INNER JOIN man_mod ON manufacturer.manufacturer_id = man_mod.manufacturer_id) ON model.model_id = man_mod.model_id"
    sql = sql & " WHERE (((man_mod.manufacturer_id)=" & manID & "))"
    sql = sql & " ORDER BY model.model_name"
 
    Response.Write "<select name=""sMod"" onchange=""this.form.submit()"""
    If manID = -1 Then 
        Response.Write " disabled"
    End If
    Response.Write ">" & vbCRLF
    
    Response.Write "<option value=""-1"">Choose"
    If manID = -1 Then
        Response.Write " Manufacturer First"
    Else 
    Response.Write " Model"
    End If
    Response.Write "</option>" & vbCRLF
    
    If manID > 0 Then
        Set rsMod=Server.CreateObject("ADODB.recordset")
        
    
        rsMod.Open sql, conn
        While Not rsMod.EOF
            opt = "<option value=""" & rsMod("model_id") & """"
            If CStr(rsMod("model_id")) = CStr(sMod) Then
                opt = opt & " selected"
            End If
            opt = opt & ">" & rsMod("model_name") & "</option>" & vbCRLF
            Response.Write opt
            rsMod.MoveNext
        Wend
        rsMod.Close
    End If
    Response.Write "</select>" & vbCRLF
    Response.Write "<noscript><input type=""submit"" name=""GetModelsDetails"" value=""Get Model Details"""
    If manID = -1 Then 
        Response.Write " disabled"
    End If
    Response.Write "></noscript>"
End Sub

This is the Sub to populate the second dropdown. It takes the ID of the Manufacturer submitted to get the associated models.

The script will work using Pure ASP and HTML.
If you want to add the AJAX functionallity then you need a Javascript.

The example here is taken from
http://www.dynamicdrive.com/dynamic...ajaxcontent.htm

asp Code:

Original - asp Code

<script type="text/javascript">
 
/***********************************************
* Dynamic Ajax Content- © Dynamic Drive DHTML code library (www.dynamicdrive.com)
* This notice MUST stay intact for legal use
* Visit Dynamic Drive at http://www.dynamicdrive.com/ for full source code
***********************************************/
 
var loadedobjects = "";
var rootdomain = "http://" + window.location.hostname;
 
function ajaxpage(url, containerid){
var page_request = false;
    if (window.XMLHttpRequest) // if Mozilla, Safari etc
        page_request = new XMLHttpRequest()
    else if (window.ActiveXObject){ // if IE
        try {
            page_request = new ActiveXObject("Msxml2.XMLHTTP")
        } 
        catch (e){
            try{
                page_request = new ActiveXObject("Microsoft.XMLHTTP")
            }
        catch (e){}
        }
    }
    else
        return false
        
        page_request.onreadystatechange = function(){
        loadpage(page_request, containerid);
    }
 
    page_request.open('GET', url, true);
    page_request.send(null);
}
 
function loadpage(page_request, containerid){
    if(page_request.readyState == 4 && (page_request.status == 200 || window.location.href.indexOf("http") == -1)){
        document.getElementById(containerid).innerHTML = page_request.responseText;
    }
}
 
/***Combo Menu Load Ajax snippet**/
function ajaxcombo(selectobjID, loadarea){
var selectobj = document.getElementById ? document.getElementById(selectobjID) : ""
    if(selectobj != "" && selectobj.options[selectobj.selectedIndex].value != ""){
        url = '<%=Request.ServerVariables("SCRIPT_NAME")%>?method=ajax&sMan=' + selectobj.options[selectobj.selectedIndex].value;
        ajaxpage(url, loadarea);
    }
}
</script>

Code:

url = '<%=Request.ServerVariables("SCRIPT_NAME")%>?method=ajax&sMan=' + selectobj.options[selectobj.selectedIndex].value;

When the script it called it will take the value of the select and append it to the URL. It will then call the Sub dynamically (in the background).

asp Code:

Original - asp Code
If Request("method") = "ajax" Then Call getModels(sMan) conn.Close() Response.End() End If

If Request("method") = "ajax" Then
    Call getModels(sMan)
    conn.Close()
    Response.End()
End If

If the AJAX script is called then this code will detect it, call the Sub and return the populated dropdown HTML.
The AJAX script will then replace the HTML dynamically in the webpage.

The full script can be seen below.
The script and database are attached.

Attached Files

ajax_dependent_dropdown.zip (17.1 KB, 608 views)

__________________
CyberTechHelp

October 17th, 2006, 06:06 AM

degsy

Contributing User

Join Date: Aug 2005

Location: North East, UK

Posts: 6,191

Time spent in forums: 3 Weeks 4 Days 19 h 41 m 52 sec
Reputation Power: 120

This is the full script for the Ajax Dependent Dropdown.
Also added is a recordset to display the model details when the model is chosen.

asp Code:

Original - asp Code

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Response.Expires = 60
Response.Expiresabsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"

sMan = -1
If Len(Request("sMan")) > 0 And IsNumeric(Request("sMan")) Then
	sMan = CInt(Request("sMan"))
End If

sMod = -1
If Len(Request("sMod")) > 0 And IsNumeric(Request("sMod")) Then
	sMod = CInt(Request("sMod"))
End If

Set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open Server.MapPath("cars.mdb")

If Request("method") = "ajax" Then
	Call getModels(sMan)
	conn.Close()
	Response.End()
End If

Sub getModels(manID)
	sql = "SELECT model.model_id, model.model_name"
	sql = sql & " FROM model INNER JOIN (manufacturer INNER JOIN man_mod ON manufacturer.manufacturer_id = man_mod.manufacturer_id) ON model.model_id = man_mod.model_id"
	sql = sql & " WHERE (((man_mod.manufacturer_id)=" & manID & "))"
	sql = sql & " ORDER BY model.model_name"

Response.Write "<select name=""sMod"" onchange=""this.form.submit()"""
	If manID = -1 Then 
		Response.Write " disabled"
	End If
	Response.Write ">" & vbCRLF
	
	Response.Write "<option value=""-1"">Choose"
	If manID = -1 Then
		Response.Write " Manufacturer First"
	Else 
	Response.Write " Model"
	End If
	Response.Write "</option>" & vbCRLF
	
	If manID > 0 Then
		Set rsMod=Server.CreateObject("ADODB.recordset")
		
	
		rsMod.Open sql, conn
		While Not rsMod.EOF
			opt = "<option value=""" & rsMod("model_id") & """"
			If CStr(rsMod("model_id")) = CStr(sMod) Then
				opt = opt & " selected"
			End If
			opt = opt & ">" & rsMod("model_name") & "</option>" & vbCRLF
			Response.Write opt
			rsMod.MoveNext
		Wend
		rsMod.Close
	End If
	Response.Write "</select>" & vbCRLF
	Response.Write "<noscript><input type=""submit"" name=""GetModelsDetails"" value=""Get Model Details"""
	If manID = -1 Then 
		Response.Write " disabled"
	End If
	Response.Write "></noscript>"
End Sub

Sub getModelDetails(modID)
	sql = "SELECT model.model_id, model.model_name, manufacturer.manufacturer_name"
	sql = sql & " FROM model INNER JOIN (manufacturer INNER JOIN man_mod ON manufacturer.manufacturer_id = man_mod.manufacturer_id)"
	sql = sql & " ON model.model_id = man_mod.model_id"
	sql = sql & " WHERE (((model.model_id)=" & modID & "));"

If modID > 0 Then
		Set rsModDetails=Server.CreateObject("ADODB.recordset")
		rsModDetails.Open sql, conn
		
		If Not rsModDetails.EOF Then
			Response.Write "<table border=""1"">" & vbCRLF
			Response.Write "<tr><td>model_id</td><td>model_name</td><td>manufacturer_name</td></tr>" & vbCRLF
			Response.Write "<tr><td>" & rsModDetails("model_id") & "</td>"
			Response.Write "<td>" & rsModDetails("model_name") & "</td>"
			Response.Write "<td>" & rsModDetails("manufacturer_name") & "</td></tr>" & vbCRLF
			Response.Write "</table>"
		End If
		rsModDetails.Close
	End If
End Sub
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>AJAX Dependent Dropdown</title>
<script type="text/javascript">

/***********************************************
* Dynamic Ajax Content- © Dynamic Drive DHTML code library (www.dynamicdrive.com)
* This notice MUST stay intact for legal use
* Visit Dynamic Drive at http://www.dynamicdrive.com/ for full source code
***********************************************/

var loadedobjects = "";
var rootdomain = "http://" + window.location.hostname;

function ajaxpage(url, containerid){
var page_request = false;
	if (window.XMLHttpRequest) // if Mozilla, Safari etc
		page_request = new XMLHttpRequest()
	else if (window.ActiveXObject){ // if IE
		try {
			page_request = new ActiveXObject("Msxml2.XMLHTTP")
		} 
		catch (e){
			try{
				page_request = new ActiveXObject("Microsoft.XMLHTTP")
			}
		catch (e){}
		}
	}
	else
		return false
		
		page_request.onreadystatechange = function(){
		loadpage(page_request, containerid);
	}

page_request.open('GET', url, true);
	page_request.send(null);
}

function loadpage(page_request, containerid){
	if(page_request.readyState == 4 && (page_request.status == 200 || window.location.href.indexOf("http") == -1)){
		document.getElementById(containerid).innerHTML = page_request.responseText;
	}
}

/***Combo Menu Load Ajax snippet**/
function ajaxcombo(selectobjID, loadarea){
var selectobj = document.getElementById ? document.getElementById(selectobjID) : ""
	if(selectobj != "" && selectobj.options[selectobj.selectedIndex].value != ""){
		url = '<%=Request.ServerVariables("SCRIPT_NAME")%>?method=ajax&sMan=' + selectobj.options[selectobj.selectedIndex].value;
		ajaxpage(url, loadarea);
	}
}
</script>
</head>

<body>
<form name="form1" method="get" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
  <select name="sMan" id="sMan" onchange="ajaxcombo('sMan','content_model')">
  <option value="-1">Choose Manufacturer</option>
	<%
	Set rsMan=Server.CreateObject("ADODB.recordset")
	rsMan.Open "SELECT manufacturer_id, manufacturer_name FROM manufacturer", conn
		While Not rsMan.EOF
			opt = "<option value=""" & rsMan("manufacturer_id") & """"
			If CStr(rsMan("manufacturer_id")) = CStr(sMan) Then
				opt = opt & " selected"
			End If
			opt = opt & ">" & rsMan("manufacturer_name") & "</option>" & vbCRLF
			Response.Write opt
			rsMan.MoveNext
		Wend
	rsMan.Close
	%>
  </select>
  <noscript><input type="submit" name="GetModels" value="Get Models"></noscript>
  
  <span id="content_model"><%Call getModels(sMan)%></span>

</form>
<%
If sMod > 0 Then 
	Call getModelDetails(sMod)
End If
conn.Close
%>
</body>
</html>

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Response.Expires = 60
Response.Expiresabsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"
 
sMan = -1
If Len(Request("sMan")) > 0 And IsNumeric(Request("sMan")) Then
    sMan = CInt(Request("sMan"))
End If
 
sMod = -1
If Len(Request("sMod")) > 0 And IsNumeric(Request("sMod")) Then
    sMod = CInt(Request("sMod"))
End If
 
Set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open Server.MapPath("cars.mdb")
 
If Request("method") = "ajax" Then
    Call getModels(sMan)
    conn.Close()
    Response.End()
End If
 
 
Sub getModels(manID)
    sql = "SELECT model.model_id, model.model_name"
    sql = sql & " FROM model INNER JOIN (manufacturer INNER JOIN man_mod ON manufacturer.manufacturer_id = man_mod.manufacturer_id) ON model.model_id = man_mod.model_id"
    sql = sql & " WHERE (((man_mod.manufacturer_id)=" & manID & "))"
    sql = sql & " ORDER BY model.model_name"
 
    Response.Write "<select name=""sMod"" onchange=""this.form.submit()"""
    If manID = -1 Then 
        Response.Write " disabled"
    End If
    Response.Write ">" & vbCRLF
    
    Response.Write "<option value=""-1"">Choose"
    If manID = -1 Then
        Response.Write " Manufacturer First"
    Else 
    Response.Write " Model"
    End If
    Response.Write "</option>" & vbCRLF