Problem when inserting double quotes into sql server

[Gill1978]

Hi!

I'm having problems with double quotes. In the first database when I insert data from a textarea box which contains double quotes i.e Competition of "Classical" state, when viewing the saved data in the page it causes the rest of the page after the quotes to break (I get a load of html tags in that box and the rest of the page does not show as if it's treating the quotes as part of the code).
In the second database when i've inserted the above data into the database when i retrieve the page to view it the text is cut off before the first double quotes i.e Competition of. But the full sentence is in the database.

Is this something to do with configuration settings on the database. Can anyone help?

Thanks
Julie

 

[MorganGreylock]

You have to escape the quotes in your sql statement. I'm not exactly sure how that happens with SQL Server, but having quotes in sql statements is sometimes a problem (with some exceptions). Look for some information on how to escape the
quotes... for example, in unix, you escape something by
putting a backslash in front of it. that tells the system
to ignore the character as a special character, and just keep
it as part of a string. For example,

How's it going?

would be
How\'s it going?

And if you needed to escape the question mark as well, it might look like this:

How\'s it going\?

Now I'm not sure if that will work with SQL Server, in fact
I doubt it will, but thats the general idea. Cold fusion
has functions to do this for single quotes (PreserveSingleQuotes) but I don't know about double quotes.

Hope that helps,

MG

 

[ecobb]

Since it's SQL Server, try using <cfqueryparam> in your query. Here is a param tag that I use and I've never had any problems with quotes in SQL Server: <cfqueryparam value=&quot;#PreserveSingleQuotes(Form.TextArea)#&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot;>

Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson