Does your project need rescued? Nearly 74% of projects do. Click here to view our Guide to get your project on the right path!

Arrays and Recordsets in Classic ASP

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

Leave a Reply

Your email address will not be published. Required fields are marked *