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