SearchClassic ASP would appear to be a relic of the bygone past but there are still some of us out there who support websites built on this technology. For those three or four of you left, I thought I would share some AJAX and JSON functionality. But before I get into that, I want to familiarize you with some of the tools I will be using.
This post will be about pulling recordsets to be output as an HTML table, leaving it to a future post how to output JSAON, instead. Instead we’ll work with how to use form variables to determine page output.
For our example we need two tables One is a list of US states and some information. The second is a list of cities and their associated state. These tables won’t be joined, we will just be showing how to return either one table or another based on form variables passed to the page. And again, there is no JSON or AJAX action going on here yet. We’re concentrating on how to pull the data and displaying it in a table as proof we are getting that data.
Here is the T-SQL code for the first table of states, to create and populate it.
/*
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];
Here is the T-SQL code for the second table of cities and states.
/*
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];
Not too much going on here, just two differing sets of data that will be called by two different functions.
Next is the ASP code. There is quite a bit to make this a fully functional page. Note that line 143 has a connection string. You will need to update this for your own server and catalog and method of connecting.
<%@ Language=VBScript %>
<%
Server.ScriptTimeout = 720
Response.CacheControl = "no-cache"
Response.ContentType = "text/html"
Response.Expires = -1
Function SqlErrorFound(dbConn,byval sourceError) ' helper function used by AllRecordsWithTitles
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)
' pulls a recordset based on a sql query in strSql. allRecords is a two dimensional array and allTitles a single dimensional array with field names
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 OutHtml(item)
OutHtml = Server.HTMLencode(item)
end function
sub TestTableForRecordsetArray(arrFields,arrValues) ' creates a table based on a recordset and field names supplied by AllRecordsWithTitles
response.Write "<p>" & vbCrLf
if isarray(arrValues) then
response.Write "Number of records: " & ubound(arrValues,2) + 1
else
response.Write "Number of records: EMPTY"
end if
response.Write "</p>" & vbCrLf
response.Write "<table border = '1'>" & vbCrLf
dim iColCounter
response.Write "<tr>" & vbCrLf
if isarray(arrFields) then
for iColCounter = 0 to ubound(arrFields)
response.Write "<th>" & outHtml(arrFields(iColCounter)) & "</th>" & vbCrLf
next
else
response.Write "<th colspan = '100%'>No fields included</th>" & vbCrLf
end if
response.Write "</tr>" & vbCrLf
dim iRowCounter
if isarray(arrValues) then
for iRowCounter = 0 to ubound(arrValues,2)
response.Write "<tr>" & vbCrLf
for iColCounter = 0 to ubound(arrValues,1)
response.Write "<th>" & outHtml(arrValues(iColCounter,iRowCounter)) & "</th>" & vbCrLf
next
response.Write "</tr>" & vbCrLf
next
else
response.Write "<tr>" & vbCrLf
response.Write "<th colspan = '100%'>No records included</th>" & vbCrLf
response.Write "</tr>" & vbCrLf
end if
response.Write "</table>" & vbCrLf
end sub
function SqlSafeString(byval strSql) ' String sanitizer
strSql = replace(strSql,"'","''",1,-1,1)
strSql = replace(strSql,vbCrLf, "" ,1,-1,1)
strSql = replace(strSql,vbCr, "" ,1,-1,1)
strSql = replace(strSql,vbLf, "" ,1,-1,1)
strSql = replace(strSql,";", "" ,1,-1,1)
SqlSafeString = strSql
end function
function StateName ' pulls form variable by query string, sanitizes and sets to lower case for comparison purposes
StateName = lcase(SqlSafeString(request.querystring("State").item))
end function
function functionName ' pulls form variable by query string, sanitizes and sets to lower case for comparison purposes
functionName = lcase(SqlSafeString(request.querystring("funcName").item))
end function
Private sub GetStateInfo(dbCon) ' SQL statement to get a single state, or if none is supplied, all states
dim strSql, allRecords, arrFields, arrIsText
strSql = _
"declare @state nvarchar(2);" & vbCrLf & _
"set @state = '" & StateName & "';" & vbCrLf & _
"select *" & vbCrLf & _
"from dbo.[tmpTableStateDemo]" & vbCrLf & _
"where case @state when '' then [StateAbbv] else @state end = [StateAbbv]"
AllRecordsWithTitles dbCon,strSql,allRecords,arrFields
TestTableForRecordsetArray arrFields,allRecords
End sub
Private sub GetCityStateInfo(dbCon) ' SQL statement to list cities within a single state, or if none is supplied, all states
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
TestTableForRecordsetArray arrFields,allRecords
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
sub OpenPage
%>
<html>
<head><title>Recordset Array Reader</title></head>
<body>
<%
end sub
sub ClosePage
response.write "<body>" & vbCrLf & "</html>"
end sub
OpenPage
OpenDatabase dbCon
select case functionName
case "cities" : GetCityStateInfo dbCon
case "states" : GetStateInfo dbCon
case else : response.write "Can't Find Function"
end select
CloseDatabase dbcon
ClosePage
%>
If you place this page on your web server and load it in your web browser, you should get “Can’t Find Function” as a result. This is because line 184 is calling a function looking for the query string variable “funcName”, which should be set to either “cities” or “states”. Assuming this is the location of the file, http://localhost/test.asp, to add that variable in the query string use this: http://localhost/test.asp?funcName=cities and you should be greeted with a table listing cities and states.
This is showing an important concept: A page may serve multiple results based on form variables you pass to it, whether by query or post. AJAX is a technology that allows JavaScript on the client side to call a web page and pass form variables by query or post and receive a result, normally in JSON, to act upon. This table of cities and states rendered in HTML is simply a proof of concept that you can call a page and receive data. Seems simple enough except this data is formatted for human eyes and would make a terrible web service to pull data from. We simply want to prove the data is available.
You can also see there’s another function written into this page besides “cities”; change the address to http://localhost/test.asp?funcName=states and you will see a list of states and associated data. Again, we’re getting different functionality from the same page albeit in an HTML format.
In addition to having two functions, each function takes a parameter allowing you to filter by state. While using the states function, let’s filter by a state name by changing the address to http://localhost/test.asp?funcName=states&state=in and you will see only Indiana’s information. The same functionality exists for the other function “cities”.
What we are showing here is that multiple form variables may be passed and acted upon by a web page to change the data served. This is very useful when creating a page meant to be called using AJAX. When you load up a page like we are doing here, we’re sending an HTTP GET but AJAX allows you to POST data, too.
Now, for a quick tour of the ASP code that makes this happen. This will be useful, as most of these routines will be used in future demonstrations of AJAX and JSON.
- Note line 5, where we specify a content type. We are specifying HTML because we are serving HTML. If we were serving up JSON to be consumed by JavaScript using AJAX, the value here would be ‘application/json’. You can have the same page either serve HTML or JSON by including a function to supply the appropriate MIIME type and we will be doing that in future examples.
- Line 8 contains the function SqlErrorFound, which iterates through any SQL errors found. It is used by the next routine on our list.
- Line 21 is AllRecordsWithTitles. Instead of looping through records and fields in an array, if the dataset being returned is fairly small, you can populate an array of values that you can then iterate through later. As arguments this subroutine takes a Server. “ADODB.Connection” object that is open, a string that contains a valid SQL statement, and two variants that are passed by reference named allRecords and allTitles internal to the subroutine. allRecords will be populated as a two-dimensional array where the first dimension are fields and the second dimension are records, so to access a particular scalar item of data in the third column of the second record: allRecords(2,1). Think of it as being backwards from Excel’s notation. The allFields array would have the same number of elements as there are fields and would contain their names. Because allRecords and allFields are passed by reference, the variables passed into this subroutine are populated with these values.
In Classic ASP, passing variables by reference to subroutines is an excellent way for a routine to return more than one set of data.
- Line 64 has the subroutine TestTableForRecordsetArray. You probably won’t be seeing this one in future demos, it’s purpose is to output a table based on arrays created by AllRecordsWithTitles and is here as a proof of concept to display data received.
- Line 100 is SqlSafeString, a simple variable sanitizer.
- Lines 109 and 113 start functions that pull query variables. They could just as easily pulled form variables or both. Line 113’s “functionName” function pulls the value for the query variable “funcName”, which is ultimately used to determine which function should be called. Line 109’s StateName pulls the query variable “State”, which is used by both functions as a parameter to filter by state.
- Lines 117 and 129 are where the rubber meets the road. Each function pulls data for states or cities within states respectively. A “CASE” statement is used in the “WHERE” clause of each that treats a blank value for state to mean every state. Note the use of SQL variables, which, when used with sanitized strings, help to reduce the likelihood of a successful attack by SQL injection.
- Line 141 has the subroutine OpenDatabase, which handles the heavy lifting of opening an ADODB connection. It takes by reference a variant which will become the variable passed around to functions and subroutines that need an open database connection.
- Line 154 is the compliment to OpenDatabase to clean up the open database connection.
- Line 161, OpenPage, starts the HTML for the page. It’s complimentary subroutine ClosePage on line 169 closes out the HTML document.
- Lines 172 and after are not encapsulated in functions and simply run when the page is served. Line 177 is key. This is a VbScript Case statement to check which function is to be called and implements the primary functionality that allows this page to server more than one function. In fact, this section can be expanded to allow a page to serve more than one kind data so long as the content type response type is also appropriately set. We’ll see that in another example.
In this post, we’ve seen how to create two sets of data (two tables) and use a Classic ASP page to serve up that data, filtered or not, based on query form variables. No JavaScript or JSON was included, this was just a proof of concept. Our next example will replace this table with JSON output, which is a much simpler output than HTML but requires us to provide our own VbScript implementation.
