We help IT Professionals succeed at work.

Syntax error creating JSON recordset

173 Views
Last Modified: 2017-04-08
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.

greyedout
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
That is awesome!! Thank you very much Julian
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
You are welcome
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.