Help loading JSON from RS

[newuserone]

How do I call a recordset and rename the properties in the process. So, I have a query retrieving a whole lot of data but all I need to pull are 3 columns, how do i place conditions to set for example this format:

[{"title":"Free Pizza","description":"<p>This is just a fake description for the Free Pizza.</p><p>Nothing to see!</p>","start":"2017-01-25T19:31:10","end":"2017-01-25T20:31:10"},{"title":"DNUG Meeting","description":"<p>This is just a fake description for the DNUG Meeting.</p><p>Nothing to see!</p>","start":"2017-01-26T19:31:10","end":"2017-01-26T20:31:10"}]

[rcdmk]

Hi.

The method uses field/column names for the property names.

You can use aliases in your query, like: SELECT originaltitle AS title FROM tablename

There is no way to load only a set of fields from a recordset out of the box.

You can build the JSON freely, using a jsonArray instance to hold the collection if needed.

[newuserone]

I can't modify the query as is used in too many places. Is there a full example of the array example as I wasn't able to successful use the examples here. Can you help?

[rcdmk]

Sure.

You will have to create an object for each record you want to serialize:

' instantiate the class
Dim JSON
set JSON = New JSONobject

' add properties
JSON.Add "prop1", "someString"
JSON.Add "prop2", 12.3
' ...

If you need a collection, you will have to create an array to hold the objects:

' instantiate the class
Dim JSONarr
Set JSONarr = New JSONarray

' add something to the array
JSONarr.Push JSON   ' Can be JSON objects, and even JSON arrays
JSONarr.Push 1.25   ' Can be numbers
JSONarr.Push "and strings too"

You can loop your recordset, creating instances of the JSONobject class and "pusing" to the JSONarray instance:

Dim JSONarr = New JSONarray
' rs = your recordset 
While Not rs.EOF
    set JSON = New JSONobject
    JSON.Add "title", rs("mytitle")
    JSON.Add "description", rs("mydescription")
    JSON.Add "start", rs("mystartdate")
    JSON.Add "end", rs("myenddate")

    JSONarr.Push JSON

    rs.MoveNext
Wend

JSONarr.Write()

You can find more examples on the README and the test.asp page.

[newuserone]

I will try this, thanks! Is there a way to save the string to variable rather that write the restults JSONarr.Write()?

[rcdmk]

You can use the Serialize() method. It returns a string.

You can see more examples on the README and test.asp page.

Best.

[newuserone]

I used the recordset example but got this error File Name: jsonObject.class.asp Line Number: 631 Description: Object doesn't support this property or method: 'obj.pairs'

[rcdmk]

What are the data types of the columns you are mapping? Can you please post your code?

[newuserone]

Thanks for your quick reply. The resolution here solved the problem for me https://github.com/rcdmk/aspJSON/issues/21

To clarify, I had set the rs to a string first... varAction = Request("action") varJSON1.Add "action", varAction

and then it worked eventhough I'm using the developer release (latest).

[rcdmk]

I've done a new release. Can you test if this works now without using a variable?

Also, its not recomended to output data directly from the request. This way you open your app for XSS injection and some other threats.

All user input is evil.

[newuserone]

Thanks! But no good... This is only way it worked:

JSONarr = New JSONarray ' rs = your recordset While Not rs.EOF set JSON = New JSONobject str0 =rs("cid") str1 = rs("title") str2 = rs("excerpt") str3 = rs("startdate") str4 = rs("enddate") JSON.Add "title", str1 JSON.Add "description", str2 JSON.Add "start", str3 JSON.Add "end", str4 JSON.Add "url", "/folder/?_from=" & script_name JSONarr.Push JSON rs.MoveNext Wend

[marceloanuch]

Hi,I use this and work fine for me, only Ara not work:

<% dim jsonObj, jsonArr

set jsonObj = new JSONobject
set jsonArr = new jsonArray

' load records from an ADODB.Recordset dim cn, rs set cn = CreateObject("ADODB.Connection") cn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=database_name; UID=user_db;PASSWORD=pass_db; OPTION=16384"

set rs = cn.execute("SELECT * FROM t_tipos_pagos ORDER BY tipo ASC")

While not rs.eof jsonObj.LoadRecordset rs jsonArr.LoadRecordset rs rs.movenext Wend

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

jsonObj.defaultPropertyName = "Custom" jsonObj.Write()

response.write vbLf

jsonArr.Write()

%>

If you can see source code in real time, go to http://dev.medialink.cl/json/lectura.asp http://dev.medialink.cl/json/lectura.asp

I want to use Kendo UI with JSON to make a Rent a Car System

Coordialmente, Best Regards,

Marcelo A. Anuch Medina Consultor de Empresas Público/Privadas Ingeniero Comercial, MBA UDD Business Management

🔒 La información contenida en esta transmisión es confidencial y está dirigida exclusivamente a el o los destinatarios indicados. Cualquier uso por otras personas que no sean sus destinatarios está sancionado por la ley chilena. Si ha recibido esta transmisión por error, por favor destrúyala y notifique al remitente si corresponde. Las opiniones expresadas en este correo son las de su autor y pueden no ser compartidas por la compañía. Marcelo A. Anuch Medina, no asume obligaciones ni responsabilidades por el contenido de este correo. Tampoco garantiza que la transmisión de este correo sea segura o libre de errores, y en consecuencia, no responde por virus, pérdidas, destrucción, recepción tardía o incompleta.

🍀 Antes de imprimir este correo electrónico asegúrese que sea realmente necesario: Cuidemos el medio ambiente! Todos vivimos en un solo planeta.

El 25-01-2017, a las 23:18, newuserone notifications@github.com escribió:

Thanks! But no good... This is only way it worked:

`set JSONarr = New JSONarray ' rs = your recordset While Not rs.EOF set JSON = New JSONobject str0 = RemoveGuidBrackets(rs("cid")) str1 = rs("title") str2 = rs("excerpt") str3 = rs("startdate") str4 = rs("enddate")

JSON.Add "title", str1 JSON.Add "description", str2 JSON.Add "start", str3 JSON.Add "end", str4 JSON.Add "url", "/folder/?_from=" & script_name

JSONarr.Push JSON rs.MoveNext Wend` — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/rcdmk/aspJSON/issues/31#issuecomment-275292415, or mute the thread https://github.com/notifications/unsubscribe-auth/AIiv6d3ZpUZoK8CtbqMoi8O5w7K250WDks5rWAKRgaJpZM4Lszax.

[rcdmk]

Hi.

You don't need the loop to load a recordset. There is an internal loop for this inside the .LoadRecodset(rs) method.

You do need to set the default property of the array object you will serialize.

[marceloanuch]

I try without loop, but no t work, this is my code:

<% dim jsonObj, jsonArr

set jsonObj = new JSONobject
set jsonArr = new jsonArray

' load records from an ADODB.Recordset dim cn, rs set cn = CreateObject("ADODB.Connection") cn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=rac_mackenna; UID=root;PASSWORD=xxxx123; OPTION=16384"

set rs = cn.execute("SELECT * FROM t_tipos_pagos ORDER BY tipo ASC")

jsonObj.LoadRecodset(rs)
jsonArr.LoadRecodset(rs)

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

jsonObj.Write()

response.write vbLf

jsonArr.Write()
%>

The result on navigator is: {} []

Bow, with this code:

<% dim jsonObj, jsonArr

set jsonObj = new JSONobject
set jsonArr = new jsonArray

' load records from an ADODB.Recordset dim cn, rs set cn = CreateObject("ADODB.Connection") cn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=rac_mackenna; UID=root;PASSWORD= xxxx123; OPTION=16384"

set rs = cn.execute("SELECT * FROM t_tipos_pagos ORDER BY tipo ASC")

While not rs.eof jsonObj.LoadRecordset rs jsonArr.LoadRecordset rs rs.movenext Wend

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

JSONarr.Push JSON

jsonObj.defaultPropertyName = "Custom" jsonObj.Write()

response.write vbLf

jsonArr.Write()

%>

The navigator result is:

{"Custom":[{"TIPO":1,"NOMBRE":"Efectivo Pesos"},{"TIPO":2,"NOMBRE":"Efectivo Dolares"},{"TIPO":3,"NOMBRE":"Cheques"},{"TIPO":4,"NOMBRE":"Red Compra"},{"TIPO":5,"NOMBRE":"T. de Credito"},{"TIPO":6,"NOMBRE":"Deposito"},{"TIPO":7,"NOMBRE":"Factura Credito"},{"TIPO":8,"NOMBRE":"Letras"},{"TIPO":10,"NOMBRE":"Cheques al Dia"},{"TIPO":11,"NOMBRE":"Saldos a Favor"},{"TIPO":12,"NOMBRE":"Nota Cred."},{"TIPO":13,"NOMBRE":"Factura"},{"TIPO":14,"NOMBRE":"Boleta"},{"TIPO":20,"NOMBRE":"Cheques a Fecha"}]} [""]

I think that LoadRecordset not work without Loop

[dtrillo]

I have the same feeling related to LoadRecordset, that it is not working as expected. I needed to create my own function, where I specified the fields and I have no problem, but I can't use LoadRecordset.

[rcdmk]

I will take a look at this soon. BTW, does the code of test.asp page works for you?

It doesn't have a loop to load the recodset. It's similar to what is indicated on the README file and the way it should have worked.

Also, you don't need to load a JSONobject AND a JSONarray. You can use the one that makes more sense to your logic.

Also pay attention that there is a .LoadFirstRecord(rs) method that does what it's name implies.

Than you both for the feedback.

[marceloanuch]

I use json with this:

http://demos.telerik.com/kendo-ui/grid/editing

Now, it sample use CRUD where de READ go to this link:

https://demos.telerik.com/kendo-ui/service/Products

The question is: How we can make to create this type of data format?

[marceloanuch]

Hi again!

I have some question:

• Why in jsonObject.class.asp Loadrecordset is define two time how function? (here obj.add and arr.push end the while function and Loadrecordset not work itself)
• How I can delete a {"data": ..... } because I need only [{...},{...},{...}] to use with a:

callback = Request("callback") Response.Write( callback & JsonObj.Write() )

This work fine to work with Kendo UI Framework for example.

Thanks.

[marceloanuch]

Hi, I make my little own json: (I make it only to work with jsonp type)

Dim json_var json_var = "(["

While not rs.eof json_var = json_var & "{" & chr(34) & "Id" & chr(34) & ":" & rs("Id") & ", " & chr(34) & "Modelo" & chr(34) & ":" & chr(34) & rs("Modelo") & chr(34) & "}," rs.movenext Wend

json_var = left(json_var,len(json_var)-1) json_var = json_var & "])"

callback = Request("callback") // callback querystring contains the callback function name Response.Write( callback & json_var )

Work fine ;)

[rcdmk]

Hi.

There is 2 LoadRecordset methods because one of them is for the JSONobject and one for the JSONarray classes.

You have some options for getting an array: 1- Use the LoadRecordset method of the JSONarray directly:

<%
'...
set arr = new JSONarray

arr.LoadRecordset(rs)

str = arr.Serialize() ' [{...}] - only array
%>

2. Use the LoadRecordset method from JSONobject but assign the data property to a variable:

   
   <%
   '...
   set json = new JSONobject

json.LoadRecordset(rs) set arr = json("data")

str = json.Serialize() ' {"data":[{...}]} - object with array str = arr.Serialize() ' [{...}] - only array %>

[lankymart]

^{profesionalTI commented 20 days ago} Hi, I make my little own json: (I make it only to work with jsonp type)

Really, after the effort that has been put into this library?

For those interested, have answered a question recently about this on StackOverflow.

^{P.S - Great library by the way!}

[rcdmk]

Hi,

What is the current status of this issue for you involved?

Have any of these comments answered the questions or helped with your problems?

I'm a little confused with so many people interacting about what seem to be different things.

[rcdmk]

I'm closing this issue. In case you guys still have doubts or problems, you can get back to me at anytime.

Bests,