Issue saving a JSON string to a SQL Server database

Question

My Angular web application currently serializes/deserializes JSON objects to/from local storage using the HTML5 localStorage object. We are now attempting to save and retrieve those same JSON objects to/from a SQL Server table.

I am however having difficultly as I try to save a JSON string using the following SQL UPDATE:

UPDATE [rz37883_SQLServer].[dbo].[RAGEDashboard]
SET 
  image = '{"widgets":[{"title":"Bar Chart","name":"chart_bar","style":{},"size":{"width":"50%","height":320},"dataModelOptions":{"title":{"text":"Bar Chart","position":"bottom"},"legend":{"visible":true,"position":"top"},"seriesDefaults":{"type":"bar","stack":false},"series":[{"field":"field1","name":"MTM"}],"dataSource":{"data":[{"field1":236151654.592439},{"field1":103612357.347808},{"field1":267066579.129913},{"field1":582355005.154486},{"field1":-9422699.958631}],"table":null},"valueAxis":{"labels":{"format":"{0:c}","rotation":-30},"line":{"visible":false},"axisCrossingValue":0},"categoryAxis":{"categories":["London","New York","Dubai","Paris","Stockholm"],"majorGridLines":{"visible":false},"labels":{"rotation":0,"margin":20}},"tooltip":{"visible":true,"template":"#= series.name #: #= kendo.format('{0:C0}', value) #"},"dimensions":[{"dimension":"BookingLocation","hierarchy":false,"id":0}],"riskMeasures":[{"name":"MTM","kri_group":"[MTM:MTM]","cube_vector":"MTM:MTM","aggreg_formula":"SUM(MTM:MTM)","id":0}]},"initImage":"images2/bar_chart.png","gadgetConfigured":true}]}'
 WHERE [RAGEDashboardConfig_userid] = 'bobmazzo1234' 
   AND id = 1441288790

The SQL Server error message is:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '{'.

In the angular directive code, the item object currently gets saved to localStorage as follows.

 save: function (widgets) {
   //'widgets' are mapped to the 'serialized' var using _.map()
   item = JSON.stringify(item);
   this.storage.setItem(this.id, item);
 }

I'm now wiring up an http request, which will call down into a c# API layer using MS WebAPI.

The problem here is that I'm simply trying to manually update the SQL Server table so I can test the retrieve functionality.

rwking · Accepted Answer · 2015-09-04 14:44:37Z

2

I think it's because you have a couple of single quotes in the string. You have to escape them, and ironically, a single quote is the escape character.

Here's the slight modification I made:

Original: kendo.format('{0:C0}', value)

Modified: kendo.format(''{0:C0}'', value)

Try changing your string to this and see if it fixes the issue:

UPDATE [rz37883_SQLServer].[dbo].[RAGEDashboard]
   SET 
      image = 
'{"widgets":[{"title":"Bar Chart","name":"chart_bar","style":{},"size":{"width":"50%","height":320},"dataModelOptions":{"title":{"text":"Bar Chart","position":"bottom"},"legend":{"visible":true,"position":"top"},"seriesDefaults":{"type":"bar","stack":false},"series":[{"field":"field1","name":"MTM"}],"dataSource":{"data":[{"field1":236151654.592439},{"field1":103612357.347808},{"field1":267066579.129913},{"field1":582355005.154486},{"field1":-9422699.958631}],"table":null},"valueAxis":{"labels":{"format":"{0:c}","rotation":-30},"line":{"visible":false},"axisCrossingValue":0},"categoryAxis":{"categories":["London","New York","Dubai","Paris","Stockholm"],"majorGridLines":{"visible":false},"labels":{"rotation":0,"margin":20}},"tooltip":{"visible":true,"template":"#= series.name #: #= kendo.format(''{0:C0}'', value) #"},"dimensions":[{"dimension":"BookingLocation","hierarchy":false,"id":0}],"riskMeasures":[{"name":"MTM","kri_group":"[MTM:MTM]","cube_vector":"MTM:MTM","aggreg_formula":"SUM(MTM:MTM)","id":0}]},"initImage":"images2/bar_chart.png","gadgetConfigured":true}]}'
WHERE [RAGEDashboardConfig_userid]='bobmazzo1234' and id = 1441288790

answered Sep 4, 2015 at 14:44

rwking

1,0326 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bob.mazzo Over a year ago

oh so a JavaScript str.replace() function, for example ? or str.replace(/'/g, "\\'");

rwking Over a year ago

I think that could work! Just as long as you don't let it modify the leading and trailing single quotes.

Collectives™ on Stack Overflow

Issue saving a JSON string to a SQL Server database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related