This post is a follow-on to where we are introduced to some of the helper Classic ASP functions I use to retrieve and display data. The main difference between this post and the last is this one leaves the HTML table behind for JSON output, which is meant to be consumed by an AJAX client. We concentrate on delivering that JSON here.

Like the last post, we have two tables to use for source data. One has US States and some associated data and the other has cities and which state they are located in. In our last piece, we used query variables to determine which functions ran and which states were displayed. Here we will do the same thing but output JSON. Therefore, we’ll have a limited review of how the helper functions work before getting into the JSON implementation.

First, let’s go ahead and create and populate the two tables, all right in one go.

And now for the classic ASP.

Reviewing the purpose of Line 136’s “funcName”, this returns the value of the query variable “funcName”, which is used by the case statement down at line 215 to determine whether to return US State only data or city and state parings. Line 132, function “StateName”, should also be review as this is the query variable used to filter by state.

There are a few new functions, however. TabNumber on line 150 retrieves query variable “tabs” and is used by the new function “JSON_TABLE_OUT” to determine how many tabs of whitespace are needed to indent the JSON output. We’ll get into that. AllOnOneRow on line 140 is used to retrieve query variable “oneRecordOneRow”, and we’ll get into that, too.

Function MakeAllFieldsText on line 160 is used to take the field array returned by AllRecordsWithTitles (line 86) and make a new array the same size and populate it as TRUE. When true, this marks that particular field as being text. If a field is text, the value in JSON is enclosed in double quotes in our implementation. More information will be forthcoming.

ExtraTabs is a helper function to function JSON_TABLE_OUT and is used to implement the tab request from function TabNumber by inserting tabs into selected areas as required.

Subroutines JSONStateInfo and JSONCityStateInfo are simply renamed to reflect their use of JSON. In addition, instead of calling the function to output a table of data, these functions call the new function JSON_TABLE_OUT.

Also note on line 5 how our response type is fixed as application/json.

Finally, we get to our main function, JSON_TABLE_OUT, on line 8. This function is designed to process a table of information much like AllRecordsWithTitles would create and formulate white-spaced JSON output. Because JSON variables can be quoted or not, our routine takes this extra information as an array based on the field names array from our recordset. As mentioned before, function MakeAllFieldsText can take an array of field names and generate this text-or-not array of flags which can then be populated as needed. In our example, we treat all values as text.

JSON_TABLE_OUT also requires how much the full output should be indented from the left margin. This facilitates the output being integrated within a larger JSON set while matching that whitespace. Whitespace isn’t important for implementations but it certainly facilitates debugging and troubleshooting.

The last parameter accepted is a Boolean variable determining if row data should be on one line of output. We’ll play with this and see why this makes a difference in readability, but again this is only a formatting issue and does not affect how the JSON is ultimately read.

Assuming your file name is dummy3 on localhost, go to http://localhost/UFR_FL/dummy3.asp?funcName=cities and you should see something like this:

Here you will see a repeating array of key-value pairs of city->state. Because there are so few columns but more than a few records, we might choose to have all records on one line. This can be facilitated by taking advantage of the query variable “oneRecordOneRow” which is read by function AllOnOneRow. Any value on line 144 is interpreted as true and will place each record on the same row. So try the following URL: http://localhost/dummy3.asp?funcName=cities&oneRecordOneRow=true

That’s pretty convenient. If we want to tab this in two tab spaces, we can add the “tabs” query variable using this url: http://localhost/dummy3.asp?funcName=cities&oneRecordOneRow=true&tabs=2

Again, this might be useful if you are incorporating this JSON in a larger set of JSON. It has no affect on how it’s interpreted by the consuming JavaScript.

As you can see, it might be useful when viewing many records with few columns to have all records appear on one line. But what if you’re interested in one record with many columns?

Change the funcName value to “states” and add a query variable “state” with a value of “ny”; an example url: http://localhost /dummy3.asp?funcName=states&oneRecordOneRow=true&state=ny

In this case, it might be nice to have all the fields appear on their own line because there are so many. Change the “oneRecordOneRow” value to false.

This is the kind of output that is easy for JavaScript using AJAX to consume. Next time, we’ll go into using this kind of data to update a page without having to post back and refresh, using JQuery.

Like this post? Share it!