SearchThis 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.
