Problem inserting a record where the string field contains an apostrophe in the value

Question

Post reply

Problem inserting a record where the string field contains an apostrophe in the value

Gary Rynearson

SSC Veteran

Points: 225
More actions
April 20, 2005 at 3:03 pm

#87294

OK this SQL statement works fine - notice the "PAID" value for the string does not contain an apostrophe, except as the string delimiters.
insert into wordMetadata (Page, [sequence], dleft, dTop, dRight, dBottom, word) values (1, 176, 297.961, 200.2125, 315.9505, 208.59, 'PAID')
This one, however has an apostrophe in the string and I am looking for advice on how to get it to work. These statements are constructed programatically and the values are read from an outside source.
insert into wordMetadata (Page, [sequence], dleft, dTop, dRight, dBottom, word) values (1, 179, 36, 211.32, 54.018, 218.022, 'ADD'L')
Can anyone show me how to insert into a string field, a value that contains an apostrophe?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can either create a stored proc (best solution) or replace the apostrophe with a double apostrophe

if you try this statement you'll see that it works just fine :

insert into wordMetadata (Page, [sequence], dleft, dTop, dRight, dBottom, word) values (1, 179, 36, 211.32, 54.018, 218.022, 'ADD''L')

exemple of a stored proc :

create procedure dbo.ProcName @Param1 as varchar(10)

AS

SET NOCOUNT ON

Insert into dbo.yourTable (VarcharField) values (@Param1 )

SET NOCOUNT OFF

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

If you use parameterized queries in asp.net you wouldnt have to do anything.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Meghana SSC Eights! Points: 840 More actions · Answer 3

If you dont want double apostrophe and want only a single apostrophe you can set the quoted_identifier off and get the required results

following is what you can do

SET QUOTED_IDENTIFIER OFF

GO

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME = 'Test')

DROP TABLE Test

GO

USE pubs

CREATE TABLE Test ( Id int, String varchar (30) )

GO

-- Literal strings can be in single or double quotation marks.

INSERT INTO Test VALUES (1,"'Text in single quotes'")

INSERT INTO Test VALUES (2,'Text with 2 '''' single quotes')

INSERT INTO Test VALUES (3,'"Text in double quotes"')

INSERT INTO Test VALUES (4,"""Text in double quotes""")

INSERT INTO Test VALUES (5,"Text with 2 """" double quotes")

insert into test values (6,"ADD'L")

GO

SET QUOTED_IDENTIFIER ON

GO

INSERT INTO "Test" VALUES (7,'Text with a single '' quote')

GO

SELECT *

FROM Test

GO

Drop TABLE Test

GO

Regards

Meghana

Regards,

Meghana

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

SET QUOTED_IDENTIFIER OFF works fine in this example - as long as you can guyarantee that you won't be getting strings with double quotes! If so, you need to check for single or double quotes and treat each differently. If you get both, you have a problem!

I would always go for a stored procedure if possible on grounds of :

Efficiency

Security

Protection against injection attacks

Maintainability

Easier transactional integrity

... and it won't care what characters you pass in its parameters (except null - ascii zero - which gets interpreted as end of string)

Gary Rynearson SSC Veteran Points: 225 More actions · Answer 5

Thank you all for your input - very insightful and helpful. Here is what I tried and what worked.

First I tried using the "Set Quoted_Identifier Off" approach, which works well inside SQLServer Query Analyzer, but I could not get it to work in my code - probably because I could not issue a two-statement SQL statement to the command.ExecuteNonQuery method in .Net.

Next, I decided embark into teritory I have never ventured, stored procedures. After getting it to work in SQLServer Query Analyzer with only minor difficulties, I then implemented it in my code and again after fixing some syntax / parameter setup problems, I was able to get it to work!

For those who may be interested, I have pasted the stored procedure and C# code below:

C#

SqlCommand myCommand = new SqlCommand ();

myCommand.Connection = cn;

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.CommandText = "InsertWordMetadata";

SqlParameterCollection myParams = myCommand.Parameters;

myParams.Add ("@Page", SqlDbType.Int ).Value = addWordInfo.Page;

myParams.Add ("@wSequence", SqlDbType.Int ).Value = addWordInfo.Sequence;

myParams.Add ("@dLeft", SqlDbType.Decimal ).Value = addWordInfo.Left;

myParams.Add ("@dTop", SqlDbType.Decimal ).Value = addWordInfo.Top;

myParams.Add ("@dRight", SqlDbType.Decimal ).Value = addWordInfo.Right;

myParams.Add ("@dBottom", SqlDbType.Decimal ).Value = addWordInfo.Bottom;

myParams.Add ("@Word", SqlDbType.NChar, 50 ).Value = addWordInfo.Word;

cn.Open();

int

numAffectedRows = myCommand.ExecuteNonQuery ();

cn.Close();

Stored Procedure

CREATE PROCEDURE InsertWordMetadata @Page int, @wSequence int,
@dLeft numeric(18,3), @dTop numeric(18,3),
@dRight numeric(18,3), @dBottom numeric(18,3),
@Word char(50)
AS
INSERT INTO WordMetadata (Page, wSequence, dLeft, dTop, dRight, dBottom, Word)
VALUES (@Page, @wSequence, @dLeft, @dTop, @dRight, @dBottom, @Word)

Manish_ Ten Centuries Points: 1070 More actions · Answer 6

I feel no need of any stored procedure at all. and even no need of doing any "Set Quoted_Identifier Off" ".

I have done the same thing by the following way on Query Analyzer !

insert into wordMetadata (Page, [sequence], dleft, dTop, dRight, dBottom, word) values (1, 179, 36, 211.32, 54.018, 218.022, 'ADD''L')

And if you required this same from any front end tool like VB/Vb.net/C#

then do by this following way ( Assume that the value ADDL is on Text box)

LCLSQLSTR="insert into wordMetadata (Page, [sequence], dleft, dTop, dRight, dBottom, word) values (1, 179, 36, 211.32, 54.018, 218.022, '" & textbox1.text.replace("'","''") & "')"

Thanks & Regards

Manish Kaushik

- Manish

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Please keep using the sp, dynamic sql shouldn't be used in prodution especially with strings. Read this for more info :

The Curse and Blessings of Dynamic SQL

Gary Rynearson SSC Veteran Points: 225 More actions · Answer 8

This solution is exactly what I was looking for ... originally. I have implemented your code and it works, as you said, without using the stored procedure.

I think this discussion is so good, I figured I would test the performance of the SQL Insert verses that of the Stored Procedure (SP). Below are the results. Be aware that the table was truncated before each test. For example with test number 1 the table was truncated before the SQL Insert test and before the SP test. Also, the SQL Server instance is local to my workstation, so their should be no question about bandwidth and someone outside this experiment taking up the bandwidth. Really the only processing being performed on my local machine is some .Net code, SQL Server, and Microsoft Word.

Test Number

SQL Insert

Stored Procedure

Number of Writes

1

01.9677224

01.8472496

3156

2

01.8874072

01.8472496

3156

3

01.9476436

01.8773678

3156

4

31.9179775

26.4594330

46065

5

27.0767675

26.8167025

46065

6

27.5468850

26.3679080

46065

7

06:29.5346552

06:14.0346944

615220

Nunit test code is below. Note that the two tests are basically the same except for the "myWriter.ExecuteNonQuery () method. Its implementation is overloaded: one takes an sql string and passis it using to a sqlcommand of type "text", the other accepts an argument of a class (structure really) that has all the parameters used to build the stored procedure (see previous post) for a sqlcommand object whose type is "storedProcedure".

[Test] public void storePageWordsSQL ()

{

Xpdf.PdfReader myPdfReader;

myPdfReader = new PdfReader

(@"_allCcerts.pdf");

int numPages = myPdfReader.NumPages;

string strSQL; /* = "insert into wordMetadata " +

"(Page, [sequence], dleft, dTop, dRight, dBottom, word) values " +

"({0), {1}, {2}, {3}, {4}, {5}, {6})"; */

long numRecsAdded = 0;

DateTime startTime = DateTime.Now;

for (int i = 0; i < numPages /*&& i < 100*/; i++)

{

//myPdfReader.setCurrentPage (2);

WordInfo[] myWordInfo = myPdfReader.createWordAndDataList(i+1);

//myWriter.ExecuteNonQuery ("truncate table wordMetadata");

foreach (WordInfo wInfo in myWordInfo)

{

//myWriter.ExecuteNonQuery (wInfo);

strSQL = "insert into wordMetadata (Page, Wsequence, dleft, dTop, dRight, dBottom, word) values " +

"(" + wInfo.Page + ", " + wInfo.Sequence + ", " + wInfo.Left + ", " +

wInfo.Top + ", " + wInfo.Right + ", " + wInfo.Bottom + ", '" +

wInfo.Word.Replace ("'","''") + "')";

//Console.WriteLine (strSQL);

myWriter.ExecuteNonQuery (strSQL);

}

numRecsAdded += myWordInfo.Length;

}

DateTime timeFinish = DateTime.Now;

Console.WriteLine ("SQL String - added " + numRecsAdded + " records / time : " + timeFinish.Subtract(startTime));

}

[Test] public void storePageWordsSP ()

{

Xpdf.PdfReader myPdfReader;

myPdfReader = new PdfReader

(@"_allCcerts.pdf");

//_allCcerts.pdf

// smallExtract.pdf

int numPages = myPdfReader.NumPages;

long numRecsAdded = 0;

DateTime startTime = DateTime.Now;

for (int i = 0; i < numPages /*&& i < 100*/; i++)

{

//myPdfReader.setCurrentPage (2);

WordInfo[] myWordInfo = myPdfReader.createWordAndDataList(i+1);

//myWriter.ExecuteNonQuery ("truncate table wordMetadata");

foreach (WordInfo wInfo in myWordInfo)

{

myWriter.ExecuteNonQuery (wInfo);

}

numRecsAdded += myWordInfo.Length;

}

DateTime timeFinish = DateTime.Now;

Console.WriteLine ("Stored Procedure - added " + numRecsAdded + " records / time : " + timeFinish.Subtract(startTime));

}