Using AJAX to Populate a Select Control

This is the final installment of using AJAX and JSON in Classic ASP. In this article we use JSON to populate a select control based on the selection in another.

In the first article we learned about arrays and recordsets in Classic ASP and the code base I would use to pull data from a database that would eventually be made available in JSON. In that post, we displayed the results in a table.

In the second article we learned how to generate a JSON object based on recordset and field data and to display that JSON as output.

In this final article, we will learn how to consume that JSON object and repopulate a control – in this case, a select control—without reposting the page.

If you have a question about how to read or write JSON, specifically how to determine when curly braces or hard brackets are appropriate, you might want to read my primer.

First, let’s take a look at the SQL scripts to create our two tables, one of which contains information specific to certain US States and the second which contains a list of cities and which state they are in.

/*
Note this removes the table if it exists but assumes any object named tmpSprocDemoConCat is a table.
*/

if object_id(N'dbo.tmpTableDemo') is not null drop table dbo.[tmpTableDemo]

go

create table dbo.[tmpTableDemo]
(
	city nvarchar(100)
,	[state] nvarchar(2)
);

go

insert into dbo.[tmpTableDemo] values
('New York','NY')
,('Buffalo','NY')
,('Albany','NY')
,('Los Angeles','CA')
,('Santa Ana','CA')
,('Chico','CA')
,('Indianapolis','IN')
,('South Bend','IN')
,('Portland','ME')
,('Bar Harbor','ME');

go

select * from dbo.[tmpTableDemo];

go

/*
Note this removes the table if it exists but assumes any object named tmpSprocDemoConCat is a table.
*/

if object_id(N'dbo.tmpTableStateDemo') is not null drop table dbo.[tmpTableStateDemo]

go

create table dbo.[tmpTableStateDemo]
(
	stateAbbv nvarchar(2)
,	[stateName] nvarchar(100)
,	[stateCapital] nvarchar(100)
,	[stateLargestCity] nvarchar(100)
,	[statehoodYear] int
,	[statehoodRank] int
);

go

insert into dbo.[tmpTableStateDemo] values
('NY', 'New York','Albany','New York City',1788,11)
,('IN', 'Indiana','Indianapolis','Indianapolis',1816,19)
,('CA', 'California','Sacramento','Los Angeles',1850,31)
,('ME', 'Maine','Augusta','Portland',1821,23);

go

select * from dbo.[tmpTableStateDemo];

Table tmpTableStateDemo contains the state-only data and will be used to populate the first select control. Based on the selection made by the user, tmpTableDemo will contain the data returned in JSON used to populate the second select control.

Below is the Classic ASP in VBScript for this page. Note that on line 183, you will have to put in the proper connection string to connect to your SQL Server database.

<%@ Language=VBScript %>
<%
Server.ScriptTimeout = 720
Response.CacheControl = "no-cache" 
ContentType
Response.Expires = -1 

Sub ContentType
	if lcase(FunctionName) = "getstate" then
		Response.ContentType = "application/json"
	else
		Response.ContentType = "text/html"
		'Response.ContentType = "text/plain"
	end if
end sub

function FunctionName
	FunctionName = request.querystring("funcName").item
end function

sub GetJQuery
	response.write "<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js'></script>" & vbCtLf
end sub

function JSON_TABLE_OUT(strObjectName,arrFieldNames,arrFieldIsString,arrValues, indentAdd, rowDataOnOneLine)
	dim strOut, extraIndents
	dim fieldCount, innerCount, recordCount
	extraIndents = ExtraTabs(indentAdd)
	strOut = ""
	strOut = strOut & extraIndents & "{" & vbCrLf
	strOut = strOut & extraIndents & vbTab & chr(34) & strObjectName & chr(34) & ":"
	if isarray(arrValues) then
		strOut = strOut & vbCrLf & extraIndents & vbTab & "[" & vbCrLf
		for recordCount = 0 to ubound(arrValues,2)
			strOut = strOut & extraIndents & vbTab & vbTab & "{"
			if not rowDataOnOneLine then strOut = strOut & vbCrLf
			for fieldCount = 0 to ubound(arrFieldNames)
				if rowDataOnOneLine then
					strOut = strOut & """" & arrFieldNames(fieldCount) & """:"
				else
					strOut = strOut & extraIndents & vbTab & vbTab & vbTab & """" & arrFieldNames(fieldCount) & """:"
				end if
				if isarray(arrValues(fieldCount,recordCount)) then
					strOut = strOut & "["
					for innerCount = 0 to ubound(arrValues(fieldCount,recordCount))
						if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
						strOut = strOut & arrValues(fieldCount,recordCount)(innerCount) & ""
						if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
						if innerCount < ubound(arrValues(fieldCount,recordCount)) then strOut = strOut & ","
					next
					strOut = strOut & "]"
				else
					if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
					strOut = strOut & replace(arrValues(fieldCount,recordCount) & "",chr(34),"")
					if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
				end if
				if rowDataOnOneLine then
					if fieldCount < ubound(arrFieldNames) then strOut = strOut & ","
				else
					if fieldCount < ubound(arrFieldNames) then strOut = strOut & "," & vbCrLf
				end if
				
			next
			if rowDataOnOneLine then
				strOut = strOut & "}"
			else
				strOut = strOut & vbCrLf & extraIndents & vbTab & vbTab & "}"
			end if
			if recordCount < ubound(arrValues,2) then strOut = strOut & ","
			strOut = strOut & vbCrLf
		next
		strOut = strOut & extraIndents & vbTab & "]" & vbCrLf
	else
		strOut = strOut & "{}" & vbCrLf
	end if
	strOut = strOut & extraIndents & "}" & vbCrLf
	JSON_TABLE_OUT = strOut
end function

function ExtraTabs(numberOfIndents)
	dim strOut
	if numberOfIndents > 0 then
		strOut = space(numberOfIndents)
	else
		strOut = ""
	end if
	ExtraTabs = replace(strOut," ",vbTab)
end function

Function SqlErrorFound(dbConn,byval sourceError)
    Dim errCounter
    Dim errOut: errOut = False
    For errCounter = 0 To dbConn.Errors.Count - 1
        If dbConn.Errors(errCounter).Number <> 0 Then
            response.Write replace(sourceError,vbCrLf,"<br />",1,-1,1) & ": " & dbConn.Errors(errCounter).Description
            errOut = True
            Exit For
        End If
    Next
    SqlErrorFound = errOut
End Function

sub AllRecordsWithTitles(dbCon,strSql,byref allRecords,byref allTitles)
	dim rs
	dim adUseClient,adOpenStatic
	'on error resume next
	adUseClient = 3
	adOpenStatic = 2
	allRecords = empty
	allTitles = empty
	set rs = CreateObject("ADODB.recordset")
	rs.CursorLocation=adUseClient
	rs.CursorType=adOpenStatic
	rs.Open strSql,dbCon
	if err.number = 0 then
		do while rs.state=0 and err.number=0 and not sqlErrorFound(dbCon,strSql)
			set rs=rs.nextrecordset	
		loop
		if not rs.eof and err.number=0 then
			allRecords = rs.getrows
			for recordCounter = 0 to rs.fields.count-1
				redim preserve titles(recordCounter)
				titles(recordCounter) = rs.fields(recordCounter).name
			next
		elseif err.number = 0 then
			for recordCounter = 0 to rs.fields.count-1
				redim preserve titles(recordCounter)
				titles(recordCounter) = rs.fields(recordCounter).name
			next
		end if
	else
		response.write "Error pulling recordset System error: " & vbCrLf & err.Description & vbCrLf
		response.write "Error pulling recordset SQL statement: " & vbCrLf & strSql
	end if
	err.Clear
	rs.close
	set rs = nothing
	allTitles = titles
end sub

function SqlSafeString(byval strSql)
	strSql = replace(strSql,"'","''",1,-1,1)
	strSql = replace(strSql,vbCrLf, "" ,1,-1,1)
	strSql = replace(strSql,vbCr, "" ,1,-1,1)
	strSql = replace(strSql,vbCr, "" ,1,-1,1)
	SqlSafeString = strSql
end function

function StateName
	StateName = SqlSafeString(request.querystring("State").item)
end function

function MakeAllFieldsText(arrFields)
	dim arrOut(), i
	redim arrOut(ubound(arrFields))
	for i = 0 to ubound(arrOut)
		arrOut(i) = true ' flag as text field
	next
	MakeAllFieldsText = arrOut
end function

Private sub JSONStateInfo(dbCon)
	dim strSql, allRecords, arrFields, arrIsText
	strSql = _
		"declare @state nvarchar(2);" & vbCrLf & _
		"set @state = '" & StateName & "';" & vbCrLf & _
		"select *" & vbCrLf & _
		"from dbo.[tmpTableDemo]" & vbCrLf & _
		"where case @state when '' then [State] else @state end = [State]"
	AllRecordsWithTitles dbCon,strSql,allRecords,arrFields
	arrIsText = MakeAllFieldsText(arrFields)
	response.write JSON_TABLE_OUT("CityAndStates",arrFields,arrIsText,allRecords, 0, true)
End sub

private sub StateInfo(dbCon, byref allRecords, byref allFields)
	dim strSql
	strSql = "select [stateAbbv], [stateName] from dbo.[tmpTableStateDemo] order by 1;"
	AllRecordsWithTitles dbCon,strSql,allRecords,arrFields
end sub

Sub OpenDatabase (byref pConn)	
	dim connStr
	connStr = "Provider=SQLOLEDB.1;Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUserId;Password=YourPassword"
	Set pConn = Server.CreateObject("ADODB.Connection")
	pConn.ConnectionTimeout = 120
	pConn.CommandTimeout = 120
	
	pConn.Open connStr	
End Sub

Sub CloseDatabase (byref pConn)
	if pConn.State = 1 then  '-- if open
		pConn.close
	end if	
	Set pConn = Nothing
End Sub

function postTo()
	dim urlOnly,slashItems
	urlOnly=split(Request.ServerVariables("HTTP_URL"),"?")(0)
	slashItems = split(urlOnly,"/")
	postTo = slashItems(ubound(slashItems))
end function

sub DrawJavaScript
%>
<script language='javascript'>
$(document).ready
(
	function()
	{
		LoadCities();
		$("#States").on('change', function(){LoadCities();});
	}
);

function LoadCities()
{
	var stateName = $("#States").val(); var dataType = 'json';
	$.get
	(
		"<%=postTo%>",
		{
			funcName: 'getstate',
			state: stateName
		}
	,	function(data, status)
		{
			var allCityStates = data.CityAndStates
			$("#ListOfTowns").empty();
			$("#ListOfTowns").attr('size',1);
			for (var x=0;x<allCityStates.length;x++)
			{
				$("#ListOfTowns").append
				(
					$('<option>'
				,
						{
							value:allCityStates[x].city,
							text:allCityStates[x].city,
						}
					)
				);
			}
			$("#ListOfTowns").attr('size', $('#ListOfTowns > option').length);
		}
	,	dataType
	)
}
</script>
<%
end sub

sub DrawStateDropDown(dbCon)
	dim allRecords, allFields, counterState
	StateInfo dbCon, allRecords, allFields
	response.write "<option value=''>All</option>" & vbCrLf
	if isarray(allRecords) then
		for counterState = 0 to ubound(allRecords,2)
			response.write "<option value='" & allRecords(0,counterState) & "' />" & allRecords(1,counterState) & "</option>" & vbCrLf
		next
	end if
end sub

sub DrawDropDowns(dbCon)
%>
<html>
<head>
<title>Test Drop Down Ajax in ASP</title>
<%
	GetJQuery
	DrawJavaScript
%>
</head>
<body>
<div id="div1">Select Your State:</div>
<form name='Form1' id = 'Form1'>
<div id='div2'>
<select name='States' id='States'>
<%DrawStateDropDown dbCon%>
</select>
</div>
<div id="div3">Towns Populated In Drop Down:</div>
<select id="ListOfTowns" name="ListOfTowns">
<option value='Test'>None Selected</option>
</select>
<div id='div4>
</div>
</form>
</body>
</html>
<%
end sub

OpenDatabase dbCon

if lcase(FunctionName) = "getstate" then
	JSONStateInfo dbCon
else
	DrawDropDowns dbCon
end if

CloseDatabase dbcon
%>

I named my file Dummy2.asp and will assume your file is located at http://localhost/dummy2.asp, so remember to consider where your file really is when I give URLS as examples.

Let’s take a look at some new additions in the Classic ASP code compared to last time.

  • Line 5 calls for a subroutine ContentType instead of just selecting one. This is because this page will serve both HTML and JSON, depending on how it is called. ContentType begins on line 8 and looks for another function called FunctionName (on line 17), which looks for the query variable “funcName”. If it contains a value, the assumption is an AJAX call is being made and that JSON is the required form of output.
     
  • Line 21 is a subroutine used to make JQUERY available.
     
  • Line 198 contains a curious function called postTo, which parses the URL to the current page without any query variables. This function eliminates the need to hard-code the location of this page in code, so if you were to rename or relocate the page no change would be required to make it self-addressing. This is important whenever a page either posts back to itself or makes an AJAX call to itself like this one does.
     
  • Line 205 is used to render the JavaScript that makes this page work. We’ll get back to it as it requires a deeper discussion.
     
  • Line 254 is used to render on the server side the option elements within the select control for US States.
     
  • Line 265 draws the HTML for the page, including the form and two drop downs, one for US States and one for cities to be displayed within those states. Note that no towns are initially displayed by the server sided code.
     
  • Lines 297 through 301 are not inside any subroutine and run as the page is called. The if-then statement here looks for the query variable “funcName” to see if its value is “getstate”. If so, the assumption is that an AJAX call has been made and JSON will be returned. Otherwise HTML is rendered.

If you were to call this page, you’ll notice that the cities populate even though the server sided code from lines 284 to 286 does no such thing. Something else must be happening. One thing I want you to notice are the IDs of the two drop downs, “States” and “ListOfTowns” on lines 279 and 284 respectively. This will be important.

Let’s see how the cities are being populated since the select control “ListOfTowns” is not being populated by server-sided VBScript. Back to the DrawJavaScript routine from lines 208 to 215. Line 208 is JQuery syntax for: This code must run once the web page has been fully rendered”, meaning all the server sided code has run and all HTML has been rendered. At line 210, an anonymous function (a function with no name) does two things: It calls the JavaScript function LoadCities on line 212 and on line 213 it associates the onChange event for the control with States as an ID to the function LoadCities, all using the syntax of JQuery.

Remember, JQuery is referencing the control “States” with a nonagram “#” using the same selection syntax as CSS. If you are familiar with CSS, JQuery is much easier.

The JavaScript LoadCities is where the rubber meets the road. It is this function that actually populates the ListOfTowns select control. Let’s go through this line by line.

  • Line 219, var stateName = $(“#States”).val(); is JQuery syntax for getting the selected value out of an select control. This will be needed because we need to filter by US State, given to use by the State select control.
     
  • Line 219 also has the command var dataType = ‘json’; This will be used later to specify in our AJAX call that we want to specify that JSON is the return type we are expecting.
     
  • Line 220 has the $.get command, which is JQuery speak for posting form variables by get. This is the same thing as posting a form by querystring in Classic ASP. If you wanted to pass variables by post, you would use the $.post command, instead.

Notice the $.get command has four arguments, the first of which is mandatory. The first argument is on line 222 with a comma separating it from the second argument. The first argument is a url in which we’ve placed the server sided function “postTo”. PostTo allows us to get away without having to hardcode the url to the same page. This page will send a get request back to itself.

The second argument from lines 223 to 226 are a set of two key value pairs, just like in JSON. JSON comes from how arrays are displayed in JavaScript so this makes perfect sense. This set of key value pairs are the form variables to be sent to the called page in the first argument.

So already, based on these first two arguments and the method call itself, we know we’re calling our own page by get with two form variables named funcName and state, each with a value. The value for funcName is ‘getState’ while the value for state is the variable stateName, which was populated on line 219 with the selected option value from the States select control.

The third argument in the $.get command is an anonymous function call with two arguments: “data” and “status” and an optional third argument we are not using . This function will run after the call has finished. “data” and “status” are two variables passed by reference, so you’ll be able to look at them within your function.

“status” contains the status of the request: “success”, “notmodified”, “error”, “timeout”, or “parsererror” and you’ll want to look at it to see if your request was successful. However, I’m not taking advantage of it here.

“data” is the JSON returned from the page called on line 22 as an object. Not to get ahead of ourselves, the fourth argument on line 247 specifies that our return type is JSON.

Let’s take a look at that JSON for a moment. In this case, the JSON is returning all cities if no state is selected:

{
	"CityAndStates":
	[
		{"city":"New York","state":"NY"},
		{"city":"Buffalo","state":"NY"},
		{"city":"Albany","state":"NY"},
		{"city":"Los Angeles","state":"CA"},
		{"city":"Santa Ana","state":"CA"},
		{"city":"Chico","state":"CA"},
		{"city":"Indianapolis","state":"IN"},
		{"city":"South Bend","state":"IN"},
		{"city":"Portland","state":"ME"},
		{"city":"Bar Harbor","state":"ME"}
	]
}

You can see this for yourself if you go to http://localhost/dummy2.asp?funcName=getstate (remember to change this URL to match the file name and location you used).

This is an object where they key “CityAndStates” has a value that is an array of key-value pairs where the key is “city”.

How do we access this as an object in JSON? Again, remember JSON is native JavaScript syntax. “data”, received as JSON, is an object with one attribute, CityAndStates. So line 229, names a variable “allCityStates” and sets its value to that attribute in the JSON. The value is the array of “city” keys.

Line 232 takes advantage of this by setting up the for next loop using the length of this array as the exit point. Lines 239 and 240 show how the key-value elements in the array can be retrieved to populate an option element.

So now you know some JavaScript syntax to access a JSON object. All that is left to do is determine how that select control is actually populated.

Lines 230 and 231 empty the select control and set its size to 1. The size will be reset later.

Line 234 uses JQuery syntax to append HTML to the LIstOfTowns control. The append command in JQuery takes 2 arguments, the first being the HTML tag and the second a key-value list of attributes.

JQuery is smart enough to take the “text” attribute and place its value between the opening and closing option tags instead of treating it as an actual attribute. JQuery is a wonderful thing.

Finally, line 245 resizes the select control to the number of option elements in the control. Note the CSS hierarchical syntax ‘#ListOfTowns > option’ here to specify what you want the length of.

Hopefully you now understand how to receive a JSON object, access data within, and even populate a select control.

Leave a Reply

Your email address will not be published.