ASP - Printing JSON without last comma and double quotation marks

Question

I need to make a query in the database, and returns a JSON.

I tried using JSON for ASP from Google, but there were some errors, so I decided to do it manually:

<!--#include file="conexao.asp"-->

<%
medico = replace(request("medico"),"'","")
crm = replace(request("crm"),"'","")

sql = "SELECT nome_pro, crm FROM medicos WHERE nome LIKE UPPER('%" & medico & "%') "
set rs = server.createobject("adodb.recordset")
rs.cursorlocation = 3
rs.open sql,conexao


response.write("[")
If Not RS.EOF Then
Do
    response.write("{")
    for each x in rs.fields

        response.write("'" & x.name & "'")
        response.write(":")
        response.write("'" & x.value & "'")
        response.write(",") 

    next
    response.write("}")
    response.write(",")
    RS.MoveNext()

Loop Until RS.EOF
End If   
response.write("]")
%> 

The problem I’m having is that when he prints the JSON, the last comma comes out, like this:

[{'NOME_PRO':'ALCINDO','CRM':'000000',},{'NOME_PRO':'PEDRO','CRM':'000000',},{'NOME_PRO':'PEDRO','CRM':'111111',},]

I never used ASP, I’m using for the first time to generate this JSON, and I was wondering if there are any ways to not print this last comma.

And I was also wondering if there’s any way to get:

response.write("'" & x.name & "'")

Print double quotes instead of single print.

Answer 1

Instead of writing directly, store the result in a variable, and at the end of the process, remove the comma at the end and then write the result.

Behold:

<!--#include file="conexao.asp"-->

<%
medico = replace(request("medico"),"'","")
crm = replace(request("crm"),"'","")

sql = "SELECT nome_pro, crm FROM medicos WHERE nome LIKE UPPER('%" & medico & "%') "
set rs = server.createobject("adodb.recordset")
rs.cursorlocation = 3
rs.open sql,conexao

Dim resposta

resposta = "["

If Not RS.EOF Then
Do
    resposta = resposta & "{"

    for each x in rs.fields

        resposta = resposta & "'" & x.name & "'"
        resposta = resposta & ":"
        resposta = resposta & "'" & x.value & "'"
        resposta = resposta & ","

    next

    resposta = resposta & "}"
    resposta = resposta & ","

    RS.MoveNext()

Loop Until RS.EOF
End If   

'Aqui removemos a ultima virgula no final da string.'
resposta = Left(resposta, (len(resposta)-1))

resposta = resposta & "]"

response.write resposta
%> 

Answer 2

I will show only the Techo of the loop, because it is only in this section that I modified:

c2 = 0;
Do
    If c2 > 0 Then
        response.write(",")
    End If
    response.write("{")
    c = 0;
    for each x in rs.fields
        If c > 0 Then
            response.write(",")
        End If
        response.write("'" & x.name & "'")
        response.write(":")
        response.write("'" & x.value & "'")
        c = 1
    next
    response.write("}")
    c2 = 1;
    RS.MoveNext()
Loop Until RS.EOF

The logic is to add the comma before and allow only when the counter is greater than 0, i.e., when the repetition loop is rotating the second record onwards.

Note that there is the counter C2 and c.

The C2 is for the primary loop.

Inside the primary loop is another loop, where I used a variable called c.

The important thing is to start the counters at 0 before starting each loop, not within the loop.

Also beware of json’s reserved characters. It is safer to escape the strings if they contain reserved characters like double quote, for example.

Replace(str, ""","\"")

Practical example

response.write("'" & Replace(x.name, ""","\"") & "'")