VBScript, Javascript for JSON from SQL Server

<meta http-equiv="x-ua-compatible" content="ie=9" /> 
<html>
      <head>
            <title>JSON from SQL Server</title>
            <hta:application
                  SINGLEINSTANCE="yes"
                  MAXIMIZEBUTTON="yes"
            >

            <script language="javascript">
                  try {
                    self.resizeTo(800, 400);
                   } catch(e) {
                   }
            </script>
            
      
<script language="VBscript">
	
Dim strSQL
    
Sub btnShowJSON()
  ProcessJSON(fnQueryData)
End Sub

function RStoJSON(rs)
    dim sFld,fld,sFlds
    dim sRec,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 & """:""" & fld.Value&"" & """"
                sFlds = sFlds & GetDelim(sFlds <> "","fld") & sFld
            next 
            sRec = "{" & sFlds & "}"
            sRecs = sRecs & GetDelim(sRecs <> "", "rec") & sRec
            rs.MoveNext
        loop
        sRecordSet = "( {""Records"": [" & vbCrLf & sRecs & vbCrLf & "], " 
        sRecordSet = sRecordSet & """RecordCount"":""" & lRecCnt & """ } )"
        RStoJSON = sRecordSet
    end if
end Function

' Return comma depending on condition and type fld or rec '
function GetDelim(bResult,sType)
    if bResult Then
        If sType = "fld" then
        GetDelim = ","
        End If
        If sType = "rec" Then
        GetDelim = "," & vbCrLf
        End if
    else
        GetDelim = ""
    end if
end function

Function fnQueryData()

Dim strJSON

Dim oCn,oRs1,strSQL

Set oCn = CreateObject( "ADODB.Connection" )
Set oRs1 = CreateObject( "ADODB.Recordset"  ) 

oCn.ConnectionString = "PROVIDER=SQLOLEDB" & ";Server=JEFFLD-HP\JD01SQL;Database=AdventureWorks2012;Trusted_Connection=Yes;"

oCn.open

strSQL = "select top 5 firstname,lastname,emailaddress from person.person P join person.emailaddress E on E.BusinessEntityID=P.BusinessEntityID"

oRs1.Open strSQL, oCn


strJSON = RStoJSON(oRS1)

' Clean up database resources
oRs1.Close
oCn.Close    
    
fnQueryData = strJSON    
    
End Function




</script>

<script language="javascript" type="text/javascript">
		
			function ProcessJSON(sJSON)
			{
			var rs = eval(sJSON);
			if ( rs ) { // has a non-null value
			  // get the record count
			  var str = rs.RecordCount + " Users:<br/>";
			  // get the data from the records
			  str = "<table border=1>"
			  for ( var recno = 0 ; recno < rs.RecordCount ; recno++ ) {
			    str += "<tr>";
			    str += "<td>"+rs.Records[recno].firstname + "</td>";
			    str += "<td>"+rs.Records[recno].lastname + "</td>";
			    str += "<td>"+rs.Records[recno].emailaddress + "</td>";
			    str += "</tr>";
			  }
			  str+= "</table>"
			  document.getElementById('userlist').innerHTML = str;
			  document.getElementById('json').innerHTML = sJSON;
			} else { // rs = null
			    str = "No users found"
			}
			}

	
</script>
             

</head>          
<body>
    <input type="button" name="btnShowJSON" id="btnShowJSON" value="Show JSON" onclick="btnShowJSON()"></input>
    <p></p>
    <div id="userlist"></div>
    <div id="json"></div>
</body>

</html>
Leave a Reply Cancel reply
