Filling dropdown options directly from the database

[FabriceB]

Hi, my question is based on the tutorial I was reading on w3s.

Here is the link with the example and the sample code:
http://www.w3schools.com/Ajax/ajax_database.asp

now here is what I would like to accomplish:

I'd like to be able to add an additional dropdown based on the data we retrieve from the recordset.

now here is the part i'm having issues with... I am trying to figure out how to pass the recordset back to the html page in order to then create the dropdown. Once I understand how to pass it back I know I can deal with creating the dropdown... it's just a matter of looping through the RS.

I have read about JSON being one option but i can't seem to see clearly how to go about it with it...

Does anyone have any sample code which would help or even better, could anyone help me out? please note that it doesn't have to be with JSON! I would prefer just asp and js... but if need be, by all means!

Thanks!

[bdl]

Unless I'm just not understanding your question, I think the tutorial shows pretty well how to iterate through the resultset and return the data back to the calling XHR object. Perhaps you should ask in the appropriate server-side language forum?

I personally prefer JSON over other formats, but returning straight data embedded in HTML is another option.

[FabriceB]

Sorry I realize that i wasn't as clear as i could've been. In the getcustomer.asp file they handle the recordset by putting it in a table and sending it back through response.write. The reason why i don't want to deal with the recordset in that file is because i would like to make it as generic as possible. This would enable me to use this code for other situations. for example, in the getcustomer.asp they print it as a table but on my end i'd like to use that rs and create another dropdown.


so i would want to construct my getcustomer.asp this:

<%
// create connection
dim retrieveConn
dim sp
dim menu
set retrieveConn = new connectionManager
// set stored procedure call
sp = "" & request("filter") & ""
//response.write(sp);

retrieveConn.storedprocedure = sp
retrieveConn.connect

if not retrieveConn.hasErrorOccured then
// send back recordset to deal with it accordingly
// on the other end
else
response.write("<div class='error'>")
response.write(retrieveConn.messageString)
response.write("</div>")
end if

set retrieveConn = nothing
%>


does that help understand a little better? I realize I am not always clear which i'm trying to work on so please let me know if you still require more details!

[FabriceB]

Ok... So after a bit of digging and some moments of :mad:, it seems like it all worked out.

Here is the dropdowns I had to fill in:

<fieldset>
<legend><strong> Selection </strong></legend>
<table>
<tr>
<td align="left"><strong>Department:</strong></td>
<td>
<select name="DeptSelect" id="DeptSelect" style="WIDTH:275px" onChange="listOnChange(this)">
<option value=null>Select a Department</option>
<%Call cascadingpopulateSelect("Dept_Id",session("deptSelect"), request("deptSelect"))%>
</select>
</td>
</tr>
<tr>
<td><strong>Manager:</strong></td>
<td>
<select name="MgrSelect" id="MgrSelect" style="WIDTH:275px" onChange="return listOnChange(this)">
<option value=null>Select a Manager</option>
</select>
</td>
</tr>
<tr>
<td><strong>Team Leader:</strong></td>
<td>
<select name="tlSelect" id="tlSelect" style="WIDTH:275px" onChange="return listOnChange(this)">
<option value=null>Select a Team Leader</option>
</select>
</td>
</tr>
<tr>
<td><strong>CSR:</strong></td>
<td>
<select name="csrSelect" id="csrSelect" style="WIDTH:275px">
<option value=null>Select a CSR</option>
</select>
</td>
</tr>
</table>
</fieldset>

Here is the portion of the code which I took from w3s and slightly modified:
<Script language="javascript">
// declare a global XMLHTTP Request object
var XmlHttpObj;

// create an instance of XMLHTTPRequest Object, varies with browser type, try for IE first then Mozilla
function CreateXmlHttpObj()
{
// try creating for IE (note: we don't know the user's browser type here, just attempting IE first.)
try
{ XmlHttpObj = new ActiveXObject("Msxml2.XMLHTTP"); }
catch(e)
{
try
{ XmlHttpObj = new ActiveXObject("Microsoft.XMLHTTP"); }
catch(oc)
{ XmlHttpObj = null; }
}
// if unable to create using IE specific code then try creating for Mozilla (FireFox)
if(!XmlHttpObj && typeof XMLHttpRequest != "undefined")
{ XmlHttpObj = new XMLHttpRequest(); }
}

var requestUrl;
var nextDropDown;

function setDD(dd)
{
if(dd == "DeptSelect")
{ nextDropDown = "MgrSelect"; }
else if(dd == "MgrSelect")
{ nextDropDown = "tlSelect"; }
else
{ nextDropDown = "csrSelect"; }
}

function getDD()
{ return window.nextDropDown; }

function setUrl(aURL)
{ requestUrl = aURL; }

// called from onChange or onClick event of the continent dropdown list
function listOnChange(origin)
{
var genericList = document.getElementById(origin.id);
var selectedVariable = genericList.options[genericList.selectedIndex].value;
// according to dropdown selection, call s.p.
if(origin.id == "DeptSelect")
{
q = 'Prg_GetHierarchy "'+ document.getElementById("from_date").value + '","' + document.getElementById("to_date").value + '","' + encodeURIComponent(selectedVariable) + '"';
setUrl(location.href.substring(0,location.href.lastIndexOf("/")+1) + "data_provider.asp" + "?filter=" + q);
setDD(origin.id);
}
else if(origin.id == "MgrSelect")
{
q = 'Prg_GetHierarchy "'+ document.getElementById("from_date").value + '","' + document.getElementById("to_date").value + '","' + document.getElementById("DeptSelect").value + '","' + encodeURIComponent(selectedVariable) + '"';
setUrl(location.href.substring(0,location.href.lastIndexOf("/")+1) + "data_provider.asp" + "?filter=" + q);
setDD(origin.id);
}
else if(origin.id == "tlSelect")
{
q = 'Prg_GetHierarchy "'+ document.getElementById("from_date").value + '","' + document.getElementById("to_date").value + '","' + document.getElementById("DeptSelect").value + '","' + document.getElementById("MgrSelect").value + '","' + encodeURIComponent(selectedVariable) + '"';
setUrl(location.href.substring(0,location.href.lastIndexOf("/")+1) + "data_provider.asp" + "?filter=" + q);
setDD(origin.id);
}
else
{
q = 'Prg_GetHierarchy "'+ document.getElementById("from_date").value + '","' + document.getElementById("to_date").value + '","' + document.getElementById("DeptSelect").value + '","' + document.getElementById("MgrSelect").value + '","' + document.getElementById("tlSelect").value + '","' + encodeURIComponent(selectedVariable) + '"';
setUrl(location.href.substring(0,location.href.lastIndexOf("/")+1) + "data_provider.asp" + "?filter=" + q);
setDD(origin.id);
}

CreateXmlHttpObj();

// verify XmlHttpObj variable was successfully initialized
if(XmlHttpObj)
{
XmlHttpObj.onreadystatechange = StateChangeHandler;
XmlHttpObj.open("GET", requestUrl, true);
XmlHttpObj.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
XmlHttpObj.send(null);
}
}

// this function called when state of XmlHttpObj changes
// we're interested in the state that indicates data has been
// received from the server
function StateChangeHandler()
{
var returnValue = {};

// state ==4 indicates receiving response data from server is completed
if(XmlHttpObj.readyState == 4)
{
// To make sure valid response is received from the server, 200 means response received is OK
if(XmlHttpObj.status == 200)
{
returnValue = eval( XmlHttpObj.responseText );
PopulateList(returnValue);
}
else
{ alert("problem retrieving data from the server, status code: " + XmlHttpObj.status); }
}
}

// populate the contents of the dropdown list
function PopulateList(xNode)
{
alert(xNode);
var idValue;
var textValue;
var optionItem;
alert("The magic number is: ");
alert(xNode.Records[0].get());
alert("should have seen RC");

for (var count = 1; count <= xNode.RecordCount; count++){
document.getElementById(getDD()).options[count] = null;
}

alert(xNode.Records.length);

for (var i = 0; i < xNode.RecordCount; i++) {
var oOption = document.createElement("OPTION");
oOption.text=xNode.Records[i].Name;
oOption.value=xNode.Records[i].emp_id;
document.getElementById(getDD()).add(oOption)
}
}
</script>


and finally here is the asp file which takes care of providing the data through JSON:


<%
dim retrieveConn
dim sp
dim menu
function RStoJSON(rs)
dim sFld
dim sFlds
dim sRec
dim sRecs
dim sRecordSet
dim lRecCnt

sRecordSet = ""
sRecs = ""
lRecCnt = 0
if rs.EOF or rs.BOF then
RStoJSON = "null"
else
do while not rs.EOF and not rs.BOF
lRecCnt = lRecCnt + 1
sFlds = ""
for each fld in rs.Fields
sFld = """" & fld.Name & """:""" & toUnicode(fld.Value&"") & """"
sFlds = sFlds & iif(sFlds <> "", ",", "") & sFld
next 'fld
sRec = "{" & sFlds & "}"
sRecs = sRecs & iif(sRecs <> "", "," & vbCrLf, "") & sRec
rs.MoveNext
loop
sRecordSet = "( {""Records"": [" & vbCrLf & sRecs & vbCrLf & "], "
sRecordSet = sRecordSet & """RecordCount"":""" & lRecCnt & """ } )"
RStoJSON = sRecordSet
end if
end function

function toUnicode(str)
dim x
dim uStr
dim uChr
dim uChrCode
uStr = ""
for x = 1 to len(str)
uChr = mid(str,x,1)
uChrCode = asc(uChr)
if uChrCode = 8 then ' backspace
uChr = "\b"
elseif uChrCode = 9 then ' tab
uChr = "\t"
elseif uChrCode = 10 then ' line feed
uChr = "\n"
elseif uChrCode = 12 then ' formfeed
uChr = "\f"
elseif uChrCode = 13 then ' carriage return
uChr = "\r"
elseif uChrCode = 34 then ' quote
uChr = "\"""
elseif uChrCode = 39 then ' apostrophe
uChr = "\'"
elseif uChrCode = 92 then ' backslash
uChr = "\\"
elseif uChrCode < 32 or uChrCode > 127 then ' non-ascii characters
uChr = "\u" & right("0000" & CStr(uChrCode),4)
end if
uStr = uStr & uChr
next
toUnicode = uStr
end function

function iif(cond,tv,fv)
if cond then
iif = tv
else
iif = fv
end if
end function

set retrieveConn = new connectionManager
sp = "" & request("filter") & ""
'response.write(sp)

retrieveConn.storedprocedure = sp
retrieveConn.connect

if not retrieveConn.hasErrorOccured then
'dim rs
set rs = retrieveConn.recordset

response.write RStoJSON(rs)
response.flush

set rs = nothing

else
response.write("<div class='error'>")
response.write(retrieveConn.messageString)
response.write("</div>")
end if

set retrieveConn = nothing
%>

If anyone has an idea on how I can improve the code (specially the part about dynamically providing the next dropdown to be filled, it would be more then welcome!

Thanks and hope this helps! :)