Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Feb 2006
    Location
    Ireland
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic fill a form using asp recordset

    Hi,

    I posted this to the Javascipt forum but I'm afraid my Javascripts not great and I'd rather work with ASP. Here's what I want to do; I have an Access table of countries and regions with an ID and ParentID field. Those with a ParentID of 0 are countries. I can manipulate them using ASP to include in a web page, but I want to populate a drop-down list of all countries and when a country is selected another dropdown list is populated with the regions of that country - i.e. those with a ParentID equal to the ID field of the parent. I've been through all of the threads but can't find anything to apply (there's probably something there but I don't recognise it!). I realise I'll have to use Javascript eventhandlers and perhaps to fill the form field. Any help appreciated.

  • #2
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you want dependent drop downs then there are a few ways to do it.

    Pure ASP.
    You submit the page and the value submitted is used in the query to populdate the second select.

    http://computer-helpforum.com/asp/select.asp
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="db.asp" -->
    <%
    Dim manufacturer
    Dim manufacturer_numRows
    
    Set manufacturer = Server.CreateObject("ADODB.Recordset")
    manufacturer.ActiveConnection = conn
    manufacturer.Source = "SELECT id, name FROM manufacturer ORDER BY name ASC"
    manufacturer.CursorType = 0
    manufacturer.CursorLocation = 2
    manufacturer.LockType = 1
    manufacturer.Open()
    
    manufacturer_numRows = 0
    %>
    <%
    Dim model__MMColParam
    model__MMColParam = "1"
    If (Request.Form("selMan") <> "") Then 
      model__MMColParam = Request.Form("selMan")
      selMan = Request.Form("selMan")
    End If
    %>
    <%
    Dim model
    Dim model_numRows
    
    Set model = Server.CreateObject("ADODB.Recordset")
    model.ActiveConnection = conn
    model.Source = "SELECT id, model_name FROM model WHERE manufacturer_id = " + Replace(model__MMColParam, "'", "''") + " ORDER BY model_name ASC"
    model.CursorType = 0
    model.CursorLocation = 2
    model.LockType = 1
    model.Open()
    
    model_numRows = 0
    %>
    <!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>Untitled Document</title>
    <script type="text/javascript">
    function chkForm(el){
    	if(el.value=="Choose"){
    		(el.name=="selMan")? msg='manufacturer' : msg='model';
    		alert('Please choose a ' + msg);
    	}
    	else if(el.name == "selMan"){
    		el.form.submit();
    	}
    	else{
    		el.form.action = "/asp/accessories/model_detail.asp";
    		el.form.method = "get";
    		el.form.submit();
    	}
    }
    </script>
    </head>
    
    <body>
    <form name="form1" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
      <select name="selMan" onchange="chkForm(this)">
        <option value="Choose" <%If (Not isNull(selMan)) Then If ("Choose" = CStr(selMan)) Then Response.Write("SELECTED") : Response.Write("")%>>Choose Manufacturer</option>
        <%
    While (NOT manufacturer.EOF)
    %>
        <option value="<%=(manufacturer.Fields.Item("id").Value)%>" <%If (Not isNull(selMan)) Then If (CStr(manufacturer.Fields.Item("id").Value) = CStr(selMan)) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(manufacturer.Fields.Item("name").Value)%></option>
        <%
      manufacturer.MoveNext()
    Wend
    If (manufacturer.CursorType > 0) Then
      manufacturer.MoveFirst
    Else
      manufacturer.Requery
    End If
    %>
      </select>
      <select name="model_id" <%If selMan="" Then%>disabled="disabled"<%End If%> onchange="chkForm(this)">
        <option value="Choose" <%If (Not isNull(selMod)) Then If ("Choose" = CStr(selMod)) Then Response.Write("SELECTED") : Response.Write("")%>>Choose Model</option>
        <%
    While (NOT model.EOF)
    %>
        <option value="<%=(model.Fields.Item("id").Value)%>" <%If (Not isNull(selMod)) Then If (CStr(model.Fields.Item("id").Value) = CStr(selMod)) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(model.Fields.Item("model_name").Value)%></option>
        <%
      model.MoveNext()
    Wend
    If (model.CursorType > 0) Then
      model.MoveFirst
    Else
      model.Requery
    End If
    %>
      </select>
    </form>
    </body>
    </html>
    <%
    manufacturer.Close()
    Set manufacturer = Nothing
    %>
    <%
    model.Close()
    Set model = Nothing
    %>
    Javascript
    You load all your data into javascript arrays and let javascript populate the boxes.

    Ajax
    You call your script using Ajax and this populates the dropdown.

    http://computer-helpforum.com/asp/aj...t_via_ajax.asp
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="db.asp" -->
    <%
    Dim manufacturer
    Dim manufacturer_numRows
    
    Set manufacturer = Server.CreateObject("ADODB.Recordset")
    manufacturer.ActiveConnection = conn
    manufacturer.Source = "SELECT id, name FROM manufacturer ORDER BY name ASC"
    manufacturer.CursorType = 0
    manufacturer.CursorLocation = 2
    manufacturer.LockType = 1
    manufacturer.Open()
    
    manufacturer_numRows = 0
    %>
    <!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>Select via Ajax</title>
    <script type="text/javascript">
    
    /***********************************************
    * Dynamic Ajax Content- &#169; 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
    }
    
    function loadobjs(){
    if (!document.getElementById)
    return
    for (i=0; i<arguments.length; i++){
    var file=arguments[i]
    var fileref=""
    if (loadedobjects.indexOf(file)==-1){ //Check to see if this object has not already been added to page before proceeding
    if (file.indexOf(".js")!=-1){ //If object is a js file
    fileref=document.createElement('script')
    fileref.setAttribute("type","text/javascript");
    fileref.setAttribute("src", file);
    }
    else if (file.indexOf(".css")!=-1){ //If object is a css file
    fileref=document.createElement("link")
    fileref.setAttribute("rel", "stylesheet");
    fileref.setAttribute("type", "text/css");
    fileref.setAttribute("href", file);
    }
    }
    if (fileref!=""){
    document.getElementsByTagName("head").item(0).appendChild(fileref)
    loadedobjects+=file+" " //Remember this object as being already added to page
    }
    }
    }
    
    </script>
    
    <script type="text/javascript">
    /***Combo Menu Load Ajax snippet**/
    function ajaxcombo(selectobjID, loadarea){
    var selectobj=document.getElementById? document.getElementById(selectobjID) : ""
    if (selectobj!="" && selectobj.options[selectobj.selectedIndex].value!="")
    ajaxpage('select_via_ajax_include.asp?manufacturer_id=' + selectobj.options[selectobj.selectedIndex].value, loadarea)
    }
    </script>
    </head>
    
    <body>
    <form name="form1" method="get" action="/php/model_detail.php">
      <p>
        <select name="sel_man" onchange="(this.selectedIndex!=0)? ajaxcombo('sel_man', 'sel_mod') : alert('Please choose a manufacturer'); return false">
          <option value="0">Choose Manufacturer</option>
          <%
    While (NOT manufacturer.EOF)
    %>
          <option value="<%=(manufacturer.Fields.Item("id").Value)%>"><%=(manufacturer.Fields.Item("name").Value)%></option>
          <%
      manufacturer.MoveNext()
    Wend
    If (manufacturer.CursorType > 0) Then
      manufacturer.MoveFirst
    Else
      manufacturer.Requery
    End If
    %>
        </select>
        <span id="sel_mod">
        <select name="dummy_select" disabled>
          <option>Choose Model</option>
        </select>
    </span></p>
      <table width="200" border="1" cellspacing="0" cellpadding="2">
        <tr>
          <td>Name</td>
          <td><input name="textfield" type="text" value="Joe"></td>
        </tr>
        <tr>
          <td>Email</td>
          <td><input type="text" name="textfield"></td>
        </tr>
        <tr>
          <td>Comments</td>
          <td><textarea name="textarea">This is some text</textarea></td>
        </tr>
      </table>
      <p>&nbsp;
        </p>
    </form>
    </body>
    </html>
    <%
    manufacturer.Close()
    Set manufacturer = Nothing
    %>
    Code:
    <!--#include file="db.asp" -->
    <%
    Dim model__MMColParam
    model__MMColParam = "1"
    If (Request.QueryString("manufacturer_id") <> "") Then 
      model__MMColParam = Request.QueryString("manufacturer_id")
    End If
    %>
    <%
    Dim model
    Dim model_numRows
    
    Set model = Server.CreateObject("ADODB.Recordset")
    model.ActiveConnection = conn
    model.Source = "SELECT id, model_name FROM model WHERE manufacturer_id = " + Replace(model__MMColParam, "'", "''") + " ORDER BY model_name ASC"
    model.CursorType = 0
    model.CursorLocation = 2
    model.LockType = 1
    model.Open()
    
    model_numRows = 0
    %>
    <%
    '<select name="model_id" onchange="(this.selectedIndex != 0)? this.form.submit() : alert('Please choose a model'); return false">
    %>
    <select name="model_id">
      <option value="0">Choose Model</option>
      <%
    While (NOT model.EOF)
    %>
      <option value="<%=(model.Fields.Item("id").Value)%>"><%=(model.Fields.Item("model_name").Value)%></option>
      <%
      model.MoveNext()
    Wend
    If (model.CursorType > 0) Then
      model.MoveFirst
    Else
      model.Requery
    End If
    %>
    </select>
    <%
    model.Close()
    Set model = Nothing
    %>

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Perhaps this may hopefully give you some ideas. It's got a downloadable sample and does retain values after the post back (although it is admittedly somewhat complicated):

    Classic ASP Design Tips - Dependent Listboxes
    http://www.bullschmidt.com/devtip-de...tlistboxes.asp
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •