Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Syntax error creating JSON recordset

I am running the following SQL using SQL Enterprise Manager and it returns the record just fine.

SELECT
'{
  "first_name": "' + FirstNm + '",
  "last_name": "' + LastNm + '",
  "email_addresses": [
    {
      "email_address": "' + email + '"
    }
  ]
}' as json_result
FROM dbo.Users 
WHERE userid = 1

Open in new window


The problem is when I create the recordset in ASP Classic. It greys out part of the code.

User generated image
I think it is the '  character, thinks the rest is a comment. How can I replace it so that it takes that as part of the query and not a regular ' which will then ignore the rest of the code?

<%
Dim rs_contactjson__mmcolparam
rs_contactjson__mmcolparam = "0"
If (request.querystring("NewUserID") <> "") Then 
  rs_contactjson__mmcolparam = request.querystring("NewUserID")
End If
%>
<%
Dim rs_contactjson
Dim rs_contactjson_cmd
Dim rs_contactjson_numRows

Set rs_contactjson_cmd = Server.CreateObject ("ADODB.Command")
rs_contactjson_cmd.ActiveConnection = MM_bluedot_STRING
rs_contactjson_cmd.CommandText = "SELECT '{   "first_name": "' + FirstNm + '",   "last_name": "' + LastNm + '",   "email_addresses": [     {       "email_address": "' + email + '"     }   ] }' as json_result FROM dbo.Users WHERE userid = ?" 
rs_contactjson_cmd.Prepared = true
rs_contactjson_cmd.Parameters.Append rs_contactjson_cmd.CreateParameter("param1", 5, 1, -1, rs_contactjson__mmcolparam) ' adDouble

Set rs_contactjson = rs_contactjson_cmd.Execute
rs_contactjson_numRows = 0
%>

Open in new window

Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Escape it by entering a double ' i.e.
rs_contactjson_cmd.CommandText = "SELECT '{   "first_name": "' + FirstNm + '",   "last_name": "' + LastNm + '",   "email_addresses": [     {       "email_address": "' + email + '"     }   ] }'' as json_result FROM dbo.Users WHERE userid = ?"

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

That is awesome!! Thank you very much Julian
You are welcome