Json to SQL ASP classic

Question

Can´t figure out how to solve this with ASP Classic, an external server will send me some Json to my site ex. www.mypage.com/getjson.asp

{"Userid":"112233","Member":Tom}

How can I fetch this and check if the Userid already exist, otherwise put it in the SQL

If the userid exist I just have to answer

response.Write "{""Userid"": true}"

Can I "convert" it to a querystring or somthing ?

Edit: Thanks for all help, but I coundn´t get it to work with the tips in this thread But I managed to get the whole string whit this code

bytecount = Request.TotalBytes
bytes = Request.BinaryRead(bytecount)

Set stream = Server.CreateObject("ADODB.Stream")
    stream.Type = 1              
    stream.Open()                                   
        stream.Write(bytes)
        stream.Position = 0                             
        stream.Type = 2                 
        stream.Charset = "utf-8"                      
        s = stream.ReadText()               
    stream.Close()
Set stream = nothing
Response.Write =(s)

But now I need to figure put how to cleanup and split this, note that Member don´t have qoutes

{"Userid":"00004547552009","Member":1,"id":"0060a80040d9"}

Answer 1

Mate, i suggest you to check the ASP JSON Class, its make your code clean and so easy to code. Check: http://www.aspjson.com/

Cheers.

Answer 2

Ok long time later … only a partial answer addressing asp parsing the URL query containing JSON. Once that happens, the SQL becomes easy.

This answer uses Jquery - it hinges on how Jquery converts the JSON before sending (via .get).

I'm posting this because a simpler version of John's already simple answer works for me. It's just key-value URL pairs!

And - caveat - although John's example works with some modifications, making the libraries mentioned unnecessary, I cannot vouch that this approach will work for more complex JSON - I haven't tried. (Also I'm glad he mentions mixing server-side jscript with asp's vbscript - a useful technique I'd forgotten about.)

Client-side code (swiped and modded from W3schools JSON / Ajax)

Note use of .get not .post:

$.get( ////.post doesn't work!          
      "ajax_text.asp" ,
      {
        UserId: 666 ,   //// json: no quotes around numbers
        Name: "Donald Puck" ,  //// Jquery's turning it into "name=Donald Puck" 
        City: "Puckburg"
      } ,
      function(data, status) {  //// the callback 
        alert ("Data: " + data + "</br>Ajax Status: " + status);
      }
);

( Properly speaking, UserID, Name, and City should be wrapped in quotes, but either way parses correctly in this example. )

And here's the server-side code (Based on John's answer):

<script language="javascript" runat="server">
   //// Yes, ok, it's jscript, not really javascript - but it works 
   //// ... probably just as easy to use ASP-vbscript
   var jUserId = Request("UserId");
   var jName = Request("Name"); 
   var jCity = Request("City");
</script>

<%
   response.write ("You sent: " & request.ServerVariables("QUERY_STRING") ) '' verbose
   response.write ("'jscript' version got: " & jUserId & " " & jName & " " & jCity)
%>

Answer 3

And what about use my utility class: https://github.com/rcdmk/aspJSON

Some examples from the README:

' instantiate the class
Dim oJSON = New JSON

' add properties
oJSON.Add "prop1", "someString"
oJSON.Add "prop2", 12.3
oJSON.Add "prop3", Array(1, 2, "three")

' change some values
oJSON.Change "prop1", "someOtherString"
oJSON.Change "prop4", "thisWillBeCreated" ' this property doen't exists and will be created automagically

' get the values
Response.Write oJSON("prop1") & "<br>"
Response.Write oJSON("prop2") & "<br>"
Response.Write oJSON("prop3") & "<br>"
Response.Write oJSON("prop4") & "<br>"

' get the JSON formatted output
Dim jsonSting
jsonString = oJSON.Serialize() ' this will contain the string representation of the JSON object

oJSON.Write() ' this will write the output to the Response - equivalent to: Response.Write oJSON.Serialize()

' load and parse some JSON formatted string
jsonString = "{ ""strings"" : ""valorTexto"", ""numbers"": 123.456, ""arrays"": [1, ""2"", 3.4, [5, 6, [7, 8]]], ""objects"": { ""prop1"": ""outroTexto"", ""prop2"": [ { ""id"": 1, ""name"": ""item1"" }, { ""id"": 2, ""name"": ""item2"", ""teste"": { ""maisum"": [1, 2, 3] } } ] } }" ' double quotes here because of the VBScript quote scaping

oJSON.Parse(jsonString) ' set this to a variable if your string to load can be an Array, since the function returns the parsed object and arrays are parsed to JSONarray objects
' if the string represents an object, the current object is returned so there is not need to set the return to a new variable

oJSON.Write()

Edit:

I've added methods to load recordsets and bidimensional arrays some time ago. This can help a lot in writing JSON from the database:

' load records from an ADODB.Recordset
dim cn, rs
set cn = CreateObject("ADODB.Connection")
cn.Open "yourConnectionStringGoesHere"

set rs = cn.execute("SELECT id, nome, valor FROM pedidos ORDER BY id ASC")
' this could also be:
' set rs = CreateObject("ADODB.Recordset")
' rs.Open "SELECT id, nome, valor FROM pedidos ORDER BY id ASC", cn 

JSON.LoadRecordset rs
JSONarr.LoadRecordset rs

rs.Close
cn.Close
set rs = Nothing
set cn = Nothing

JSON.Write()        ' outputs: {"data":[{"id":1,"nome":"nome 1","valor":10.99},{"id":2,"nome":"nome 2","valor":19.1}]}
JSONarr.Write()     ' outputs: [{"id":1,"nome":"nome 1","valor":10.99},{"id":2,"nome":"nome 2","valor":19.1}]

Answer 4

You really need a JSON passer for this. Best one I have found is Extreme JSON A VbsJson class which has a "Decode" method to parse JSON to VBScript and a "Encode" method to generate JSON from VBScript. The code is somewhat long, I have extracted the Encode and Decode functionality and have used it. It seesm to work well.

I get the feeling you just have this simple little piece of JSON which is always the same. With different values of course but always with a userID and a member. If so here is something that will work for you. note - You are missing quotes around "Tom" in your example. Save this to a txt file called json.txt in the same dir as your .asp page:

{"Userid":"112233","Member":"Tom"}

Now put this in your page. It should be cut n paste except for it expects you have a Database connection object called conn. Oh and you will have to change the query

dim fso, jsonStr, myArray, i, sql, rs
Set fso = Server.CreateObject("Scripting.Filesystemobject")
'read json from some source. in this case its in a txt file in same locaiton as .asp file
jsonStr = fso.OpenTextFile(Server.MapPath("json.txt")).readAll
'split it on the "
myArray = split(jsonStr, """")
'loop through elements
for i = 0 to uBound(myArray)
    'check which one is a number
    if isNumeric(myArray(i)) then
        response.write myArray(i) & " is a number so check in DB"
        sql = "SELECT id from table WHERE userID=" & myArray(i)  & ";"
        set rs = conn.execute(sql)
        if rs.eof then
            'not in DB so return JSON
            response.Write "{""Userid"": ""true""}" 
        end if
    else
        'response.write myArray(i) & " is not a number"
    end if
next

UPDATE - As I have said the solution above is customised for a JSON format that you know will not change, is simple and you know the one integer value is the one you are looking for. You should use a JSON paser for anything else!

You say your not sure about how JSON is sent. Its a text string that will be sent and available to you via the request object. Either .form or .querystring. I always avoid querystring if I can in all situations. The form collection is better for many reasons (another whole conversation). No someone can not pass you JSON like your example. I believe it must be encoded E.G:

{"Userid":"112233"}

Would look like this:

www.mypage.com/path.asp?json=%7B%22Userid%22%3A%20112233%7D

Answer 5

Classic ASP supports Javascript as well as VBScript, and in my experience its much easier to use it to deal with JSON than to use any of the JSON VBS classes which are out there. You can declare Javascript as your language at the top of the page (<%@language="javascript"%>) but if you prefer to use VBS elsewhere you could use something like this in the head section of your page. Note the runat="Server" attribute

<script language="javascript" runat="server">
var JSONObject= Request("YourJsonStringName");
var jUserId = JSONObject.UserId;
var jMember =JSONObject.Member;
</script>

the strings jUserId and jMember would then be available for you to use elsewhere in your page within VBS code inside <% %> delimiters and you can use them in your database insert.