GetString problem with single quotes

[theniteowl]

Hi All,

I am using GetString to pull records from a table and dump them into a Javascript array.
Existing text data in one of the table fields has single quotes which is causing my Javascript to bomb when initializing the array.

How can I handle the single quotes and still use GetString without modifying the data to remove single quotes?

Here is my code.

  SQLC = "SELECT * FROM Service_RequestType WHERE dep_id='" & CampusID & "' ORDER BY request_desc"
  set rsl=mCN.execute(SQLC) 
    If rsl.EOF AND rsl.BOF Then
      'No record exists so set default value for array
	  outstr = ""
    Else
      rsstring = rsl.GetString(,,"','","','","")
	  outstr = "'" & Left(rsstring,len(rsstring)-2)
	End If
    Response.Write "<SCR" & "IPT TYPE=""text/JavaScript"">" & vbCrlf
	Response.Write "var arrDesc = new Array(" & outstr & ");" & vbCrLf
    Response.Write "</SCR" & "IPT>" & vbCrlf
  rsl.Close
  Set rsl = Nothing

At my age I still learn something new every day, but I forget two others.

 

[kss444]

Not sure what you are doing with Getstring I have never used it. But here's a link when I googled Getstring.

http://www.4guysfromrolla.com/webtech/121598-1.shtml

 

[theniteowl]

GetString is not the problem, it is a matter of escaping the quotes or otherwise converting them so they are not interpreted literally causing the javascript string to terminate early.

I am using single quotes to surround the values in the output creating the javascript array.
I can get around this by using double quotes instead but what happens if the text contains a double quote rather than single?

There must be a way to automatically escape any quotes contained within the data returned from the table, I just have to find out what the best method is. Otherwise I could just iterate through the table grabbing and dealing with all values directly but that is not efficient.

GetString returns all results from the query in a single formatted string with delimiters set as parameters in the command so my command above will give me every field from every matching column with each wrapped in single quotes and separated by commas.


At my age I still learn something new every day, but I forget two others.

 

[Sheco]

any quotes contained within the data returned from the table

Have you tried using the Replace() function to add the backslash escape character like this:
[tt]
dim strTest
strTest = "I'm a string"
strTest = Replace(strTest, "'", "\'")
[/tt]

 

[theniteowl]

I have not primarily because the data is returned in one long formatted string with all quotes already applied and there would be no way to distinguish between legitimately formatted quotes and those from within the data.

I could use a substitute character in place of the quotes during formatting with the GetString command and then Replace to escape quotes, then again to replace the substituted characters with quotes.
I had thought there might be a way within the SQL query to handle the escaping so I did not have to take all the extra steps.


At my age I still learn something new every day, but I forget two others.

 

[Sheco]

OK if you are using SQLServer then it has its own TSQL version of the Replace() function.

[tt]
SELECT Replace('Roses are blue ...', 'blue', 'red') as 'Poem'
[/tt]

 

[theniteowl]

OK if you are using SQLServer then it has its own TSQL version of the Replace() function.


SELECT Replace('Roses are blue ...', 'blue', 'red') as 'Poem'

That's what I was looking for. I thought there should be a way of handling it from the query. Just have to figure out the minor intracacies of quoting out the query.
Funny how I have to battle embeded quotes in order to overcome an embeded quote problem.

At my age I still learn something new every day, but I forget two others.

 

[theniteowl]

EEEK!
Not getting it so far.
Replace apparently does not allow using a column name.

Is it possible to select from a select?
I am trying to use an inner select statement to pull the data storing it under another name then use the outer select to do the replace on that field.
This is more sophisticated query than I am used to working with and I have no idea of the correct syntax.

At my age I still learn something new every day, but I forget two others.

 

[Tarwn]

Replace apparently does not allow using a column name.

err..yes it does? Paste your SQL, lets see if maybe an extra set of eyes will see something.

 

[theniteowl]

Well, I have been through so many variations of the string I do not remember which gave me which particular error.

I think it was this line:

SQLC = "SELECT request_id, Replace(request_desc, ""'"", ""\'"") as ''myDesc'', dep_id, active FROM Service_RequestType WHERE dep_id='" & CampusID & "'"[code]

It returns error:
Microsoft OLE DB Provider for SQL Server error '80040e14' 

Cannot use empty object or column names. Use a single space if necessary. 

I have currently worked around the problem doing this:
[code]
set rsl=mCN.execute(SQLC) 
  If rsl.EOF AND rsl.BOF Then
    'No record exists so set default value for array
    outstr = ""
  Else
    rsstring = rsl.GetString(,,"¦,¦","¦,¦","") 
    rsstring = Replace(rsstring, "\", "\\")
    rsstring = Replace(rsstring, "'", "\'")
    rsstring = Replace(rsstring, "¦", "'")
    outstr = "'" & Left(rsstring,len(rsstring)-2)
  End If
    Response.Write "<SCR" & "IPT TYPE=""text/JavaScript"">" & vbCrlf
    Response.Write "var arrDesc = new Array(" & outstr & ");" & vbCrLf
    Response.Write "</SCR" & "IPT>" & vbCrlf
  rsl.Close

I first set a high ascii character (chr 127) as the delimiters around the field, separated by commas.
Then I escape any backslashes, then single quotes.
Finally I replace the high ascii characters with single quotes for the Javascript array.

This way I do not have to worry about double quotes in the table data as I use none in the javascript array section for them to interfere with and the escape characters before the backslash and single quotes (inside the data field) prevent them from becoming a problem as the Javascript string is interpreted by the browser.

If you can see a cleaner way to do it though I would love to see it. But since I have to consider backslash, single and double quotes as possible problems I think doing it in the query might be overly complex. Previously I had only been considering dealing with single quotes and figured a query based solution might be easy.


At my age I still learn something new every day, but I forget two others.