ASP to JSON

[unplugged_web]

I wonder if somebody could help me please. I'm trying to get the results of a database query into json a bit like this but the problem is I've not worked with asp or json before.I've managed to get the query output to xml and I think json must be similar to this, but I can't seem to get it to work. The code I'm using to get the xml is:

<%response.ContentType = "text/xml"set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0;"conn.open Server.MapPath("/database/database.mdb")sql="select * from album where id='7' and active <> 0"set rs=Conn.Execute(sql)while (not rs.EOF)response.write(" " & rs("title") & " ")response.write(" <br/> ")response.write(" <br/> ")response.write("Description: " & rs("description") & " ")response.write(" <br/> ")response.write(" <br/> ")response.write("<a href=" & rs("url") & " target='_blank'><image src='http://images.shrinktheweb.com/xino.php?stwembed=1&stwaccesskeyid=9660cb028b0debf&stwsize=sm&stwurl=" & rs("url") & "'></a>")response.write(" <br/> ")response.write(" <hr> ")response.write(" <br/> ")rs.MoveNext()wendrs.close()conn.close()%>

The xml output does work btwThanks

[justsomeguy]

Have you read about JSON to see how the structure is defined? For simple structures you may just want to print the brackets and quotes and everything yourself, for anything decently complex you'll probably want to use a library to convert an existing data structure to a JSON string. json.org has a description of the JSON structure and links to implementations for various languages.

[unplugged_web]

Have you read about JSON to see how the structure is defined? For simple structures you may just want to print the brackets and quotes and everything yourself, for anything decently complex you'll probably want to use a library to convert an existing data structure to a JSON string. json.org has a description of the JSON structure and links to implementations for various languages.

Thanks, I had a look at the site and it was very helpful although I didn't understand all of it.I've used this to get the results:

<!--#include file="JSON_latest.asp"--><!--#include file="JSON_UTIL_latest.asp"--><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0;"conn.open Server.MapPath("/database/database.mdb")QueryToJSON(dbconn, "select title, artist from album where id='7' and active <> 0").Flush%>

which works, but within each album I wanted to have a sub category of the tracks. So it would look something like this:album one title, artist - track one - track two - track threealbum two title, artist - track one - track two - track threeetc

[justsomeguy]

You won't be able to use QueryToJSON then, you'll need to get the query results yourself and build the structure you need, then convert it to JSON once it's built.

[unplugged_web]

You won't be able to use QueryToJSON then, you'll need to get the query results yourself and build the structure you need, then convert it to JSON once it's built.

Is it possible to do that automatically or do I need to build myself everytime?

[justsomeguy]

You only need to write the code once. Just put the query results in whatever structure you want to send out, and convert it to a JSON string. It sounds like you need an array of objects, where each element in the array is an object that includes the details about the artist, plus an array of tracks.

[unplugged_web]

You only need to write the code once. Just put the query results in whatever structure you want to send out, and convert it to a JSON string. It sounds like you need an array of objects, where each element in the array is an object that includes the details about the artist, plus an array of tracks.

Yes that does sound exactly what I need. How do I do that? Is there anywhere I can get any examples?

[justsomeguy]

Look into using arrays and objects with VBScript. It may be easier to do in Javascript if that's an option for you.

[unplugged_web]

Look into using arrays and objects with VBScript. It may be easier to do in Javascript if that's an option for you.

I'm more than happy doing it in JavaScript, but I don't know how to and I'm getting really worried because the head office boss is coming down on Wednesday and I've got to get this done by then.

[justsomeguy]

You can set the language for the ASP page on the top with the @language directive. Note that if you're including other files in other languages that may be a problem, and you'll need to rewrite your existing code to use Javascript instead of VBScript. Have you used arrays and objects in Javascript before?

[unplugged_web]

You can set the language for the ASP page on the top with the @language directive. Note that if you're including other files in other languages that may be a problem, and you'll need to rewrite your existing code to use Javascript instead of VBScript. Have you used arrays and objects in Javascript before?

No I've nevr used that sort of thing in JavaScript before. This is the code that I'm currently using to get the json file:

<!--#include file="JSON_latest.asp"--><!--#include file="JSON_UTIL_latest.asp"--><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0;"conn.open Server.MapPath("/database/database.mdb")QueryToJSON(dbconn, "select title, artist from album where id='7' and active <> 0").Flush%>

but I want it to resemble this format with this sort of content:album one title, artist- track one- track two- track threealbum two title, artist- track one- track two- track threeetcThank you so much for your help

[justsomeguy]

The structure you see in that file could be created statically like this, using Javascript objects and arrays, the long way:

var obj = new Object();obj.text = 'Cars';obj.items = new Array();obj.items[0] = new Object();obj.items[0].text = 'Asia';obj.items[0].items = new Array();obj.items[0].items[0] = new Object();obj.items[0].items[0].text = 'Japan';obj.items[0].items[0].items = new Array();obj.items[0].items[0].items[0] = new Object();obj.items[0].items[0].items[0].text = 'Acura';obj.items[0].items[0].items[0].leaf = true;obj.items[0].items[0].items[1] = new Object();obj.items[0].items[0].items[1].text = 'Honda';obj.items[0].items[0].items[1].leaf = true;obj.items[0].items[0].items[2] = new Object();obj.items[0].items[0].items[2].text = 'Infiniti';obj.items[0].items[0].items[2].leaf = true;//etc

After that, you would convert obj into a JSON string. Notice that it's just a series of arrays and generic objects with various named properties.That code is in Javascript, so one option is to change your script to use JScript and use a different JSON library also written in Javascript, then build the structure like that. The other option is to figure out what the equivalent code is in VBScript, and use the code you have now plus the changes to make objects and arrays in VBScript.If you haven't done any research about how to use objects and arrays in whichever language you decide to use, now would be a good time.

[unplugged_web]

Thank you so much for your help. I'll have a look at those

[unplugged_web]

I've managed to get the results using GetRows() but I can either get all of the results or get them in the format I want. I can't find out how to do both.Gets all of the results, but with no formatting:

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open(Server.Mappath("/database/database.mdb"))set rs = Server.CreateObject("ADODB.recordset")rs.Open "select title, artist, tracks, url, releasedate from listings where music='17' and active <> 0", connp=rs.GetRows()rs.closeDim iR, iCFor iR = 0 To UBound(p, 2)For iC = 0 To UBound(p, 1)Response.Write p(iC, iR) & "<br>"NextNext%>

Gets the right formatting, but only returns one result:

response.write(p(0,0))response.write("<center>")response.write("<br/><br/>")response.write(p(1,0))response.write("<br /><br/>")response.write("<a href=" & p(3,0) & " target='_blank'><image src='http://images.shrinktheweb.com/xino.php?stwembed=1&stwaccesskeyid=9660cb028b0debf&stwsize=sm&stwurl=" & p(3,0) & "'></a>")response.write("<br/><br/>")response.write("Released: ")response.write(p(3,0))response.write(" | ")response.write("Tracks: ")response.write(p(2,0))response.write("</center>")response.write("<hr>")response.write("<br />")

[justsomeguy]

Your second piece of code needs to go inside a loop where it replaces the 0 with the current row. You've hard-coded that so it only reads from row 0, but that needs to be in a loop where it changes what row it reads from each time.

[unplugged_web]

Your second piece of code needs to go inside a loop where it replaces the 0 with the current row. You've hard-coded that so it only reads from row 0, but that needs to be in a loop where it changes what row it reads from each time.

I changed it to:

Dim iR, iCFor iR = 0 To UBound(p, 2)For iC = 0 To UBound(p, 1)response.write(p(iC, iR))response.write("<center>")response.write("<br/><br/>")response.write(p(iC, iR))response.write("<br /><br/>")response.write("<a href=" & p(iC, iR) & " target='_blank'><image src='http://images.shrinktheweb.com/xino.php?stwembed=1&stwaccesskeyid=9660cb028b0debf&stwsize=sm&stwurl=" & p(iC, iR) & "'></a>")response.write("<br/><br/>")response.write("Added: ")response.write(p(iC, iR))response.write(" | ")response.write("Hits: ")response.write(p(iC, iR))response.write("</center>")response.write("<hr>")response.write("<br />")NextNext

but then it seemed to display everything a bit madly.I tried it with:

Dim iR, iCFor iR = 0 To UBound(p, 2)For iC = 0 To UBound(p, 1)response.write(p(0,0))response.write("<center>")response.write("<br/><br/>")response.write(p(1,0))response.write("<br /><br/>")response.write("<a href=" & p(2,0) & " target='_blank'><image src='http://images.shrinktheweb.com/xino.php?stwembed=1&stwaccesskeyid=9660cb028b0debf&stwsize=sm&stwurl=" & p(2,0) & "'></a>")response.write("<br/><br/>")response.write("Added: ")response.write(p(3,0))response.write(" | ")response.write("Hits: ")response.write(p(4,0))response.write("</center>")response.write("<hr>")response.write("<br />")NextNext

but that looped just that result. I also tried changing it to "p(1, i)" "p(2,i)" etc, but that didn't work either.

[justsomeguy]

Don't change the columns, and don't loop through the columns. Loop through the rows. You want to loop through each row and display each column where you want it, so the column positions still get hard-coded but the row position needs to be a variable.

[unplugged_web]

Don't change the columns, and don't loop through the columns. Loop through the rows. You want to loop through each row and display each column where you want it, so the column positions still get hard-coded but the row position needs to be a variable.

Sorry to be a pain but how do I do that?

[justsomeguy]

You added 2 loops. One loops over the rows, and the other loops over the columns. You only need to loop over the rows, that's all I was trying to get you to add. I assume this statement:p(iC, iR)returns the value in a certain column and row. The column number should not be a variable, you know where you need the different columns. The row should be a variable because you need to run the same code for every row, so you need to loop over the rows. The column numbers should not be a loop or variable, you should just tell it where you want each column to get printed.

[unplugged_web]

You added 2 loops. One loops over the rows, and the other loops over the columns. You only need to loop over the rows, that's all I was trying to get you to add. I assume this statement:p(iC, iR)returns the value in a certain column and row. The column number should not be a variable, you know where you need the different columns. The row should be a variable because you need to run the same code for every row, so you need to loop over the rows. The column numbers should not be a loop or variable, you should just tell it where you want each column to get printed.

Okay I think I understand what you're saying. I did try:

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open(Server.Mappath("/database/database.mdb"))set rs = Server.CreateObject("ADODB.recordset")rs.Open "select title, artist, tracks, url, releasedate from listings where music='17' and active <> 0", connp=rs.GetRows()rs.closeDim iR, iCFor iR = 0 To UBound(p, 2)For iC = 0 To UBound(p, 1)Response.Write p(iC, iR) & "<br>"NextNext%>and it worked in so much as it got all of the information correctly, but it didn't have any of the formatting that I need it to have such as :[code]response.write("<a href=" & p(2,0) & " target='_blank'><image src='http://images.shrinktheweb.com/xino.php?stwembed=1&stwaccesskeyid=9660cb028b0debf&stwsize=sm&stwurl=" & p(2,0) & "'></a>")

I think this is where I'm getting confused

[justsomeguy]

In post 14 you have one piece of code that loops over both columns and rows, and another piece of code that hard-codes both column and row numbers. You changed it so that the second one loops over both columns and rows. I'm saying it should only loop over rows, not columns. You have two loops, you only need one.

[unplugged_web]

In post 14 you have one piece of code that loops over both columns and rows, and another piece of code that hard-codes both column and row numbers. You changed it so that the second one loops over both columns and rows. I'm saying it should only loop over rows, not columns. You have two loops, you only need one.

So how would I write that so that I still keep all of the formatting?

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open(Server.Mappath("/database/database.mdb"))set rs = Server.CreateObject("ADODB.recordset")rs.Open "select title, artist, tracks, url, releasedate from listings where music='17' and active <> 0", connp=rs.GetRows()rs.closeDim iR, iCFor iR = 0 To UBound(p, 2)response.write(p(0, iR))response.write("<center>")response.write("<br/><br/>")response.write(p(1, iR))response.write("<br /><br/>")response.write("<a href=" & p(3, iR) & " target='_blank'><image src='http://images.shrinktheweb.com/xino.php?stwembed=1&stwaccesskeyid=9660cb028b0debf&stwsize=sm&stwurl=" & p(3, iR) & "'></a>")response.write("<br/><br/>")response.write("Released: ")response.write(p(3, iR))response.write(" | ")response.write("Tracks: ")response.write(p(2, iR))response.write("</center>")response.write("<hr>")response.write("<br />")NextNext%>

[justsomeguy]

Does that code work? What kind of formatting are you looking for?

[unplugged_web]

Does that code work? What kind of formatting are you looking for?

I does kind of work. It gets the information in exactly the format I want, but it won't display it in my app because there's line breaks in the text Is there a way of removing them all automatically as I get the results?I tried using CDATA, but that just removed the text completely.

response.write("<![CDATA[ ")response.write(p(1,iR))response.write("]]>")

[justsomeguy]

You can use replace to replace line breaks with something else.http://www.w3schools.com/vbscript/func_replace.asp