quotation marks in xml

I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.

this section shows the problem, i am loading xml from a file and trying to insert this into the sql database

XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));
reader.WhitespaceHandling = WhitespaceHandling.None;
XmlDocument xmlDocF = new XmlDocument();
xmlDocF.Load(reader);
this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;
this.SqlDataSource1.Update();
reader.Close();

pls help

Can you post some part of xml?

|||

<_n0011 HyperLink="undefined" Welcome="A'hneiv zipv meih" Language="undefined" country="undefined" x="1329" y="338"/>

this is an example.

there are about 400 similar tags with about 30 instances of the single quote mark. this may change in the future as i am trying to make the xml editable.

|||

I believe using Parameters will take care of the escaping automatically.

|||

I have tried the following syntax and got the same problems. I think this type of parameterising just parses the text into the string and produces effectively the same problem. Is there a different type of parameterising that I should be using.

this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] @.xmlDocF.InnerXml where [index] = 1" ;

I am a bit new to this subject and have looked for an article on this subject but not been able to find anything that deals with this specific requirement.

many thx for your assistance.

|||

what kind of parameter method did u have in mind?

|||

I created a DataSource with insert/update/delete commands and connected it to a GridView.

In edit mode, I entered this in the AVarCharField: Hello, "test" 'test'

When I clicked update, it worked.

The UpdateCommand looks like this:

UpdateCommand="UPDATE [TestTable] SET [CustomerName] = @.CustomerName, [Status] = @.Status, [AVarCharField] = @.AVarCharField WHERE [ID] = @.ID">

The Parameters look like this:

<UpdateParameters> <asp:Parameter Name="CustomerName" Type="String" /> <asp:Parameter Name="Status" Type="Single" /> <asp:Parameter Name="AVarCharField" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /></UpdateParameters>

I hope that helps.

Quotation Marks in SQL Server

I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website.

The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.

What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')

If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.

Try the links below for all the info you need including how to enable QUOTED_IDENTIFIER option in your create database statement and the restrictions. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

http://msdn2.microsoft.com/en-US/library/ms176027.aspx

|||

Search the forums for:

A) Parameterized SQL Query (What you should do)

B) SQL String concatenation (What you are probably doing)

C) SQL Injection attack (The security problems of doing B instead of A)

|||

Motley:

Search the forums for:

A) Parameterized SQL Query (What you should do)

B) SQL String concatenation (What you are probably doing)

C) SQL Injection attack (The security problems of doing B instead of A)

Not worried about SQL Injection attacks. This is an intranet app.|||

Caddre:

Try the links below for all the info you need including how to enable QUOTED_IDENTIFIER option in your create database statement and the restrictions. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

http://msdn2.microsoft.com/en-US/library/ms176027.aspx

Can you enable the Quoted_Identifier only when you create a new table?|||You can do it in your create database statement or create table, the how for table is covered in the second link. Hope this helps.|||After reading the replies and links that were posted, I feel that I need to reiterate my question.

There is a page that displays records from a SQL Server DB. These records are "announcements" on an internal bulletin board. An admin has a special page that gives the admin the ability to edit, add or delete any of these records.

For instance, an admin can add an announcement (using a textbox) that says, 'This sentence has "Quotation Marks" in it'. I want to be able to search that specific phrase for the quotation marks and replace them with the appropriate code so that they may be displayed on the bulletin board. I don't want the admin to have to type double quotes or double apostrophes in order for them to show up.

The page needs to be user friendly with any concates or alterations to occur server side.

So if anyone can tell me the proper way of say

if str.chars(x) = "<quotation>" then ...

that would be much appreciated.

This is the javascript that generates the pop-up window:
Alert Descrip

<script language="javascript">
//popup function which recieves the email group description and name as parameters
function popitup3(description, name)
{
newwindow2=window.open('','name','height=400,width=600,scrollbars=yes');
var tmp = newwindow2.document;
tmp.write('<html><head><title>Alert Description</title>');
tmp.write('</head><body><font face="verdana, tahoma, sans-serif" size="2"');
tmp.write('b><br><p align="justify">');
tmp.write(description);
tmp.write('</p><p><a href="javascript:self.close()">close</a> this window.</p>');
tmp.write('</body></html>');
tmp.close();
}
</script>

Variable description is where the text with the quotes would most likely be.|||

I am sorry I did not understand your original post you are looking for ANSI SQL LIKE and Pattern search. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp

Quotation marks

Hi
Can someone tell me what i am doing wrong below:
--
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe'+"'"+'s')
select * from Tb1
Drop table Tb1
--
I would like the 2nd column to appear as Joe's in the resultset.
Thank you in advanceTry this:
INSERT INTO Tb1 VALUES('7', 'Joe''s';
HTH
Vern
"MittyKom" wrote:

> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Oops, forgot the closing parenthesis:
INSERT INTO Tb1 VALUES('7', 'Joe''s');
"Vern Rabe" wrote:
> Try this:
> INSERT INTO Tb1 VALUES('7', 'Joe''s';
> HTH
> Vern
> "MittyKom" wrote:
>|||escape single quote with a single quote
like
'joe''s'
(P.S: that not a double quote, its 2 single quotes :)|||Hi MittyKom
There is no need for any concatenation of strings. If you use two single
quotes inside outer single quotes, it is interpreted as one single quote in
the string.
So your use of concatenation is unnecessary but your use of the double
quotes (") is incorrect. Most interfaces have a setting called
QUOTED_IDENTIFIER set to on, which means that double quotes are used only to
delimit identifiers, and not user data. So the message you are receiving
refers to the fact that your single quote inside the double quotes is being
interpreted as an identifer, and it makes no sense.
So the cleanest solution is to just make it all one string to insert into
the second column, with the two adjacent single quotes getting interpreted
as one single quote in the string.
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe''s')
select * from Tb1
Drop table Tb1
The other solution is to SET QUOTED_IDENTIFIER OFF, and then your original
solution will work (but if you leave it on, other things might break)
HTH
Kalen Delaney, SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Thank you so much Vern and Omnibuzz.
"Omnibuzz" wrote:

> escape single quote with a single quote
> like
> 'joe''s'
> (P.S: that not a double quote, its 2 single quotes :)
>|||I use char(39) I think..
Insert Into Emp (LastName) Values ("O" + char(39) + "clock") -- O'clock
something like that.
I know there are quotes/inside other quotes methods, but those sometimes
come back to haunt me, since I deal with client's databases that I don't
have full control over.
..
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance