Insertion with single quotes problem

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it is okay. Is is a bug in sql 2005? Please help. Thanks.

blumonde

The best bet is to use parameters. Building the SQL String as you are can cause all sorts of problems.

Absent that, you need to "Escape" the apostrophe. SO, do the following...

string testme = "we don''t have anything";

Note there are 2 apostrophes in a row...

|||

douglas.reilly:

The best bet is to use parameters. Building the SQL String as you are can cause all sorts of problems.

Absent that, you need to "Escape" the apostrophe. SO, do the following...

string testme = "we don''t have anything";

Note there are 2 apostrophes in a row...

Hi Douglas,

The problem is that end-users write those statements and hit insert. And they don't type two apostrophes. I can't "escape" it. Thanks.

blumonde

|||

You then have two choices.

1.Use parameters.

2. Search the string for ' and replace it with '' (two apostrophes). Of course users are not going to use two apostrophes.

|||

douglas.reilly:

You then have two choices.

1.Use parameters.

2. Search the string for ' and replace it with '' (two apostrophes). Of course users are not going to use two apostrophes.

I will try using parameter first. Thanks.

blumonde

|||

Parameters did it for me. Thanks.

blumonde

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind Regards
For double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

inserting string with quotes

Hi Guys,
i want to insert a string for example 'abcd'edfg'gg'into a
table in sql server 2000.
but it is not working but giving the error
"not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are
not permitted."
will this require any sp_configue or db_option changes?
pls advice me.Use two single quotes instead of one like: 'abcd''edfg''gg'
Anith

inserting SQL statement in a table

Hi,
I want to insert an SQL statement in an sql table, i think i need an escape
character for the single quotes in statement?
declare @.statement varchar(255)
select @.statement = "select name from employees where fname = 'john' and
lname like 'chr%'"
insert into sqltable (str_text) values (@.statement)
If i use double quotes or braces in the statement, then i get an error.
Thanks in advance.
MAQMAQ
It's a good idea to use stored procedure that accepts parameters
Also , I hope you are aware of SQL injections, so with stored procedure you
will be more safely.
CREATE PROC spMyProc
@.fname VARCHAR(20),
@.lname VARCHAR(20)
AS
SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
+'%'
GO
EXEC spMyProc 'John','Braun'
"MAQ" <maq@.nos.pam> wrote in message
news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to insert an SQL statement in an sql table, i think i need an
escape
> character for the single quotes in statement?
> declare @.statement varchar(255)
> select @.statement = "select name from employees where fname = 'john' and
> lname like 'chr%'"
> insert into sqltable (str_text) values (@.statement)
> If i use double quotes or braces in the statement, then i get an error.
> Thanks in advance.
>
> MAQ
>|||Hi,
Thanks for the answer. I know i can do it very easily through stored
procedure. But the problem is that i am customising an existing very large
database. and they have all the SQL statements in a table. I only need to
modify some of those statements. Though i can do it through enterprise
manager directly, but I have to create an sql script which updates those
statements in the table.
/MAQ
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJ86wKmRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> MAQ
> It's a good idea to use stored procedure that accepts parameters
> Also , I hope you are aware of SQL injections, so with stored procedure
> you
> will be more safely.
> CREATE PROC spMyProc
> @.fname VARCHAR(20),
> @.lname VARCHAR(20)
> AS
> SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
> +'%'
> GO
> EXEC spMyProc 'John','Braun'
>
>
> "MAQ" <maq@.nos.pam> wrote in message
> news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> escape
>|||use double single quotes inside the string and single quotes for statement
itself:
select @.statement = 'select name from employees where fname = ''john'' and
lname like ''chr%'''
that will do the trick
Hope it helps
"Uri Dimant" wrote:

> MAQ
> It's a good idea to use stored procedure that accepts parameters
> Also , I hope you are aware of SQL injections, so with stored procedure yo
u
> will be more safely.
> CREATE PROC spMyProc
> @.fname VARCHAR(20),
> @.lname VARCHAR(20)
> AS
> SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
> +'%'
> GO
> EXEC spMyProc 'John','Braun'
>
>
> "MAQ" <maq@.nos.pam> wrote in message
> news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> escape
>
>