We help IT Professionals succeed at work.

Export asp Queries Results with JSON

ad2sound
ad2sound asked
on
Hello. I Want to use JSON to view my database data with ASP Code.

Exp.
I have a customers table and my fileds are customerid and customername

I need to use  JSON to provide these data

[[customername:blblblb],[customername:test2], [customername:test3]]

Is it possible??

Thanks!!
Comment
Watch Question

Mick BarryJava Developer
Top Expert 2010

Commented:

Author

Commented:

i use this code to retrieve data from recordset.


<%@LANGUAGE="VBSCRIPT"%>
<%option explicit%>
<!--#include file="Connections/blog.asp" -->
<%
Dim rs
Dim rs_cmd
Dim rs_numRows

Set rs_cmd = Server.CreateObject ("ADODB.Command")
rs_cmd.ActiveConnection = MM_blog_STRING
rs_cmd.CommandText = "SELECT customerid, customername FROM customers"
rs_cmd.Prepared = true

Set rs = rs_cmd.Execute
rs_numRows = 0
%>
<%
response.write((new JSON).toJSON("rows", RS))
%>


<%
rs.Close()
Set rs = Nothing
%>

i get the error message

''Microsoft VBScript runtime  error '800a01f4'

Variable is undefined: 'JSON'

/blog/autocomplete3.asp, line 18 ''


Line 18 is
<%
response.write((new JSON).toJSON("rows", RS))
%>

please help
leakim971Multitechnician
Distinguished Expert 2019

Commented:

Author

Commented:
Thanks Leakim

When i;m running the page i get this error

Microsoft VBScript runtime  error '800a01f4'

Variable is undefined: 'fld'

/blog/RStoJSON.inc, line 25

Any ideas?? Thnks Again
leakim971Multitechnician
Distinguished Expert 2019

Commented:
What is line 25 in your "final" code ?

Author

Commented:
is in RStoJON.inc

<%
'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 & """ } )"




LINE 25 :     for each fld in rs.Fields
        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
%>


In the page that you have send me.

Thank
leakim971Multitechnician
Distinguished Expert 2019

Commented:
work fine for me
perhaps your resultset is empty (no rows) or null (bad connexion)
Multitechnician
Distinguished Expert 2019
Commented:
I used a SQL Server, update Server, Initial Catalog, User Id and Password in the following line 4 :
 
strCon = "Provider=sqloledb;Server=192.168.x.y;Initial Catalog=myCatalog;User Id=sa;Password=password"

Test page :

(no modification of RStoJSON.inc)
<% @LANGUAGE="VBSCRIPT" %>
<!--#include File="adovbs.inc"-->
<!--#include File="RStoJSON.inc"-->
<%
	strCon = "Provider=sqloledb;Server=192.168.x.y;Initial Catalog=myCatalog;User Id=sa;Password=password"  
	Set conn = Server.CreateObject("ADODB.Connection")
	Set rs = Server.CreateObject("ADODB.recordset")		   
	conn.Open strCon

	strSQL = "SELECT customerid, customername FROM customers"
	rs.Open strSQL, conn, adOpenStatic, adLockOptimistic

	response.write RStoJSON(rs)
	response.flush

	rs.Close
	Set rs.ActiveConnection = Nothing
	Set rs = Nothing
	conn.Close
	Set conn = Nothing
%>

Open in new window

leakim971Multitechnician
Distinguished Expert 2019

Commented:
I get on the screen :


( {"Records": [ {"customerid":"1","customername":"Brian"}, {"customerid":"2","customername":"Ben"}, {"customerid":"3","customername":"John"} ], "RecordCount":"3" } )

Open in new window

Author

Commented:
<!--#include File="adovbs.inc"-->  What is this File ??
leakim971Multitechnician
Distinguished Expert 2019

Commented:

Author

Commented:
thanks leakim!!!!!! ITS WORKING NOW.

But i have problems with greek lanquage. is there a simple way to correct problem ?

exp.
'When i write "aspis" Shows

aspis real estate ?????????

Author

Commented:
Everything is ok Now Leakim!!!

I add  this code <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
to page Query and it works!!!

Thank you for All!!!