Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Translating a VBScript (ASP) recordset object to JSON

JSON

Translating a VBScript (ASP) recordset object to JSON

by  tsdragon  Posted    (Edited  )
Here is a VBScript (ASP) function that you can use to easily convert an ADO recordset into JavaScript Object Notation (JSON). The object it returns contains two properties:

Records: an array of records in fldname:value form
RecordCount: the number of records returned

Copy the following code, paste it into an empty file, and save it as RStoJSON.inc
Code: 
<%
'Function to convert an ADO recordset into a JSON object
'
'Written by Tracy Dryden, Commonwealth Technology Group, Inc.
'
'Released to the public domain.

function RStoJSON(rs)
	dim sFld
	dim sFlds
	dim sRec
	dim sRecs
	dim sRecordSet
	dim lRecCnt

	sRecordSet = ""
	sRecs = ""
	lRecCnt = 0
	if rs.EOF or rs.BOF then
		RStoJSON = "null"
	else
		do while not rs.EOF and not rs.BOF
			lRecCnt = lRecCnt + 1
			sFlds = ""
			for each fld in rs.Fields
				sFld = """" & fld.Name & """:""" & toUnicode(fld.Value&"") & """"
				sFlds = sFlds & iif(sFlds <> "", ",", "") & sFld
			next 'fld
			sRec = "{" & sFlds & "}"
			sRecs = sRecs & iif(sRecs <> "", "," & vbCrLf, "") & sRec
			rs.MoveNext
		loop
		sRecordSet = "( {""Records"": [" & vbCrLf & sRecs & vbCrLf & "], " 
		sRecordSet = sRecordSet & """RecordCount"":""" & lRecCnt & """ } )"
		RStoJSON = sRecordSet
	end if
end function

function toUnicode(str)
	dim x
	dim uStr
	dim uChr
	dim uChrCode
	uStr = ""
	for x = 1 to len(str)
		uChr = mid(str,x,1)
		uChrCode = asc(uChr)
		if uChrCode = 8 then ' backspace
			uChr = "\b" 
		elseif uChrCode = 9 then ' tab
			uChr = "\t" 
		elseif uChrCode = 10 then ' line feed
			uChr = "\n" 
		elseif uChrCode = 12 then ' formfeed
			uChr = "\f" 
		elseif uChrCode = 13 then ' carriage return
			uChr = "\r" 
		elseif uChrCode = 34 then ' quote 
			uChr = "\""" 
		elseif uChrCode = 39 then ' apostrophe
			uChr = "\'" 
		elseif uChrCode = 92 then ' backslash
			uChr = "\\" 
		elseif uChrCode < 32 or uChrCode > 127 then ' non-ascii characters
			uChr = "\u" & right("0000" & CStr(uChrCode),4)
		end if
		uStr = uStr & uChr
	next
	toUnicode = uStr
end function

function iif(cond,tv,fv)
	if cond then
		iif = tv
	else
		iif = fv
	end if
end function
%>
Include this file in your VBScript (ASP) program with a standard SSI include. Use it with an ADO recordset like this:
Code: 
set cn = Server.CreateObject("ADODB.Connection")
cn.Open "..."
sSQL = "select * from users order by lastname, firstname"
set rs = cn.Execute(sSQL)
response.write RStoJSON(rs)
response.flush
rs.Close
set rs = nothing
cn.close
set cn = nothing
%>
If the recordset is empty, the resulting JSON object will have the value null. You can check for this in your javascript code. The function does not attempt to perform conversions to correct javascript data types - all field values are returned as strings.

Use the JSON object within your javascript code like this:
Code: 
// create the recordset object
var rs = eval(request.responseText);
if ( rs ) { // has a non-null value
  // get the record count
  var str = rs.RecordCount + " Users:<br/>";
  // get the data from the records
  for ( var recno = 0 ; recno < rs.RecordCount ; recno++ ) {
    str += "&nbsp;&nbsp;&nbsp;";
    str += rs.Records[recno].handle + " is ";
    str += rs.Records[recno].firstname + " ";
    str += rs.Records[recno].lastname + "<br/>";
  }
  document.getElementById('userlist').innerHTML = str;
} else { // rs = null
	str = "No users found"
}
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top