Output ADODB.RecordSet as JSON

The JSON2.asp implementation doesn't have a "Load From Database" function which means you will have to implement something to convert the ADODB.Recordset to a JSON structure yourself.

If you are willing to use a different script there is an implementation by RCDMK on GitHub that does have a LoadRecordset() method, it's called JSON object class 3.5.3.

This makes loading data from an ADODB.Recordset really straightforward.

<!-- #include virtual="/jsonObject.class.asp" --><%Response.LCID = 2057'...Dim rs: Set rs = conn.execute(strQuery)Dim JSON: Set JSON = New JSONobjectCall JSON.LoadRecordset(rs)Call Response.Clear()Response.ContentType = "application/json"Call JSON.Write()%>

^{Code has been tested using a disconnected recordset, the ... here denote assumed code to setup your recordset, connection etc}

It's worth noting you could write this yourself, it's not a huge leap to loop through an ADODB.Recordset and build a JSON string. However, I would argue against for a few reasons;

1. It is a time-consuming exercise.
2. Very easy to miss something (like checking for numeric data types, when generating output).
3. Depending on how it is coded can make it awkward to maintain (For example, if not injecting property names directly from the recordset and choosing to "hardcode" them instead).
4. Why reinvent the wheel ? There are a lot of public implementations in the wild that deal with the issues raised here. Admittedly, some are better than others, but it takes five minutes to include them and give it a try.

Just for completeness here is my local test code using a disconnected recordset

<!-- #include virtual="/jsonObject.class.asp" --><%Call init()Sub init()  Dim fields: fields = Array(Array("title", adVarChar, 50), Array("firstname", adVarChar, 50), Array("lastname", adVarChar, 50), Array("age", adInteger, 4))  Dim rs: Set rs = Server.CreateObject("ADODB.Recordset")  Call InsertRow(rs, fields, Array("Mr", "Joe", "Bloggs", 31))  Call InsertRow(rs, fields, Array("Mr", "John", "Smith", 42))  Response.LCID = 2057  Dim JSON: Set JSON = New JSONobject  Call JSON.LoadRecordset(rs)  Call Response.Clear()  Response.ContentType = "application/json"  Call JSON.Write()End SubSub InsertRow(ByVal rs, fields, values)  With rs    If rs.State <> adStateOpen Then      For Each fld In fields        Call .Fields.Append(fld(0), fld(1), fld(2))      Next      .CursorLocation = adUseClient      .CursorType = adOpenDynamic      Call .Open()    End If    Call .AddNew()    For i = 0 To UBound(fields, 1)      .Fields(fields(i)(0)).Value = values(i)    Next    Call .Update()    Call .MoveFirst()  End WithEnd Sub%>

Output:

{"data":[{"title":"Mr","firstname":"Joe","lastname":"Bloggs","age":31},{"title":"Mr","firstname":"John","lastname":"Smith","age":42}]}

Here ya go. This works for me.

set rs = conn.execute(strQuery)c=0Response.write "["Do Until rs.eof     'Assign variables here with whatever you need to change     title = rs(0)    fName = rs(1)    lName = rs(2)    empID = rs(3)    With Response        if c > 0 then .write ", "        .write "{" & chr(34) & "Title" & chr(34) & " : " & chr(34) & title & chr(34) & ", " & chr(34) & "FirstName" & chr(34) & " : " & chr(34) & fName & chr(34) & ", "        .write       chr(34) & "LastName" & chr(34) & " : " & chr(34) & lName & chr(34) & ", " & chr(34) & "EmpID" & chr(34) & " : " & chr(34) & empID & chr(34) & "}"    End With    c = c + 1    rs.MoveNextLoopResponse.write "]"

This will write your JSON object directly to the page.

try setting content-type to "application/json" on top of your asp page.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%><%Option ExplicitResponse.Buffer=TrueResponse.ContentType="application/json"Response.Charset="utf-8"'' rest of your code.. your db operations'' response write your json%>