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.

Here is the T-SQL code for the second table of cities and states.

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.

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.

Like this post? Share it!