![\"\"](\"http:\/\/www.polysyncronism.com\/wordpress\/wp-content\/uploads\/2017\/03\/jsonSQLvbscriptJavascript-300x179.png\")
<\/p>\n<\/p>\n
<meta http-equiv=\"x-ua-compatible\" content=\"ie=9\" \/> \r\n<html>\r\n <head>\r\n <title>JSON from SQL Server<\/title>\r\n <hta:application\r\n SINGLEINSTANCE=\"yes\"\r\n MAXIMIZEBUTTON=\"yes\"\r\n >\r\n\r\n <script language=\"javascript\">\r\n try {\r\n self.resizeTo(800, 400);\r\n } catch(e) {\r\n }\r\n <\/script>\r\n \r\n \r\n<script language=\"VBscript\">\r\n\t\r\nDim strSQL\r\n \r\nSub btnShowJSON()\r\n ProcessJSON(fnQueryData)\r\nEnd Sub\r\n\r\nfunction RStoJSON(rs)\r\n dim sFld,fld,sFlds\r\n dim sRec,sRecs\r\n dim sRecordSet\r\n dim lRecCnt\r\n\r\n sRecordSet = \"\"\r\n sRecs = \"\"\r\n lRecCnt = 0\r\n if rs.EOF or rs.BOF then\r\n RStoJSON = \"null\"\r\n else\r\n do while not rs.EOF and not rs.BOF\r\n lRecCnt = lRecCnt + 1\r\n sFlds = \"\"\r\n for each fld in rs.Fields\r\n sFld = \"\"\"\" & fld.Name & \"\"\":\"\"\" & fld.Value&\"\" & \"\"\"\"\r\n sFlds = sFlds & GetDelim(sFlds <> \"\",\"fld\") & sFld\r\n next \r\n sRec = \"{\" & sFlds & \"}\"\r\n sRecs = sRecs & GetDelim(sRecs <> \"\", \"rec\") & sRec\r\n rs.MoveNext\r\n loop\r\n sRecordSet = \"( {\"\"Records\"\": [\" & vbCrLf & sRecs & vbCrLf & \"], \" \r\n sRecordSet = sRecordSet & \"\"\"RecordCount\"\":\"\"\" & lRecCnt & \"\"\" } )\"\r\n RStoJSON = sRecordSet\r\n end if\r\nend Function\r\n\r\n' Return comma depending on condition and type fld or rec '\r\nfunction GetDelim(bResult,sType)\r\n if bResult Then\r\n If sType = \"fld\" then\r\n GetDelim = \",\"\r\n End If\r\n If sType = \"rec\" Then\r\n GetDelim = \",\" & vbCrLf\r\n End if\r\n else\r\n GetDelim = \"\"\r\n end if\r\nend function\r\n\r\nFunction fnQueryData()\r\n\r\nDim strJSON\r\n\r\nDim oCn,oRs1,strSQL\r\n\r\nSet oCn = CreateObject( \"ADODB.Connection\" )\r\nSet oRs1 = CreateObject( \"ADODB.Recordset\" ) \r\n\r\noCn.ConnectionString = \"PROVIDER=SQLOLEDB\" & \";Server=JEFFLD-HP\\JD01SQL;Database=AdventureWorks2012;Trusted_Connection=Yes;\"\r\n\r\noCn.open\r\n\r\nstrSQL = \"select top 5 firstname,lastname,emailaddress from person.person P join person.emailaddress E on E.BusinessEntityID=P.BusinessEntityID\"\r\n\r\noRs1.Open strSQL, oCn\r\n\r\n\r\nstrJSON = RStoJSON(oRS1)\r\n\r\n' Clean up database resources\r\noRs1.Close\r\noCn.Close \r\n \r\nfnQueryData = strJSON \r\n \r\nEnd Function\r\n\r\n\r\n\r\n\r\n<\/script>\r\n\r\n<script language=\"javascript\" type=\"text\/javascript\">\r\n\t\t\r\n\t\t\tfunction ProcessJSON(sJSON)\r\n\t\t\t{\r\n\t\t\tvar rs = eval(sJSON);\r\n\t\t\tif ( rs ) { \/\/ has a non-null value\r\n\t\t\t \/\/ get the record count\r\n\t\t\t var str = rs.RecordCount + \" Users:<br\/>\";\r\n\t\t\t \/\/ get the data from the records\r\n\t\t\t str = \"<table border=1>\"\r\n\t\t\t for ( var recno = 0 ; recno < rs.RecordCount ; recno++ ) {\r\n\t\t\t str += \"<tr>\";\r\n\t\t\t str += \"<td>\"+rs.Records[recno].firstname + \"<\/td>\";\r\n\t\t\t str += \"<td>\"+rs.Records[recno].lastname + \"<\/td>\";\r\n\t\t\t str += \"<td>\"+rs.Records[recno].emailaddress + \"<\/td>\";\r\n\t\t\t str += \"<\/tr>\";\r\n\t\t\t }\r\n\t\t\t str+= \"<\/table>\"\r\n\t\t\t document.getElementById('userlist').innerHTML = str;\r\n\t\t\t document.getElementById('json').innerHTML = sJSON;\r\n\t\t\t} else { \/\/ rs = null\r\n\t\t\t str = \"No users found\"\r\n\t\t\t}\r\n\t\t\t}\r\n\r\n\t\r\n<\/script>\r\n \r\n\r\n<\/head> \r\n<body>\r\n <input type=\"button\" name=\"btnShowJSON\" id=\"btnShowJSON\" value=\"Show JSON\" onclick=\"btnShowJSON()\"><\/input>\r\n <p><\/p>\r\n <div id=\"userlist\"><\/div>\r\n <div id=\"json\"><\/div>\r\n<\/body>\r\n\r\n<\/html><\/pre>\n![\"\"](\"http:\/\/www.polysyncronism.com\/wordpress\/wp-content\/uploads\/2017\/03\/jsonSQLvbscriptJavascript02-300x150.png\")
<\/p>\n","protected":false},"excerpt":{"rendered":"
<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 … Continue reading “VBScript, Javascript for JSON from SQL Server”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,12,6,7,5],"tags":[],"class_list":["post-57","post","type-post","status-publish","format-standard","hentry","category-code","category-javascript","category-json","category-t-sql","category-vbscript"],"_links":{"self":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/57","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/comments?post=57"}],"version-history":[{"count":7,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/57\/revisions"}],"predecessor-version":[{"id":67,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/57\/revisions\/67"}],"wp:attachment":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/media?parent=57"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/categories?post=57"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/tags?post=57"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}