...

View Full Version : Dynamic fill a form using asp recordset



websmith
02-24-2006, 06:54 PM
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.

degsy
02-27-2006, 03:05 PM
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


<%@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/ajax/select_via_ajax.asp


<%@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
%>




<!--#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
%>

Bullschmidt
03-07-2006, 02:23 AM
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-dependentlistboxes.asp



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum