Generate JSON from VBScript (ASP) datatypes

When working with JSON it’s nice to have a generator for your language which transforms all the datatypes from your chosen programming language into the JSON grammar so that you can use them within javascript. For a lot of popular languages it’s done already (i suppose) but I haven’t found one for classic ASP .. so here it comes. The following example quickly demonstrates what the goal is:

[asp]
set RS = getRecordset(“SELECT * FROM table”)
response.write((new JSON).toJSON(“rows”, RS))
[/asp]

A simple usage of JSON normally is that you create a page which outputs data as JSON as the only response. This page is called later from another page and the returned data is used within a javascript function (known as callback). So the snippet above gets some data from the database and stores it in an adodb.recordset which is passed then to the JSON generator and the result is printed on the page. The consuming page would like to access the data now as it originally was within the recordset. like this:

[javascript]
function callback(rows) {
for (i = 0; i < rows.length; i++) {
alert(rows[i].columName);
}
}
[/javascript]

read on to get the details…

If you are already impressed by the short example then you will save a lot of time from now on when working with JSON. I have written a class which handles the whole conversation of classic ASP datatypes to JSON grammar. In particular an ASP boolean is recognized as a boolean within javascript, an integer as a number, an array as an array, a recordset as collection, etc. I will come back to the first example later but first another example:

[asp]

alert(.foo[0]);

[/asp]

This short snippet displays an alert with true. As you can see we passed an ASP variable which has been recognized later by javascript. In this very example we even pass an array with 3 different datatypes (boolean, int and string). Those are all accessible within javascript.

Even nesting is fully supported. So array within arrays, dictionaries within arrays and vice versa. Mixing is allowed in every way you imagine. This example demonstrates what i am talking about:

[asp]

alert(.foo[0].de[1]);

[/asp]

We’ve created a dictionary (which consists of two value pairs – one holds just a string (sausage) and the other an array (egal, wurst)) and we’ve added this into another array which is the value of “foo”. After toJSON has generated the JSON string we can access the whole structure and the alertbox says “wurst”.

Now back to the example of the introduction. We can even pass whole recordsets to the generator which will generate a datastructure as followed:

JSON representation for ADODB.recordset

a recordset with two columns ID and LASTNAME will be converted into a javascript array where each field represents a row in the field and the row provides properties which are named the same as the columns within the recordset. That means that iterating through data within javascript is not a mission anymore … look at this example:

[asp]

alert(.data[0].lastname);

[/asp]

We transfered the whole adodb.recordset from ASP to javascript using JSON. How cool is that!?

Custom classes

If you create your own classes within VBScript then you might like the automatic conversion of your objects into a JSON representation. As VBScript does not support introspection (reflection) it is necessary to built an own work around. If you want the JSON class to recognize your custom objects it is necessary to implement a reflect() method within the custom type. reflect() must return a dictionary with all properties where each key is the property name and the value is its value. Values can be all kind of types (because its resolved recursively anyway). The following example shows a “Person” class which implements reflect() and therefore can be used within the JSON generator:

[asp]
class Person
public firstname ”[string] firstname
public lastname ”[string] lastname
public favNumbers ”[array] persons favorite numbers

public function reflect()
set reflect = server.createObject(“scripting.dictionary”)
with reflect
.add “firstname”, firstname
.add “lastname”, lastname
.add “favNumbers”, favNumbers
end with
end function
end class
[/asp]

The following example access the properties of the VBScript Person class within JavaScript (as it would be a JavaScript object).

[asp]

alert(.p.favNumbers[0]);

[/asp]

Update 10.07.2008: as the toJSON() method has been defined as default method it can be used much quicker by leaving the methodname out:

[asp]

alert(.root);

[/asp]

Those examples are really straight forward and should just demonstrate how to use the generator. Normally you don’t really deal with that as a client, you’d rather have some nice classes which do all this stuff for you. In another article I will demonstrate how to combine ASP, JSON and AJAX in a nice and effective way…

The download of the class is at the bottom. Here is a list of the features..

  • Transforms various datatypes from ASP to Javascript using JSON
  • Escapes all characters which needs to be escaped according to JSON’s RFC
  • Recursively inspects the values so nesting of values is supported
  • Results can be returned or (for better performance) written directly to the response

Have fun with it. There are other article related to this topic which might be interested for you:

Download latest JSON ASP utility class:
JSON 1.5.1 (JSON documentation)

License:
Use it for whatever you want but be sure to leave the credits to the creator(s) within the code. If you don’t change it you can get always the latest update here 😉

Change Log:
v1.5.1 (15.07.2008)

  • bugfix: didnt work properly with empty dictionaries. fixed!

v1.5 (10.07.2008)

  • bugfix: utf-8 fully supported now (e.g. chinese didnt work)
  • bugfix: empty dictionaries werent represented. now represented as ‘null’
  • toJSON() is defined as default method
  • paged recordsets supported
  • asp request object, IStringList and IRequestDictionary supported
  • updated documentation
  • detailed information (examples, …) about the changes

v1.4.1 (19.12.2007)

  • minor bugfix immediately after the 1.4 release

v1.4 (18.12.2007)

  • there used to be an error when returning custom objects which were processed recursively. it resulted in outputing only ‘}’. this has been fixed.
  • vartype 19 was not recognized as integer, but is now (comes from mysql)
  • vartype 7 was not recognized as float, but is now

v1.3 (21.08.2007)

  • Option Explicit can be used now
  • Multidimensional arrays are supported (unlimited dimensions)

v1.2 (19.07.2007)

  • Nested dictionaries were are not generated with toResponse = false. Fixed
  • Currency and Byte datatype were not generated correctly. Fixed

v1.1 (17.05.2007)

  • Bug with Floats has been fixed
Google Groups
asp-ajaxed
JSON is part of ajaxed. So you can check the ajaxed discussion group for more discussion about it.

193 thoughts on “Generate JSON from VBScript (ASP) datatypes

  1. I just wanted to leave a note as I used your JSON class and made a small improvement to it. I use my own custom array and associative array classes to make working with arrays and hashes easier in ASP. Adding a reflect method to the associative array class was easy as it simply returned the internal dictionary. The array was harder since your generateObject method expects an object and would not work if reflect returned a simple type. I’ve made a small modification so that a reflect method could return both objects and native types. For example somebody could have some sort of math object and simply return an integer on reflect. Please note that this code works for me but has not been extensively tested:

    private sub generateObject(val)
    dim props
    on error resume next
    set props = val.reflect()
    if err 0 then
    err.clear
    props = val.reflect()
    end if
    if err = 0 then
    innerCall = innerCall + 1
    toJSON empty, props, true
    innerCall = innerCall – 1
    else
    write(“””” & escape(typename(val)) & “”””)
    end if
    err.clear
    on error goto 0
    end sub

    Like

  2. I like the concept of using Json with Classic ASP. I’m complete lost without a full ASP example of the use of this class. I have no reference on how to apply this library class.

    Like

  3. Hi Michael,

    We are stuck up with ASP on the front end, where as the app layer is being built using WCF. So i was wondering if we can return the data from WCF in JSON format and then convert that data into custom objects on the server using JScript / ASP etc?

    Can you give some ideas please?

    Thanks

    Like

  4. i noticed that your examples in this post do not include the 3rd parameter (“nested”) in the call to toJSON. it threw me off for a couple seconds when testing this library out.

    thanks for providing the code!

    Like

  5. For anyone interested in decoding the JSON into an ASP object, I would suggest trying the jscript eval function. This has worked for me. Unfortunately, it only works in jscript ASP and most of my code is in vbscript.

    Like

  6. Dan – I call JSON.parse() from the standard json.js from VBscript ASP all the time. The only caveat is that the arrays it creates are Javascript arrays – VB sees them as objects with a .length property plus a bunch of numeric properties. Since there is no way to express accessing an numeric property in VB, you have to use a Jscript wrapper function:

    <script type=”text/javascript” runat=”server>
    function arrayValue(a, index) {
    return a[index];
    }
    </script>

    It also means you can’t use vartype or isarray on the array to see if it is an array – you get type 8 (string) from vartype.

    Like

  7. Dan – I call JSON.parse() from the standard json.js from VBscript ASP all the time. The only caveat is that the arrays it creates are Javascript arrays – VB sees them as objects with a .length property plus a bunch of numeric properties. Since there is no way to express accessing an numeric property in VB, you have to use a Jscript wrapper function:

    [asp]
    <script type=”text/javascript” runat=”server>
    function arrayValue(a, index) {
    return a[index];
    }
    [/asp]
    </script>

    It also means you can’t use vartype or isarray on the array to see if it is an array – you get type 8 (string) from vartype.

    Like

  8. Mike – Yeah, I just ran in to that the other day as I just recently started combining VBScript and JScript in my ASP pages. Makes me wish that I had just been doing everything in JScript from the start. It’s nice to be able to iterate through all of the properties of an object with

    for (var prop in object){
    Response.write(prop + ‘=’ + object[prop]);
    }

    I guess you could write a Javascript function to help you out with that too like

    function iterateObject(obj, fn) {
    for (var prop in obj) {
    fn(prop, obj[prop];
    }
    }

    function callback(property, value)
    response.write property & “=” & value;
    end function

    iterateObject(someObject, getRef(“callBack”))

    Not sure if that would work or not. All I know is it would be a lot prettier in javascript with the anonymous functions and all. like jQuery’s .each(function () {});

    Like

  9. i search json parser for asp,but i find nothing
    but i find a easy way to parse json string to Asp Object.it’s very easy.
    i find the topic from here:http://topic.csdn.net/u/20090506/16/909694ae-aa40-45fb-96cb-dd3d67959633.html
    use the server side javascript parser and create a object and return it to your asp code.
    [javascript]

    function parseToJson(json_data)
    {
    eval(“var o=” + json_data);
    return o;
    }

    [/javascript]

    [asp]
    if sel”” then
    set json_obj=parseToJson(sel)
    response.write Json_obj.name
    end if
    [/asp]

    you can try it.

    Like

  10. but we cann’t access the item in the jscript array directly,so we need other way to use the json object in asp vbscript
    the following is my code:

    [asp]
    if sel”” then
    set json_obj=parseToJson(sel)
    json_len=json_obj.length
    while json_obj.length>0
    if json_len>json_obj.length then ids=ids&”,”
    set Json_item=json_obj.pop()
    ids=ids&json_item.id
    wend
    response.write ids
    end if

    [/asp]

    Like

  11. I have a ajax reponse that I would like to insert into a db, the response is:

    [asp]
    {“forename”:[“Forname1″,”forname2″],”surname”:[“Surname2″,”surname2″],”dateOfBirth”:[“04/07/1970″,”05
    /27/2009”]}
    [/asp]

    where forename, surname, dateOfBirth are all the fields in the DB, how can process this information using the insert command?

    I use the JSON to retrive information but not insert anything so any help is appreicated.
    [asp]
    INSERT INTO dbo.login_tbl (forename, surname, dateOfBirth) VALUES (value1, value2, value3)
    [/asp]

    for each in the array?

    Like

  12. Hi,

    When outputting as:

    sqlquery = “Select company, username, useremail, tel, password FROM tbl_login where id = 93”

    sOutput = (new JSON).toJSON(“data”, getRecordset(sqlquery), false)

    I need the output to read:

    “{‘success’:true,’data’:{‘company’:’John’,’username’:’Doe’,’useremail’:’john.doe@example.com’}}”

    At present there is no ‘success’:true in front and there is always an [ ]

    How could add the sucess and omit the [] which I think is an array?

    Like

    1. @sanj you can’t easily remove the [] as thats the way how the recordset is parsed. It’s an array with “objects’ inside. The success can be appended by e.g. using a dictionary:
      [asp]
      set dict = server.createobject(“scripting.dictionary”)
      dict.add “success”, true
      dict.add “data”, getRecordset(sqlquery)
      sOutput = (new JSON).toJSON(“root”, dict, false)
      [/asp]

      Like

  13. Thanks Michal, is this still the best ASP Json.

    The reason I need the [] removed is becasue of EXTjs loading into aform via a JSON response. I’ve got around this be manually removing it.

    Sanj

    Like

  14. Hi,

    is it possible to add more than one sql,

    say I have:

    sqlquery = “SELECT PORev1Name, PORev1Status, PORev1Comments, PORev1DateRev FROM dbo.tbl_uob_main WHERE id = ?”

    dim sOutput
    sOutput = (new JSON).toJSON(“data”, getRecordset(sqlquery), false)

    how could I do the following?

    sqlquerya = “SELECT PORev1Name, PORev1Status, PORev1Comments, PORev1DateRev FROM dbo.tbl_uob_main WHERE”

    sqlqueryb = “SELECT PORev2Name, PORev2Status, PORev2Comments, PORev2DateRev FROM dbo.tbl_uob_main”

    sOutput = (new JSON).toJSON(“data”, getRecordset(sqlquerya) & getRecordset(sqlqueryb), false)

    Like

    1. @sanj: you have two options .. you can either add both recordsets into an array or into a dictionary. Then transform the dictionary/array with toJSON().

      Like

  15. Michal,

    Thanks for your reply, could you please give me an example to convert into an array,

    is the correct way for a dictionary:

    dict.add "success", true
    dict.add getRecordset(sqlqueryA)
    dict.add getRecordset(sqlqueryB)
    sOutput = (new JSON).toJSON("root", dict, false)

    Like

  16. @Michal Do you have any advice for handling SQL DateTime types from a result set? My dates are coming back like: ‘6u002F1u002F2009’ and I need to convert them to a JavaScript date.

    Thanks 🙂

    Like

  17. Can you please explain how to get rid of that [] at the beginning of the RecordSet JSON? It’s screwing things up for me royally.

    Thanks!

    Like

  18. I like your program very much and use it a lot. I still have one problem: dateformats.
    Our MSSQL server is English, our webpages are Dutch. Json returns the MDY format of the server, but is there a way to switch it around to DMY?

    Thanks (smile)

    Like

      1. I had to translate json.org’s json_parse.js into VBScript and integrate it into jsCore.

        It seems to work but I am still testing. Is this something I could submit back to you for your inclusion in jsCore?

        Like

  19. Two things:

    – I found two ASP libraries and couldn’t decide if either had stronger attributes. Both were very cleanly written and I didn’t choose JSON.asp for any reason I can point to. There seems to be more forum activity here, so perhaps I made the wrong choice.

    If you would like to see the library I’ve got which is fairly well tested now and offers full-duplex JSON on the server-side, let me know how to get it to you or otherwise post the library.

    Sam

    Like

    1. Could you send me the JSON parser you translated? I am fairly new to ASP and am having one tough time trying to translate a javascript parser into vbscript! I would really appreciate it.

      Sean

      Like

  20. I could not get the first example to work until I added a false parameter to the end of the call:

    response.write ((new JSON).toJSON(“rows”, rs, false))

    Until then, I was getting a “Wrong Number of arguments” error.

    Like

  21. Thanks dear Michal for the clas.

    could you please tell me how return a very simple result in asp :
    {{“server”: “good”,”message”: “Done”}

    I need to response.Write theresult,
    I dont want to use js.

    thanks

    Like

  22. Pingback: pligg.com

Leave a Reply to Slimboost Forskolin Review Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: