Problem with single quotes

Hi,
I've a column (type: varchar) in a table. I need to add single quotes in the
value while inserting the rows. But I get an error. Please see below:
create table #t1 (address varchar(20))
go
insert into #t1 values ('St John's street')
Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')
Since the database is on hosting providers server, I cannot change the
settings of the server. Please help me by providing any suitable solution.
Also, note that I've an ASP script which insert rows into the table.
Thanks in advance.
-VenkatHi Venkat,
To solve the problem you need to generate insert statement like following in
your ASP script:
insert into #t1 values ('St John''s street')
i.e. replace single quote ( ' ) by two single quotes ( '' )
Krish
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq#aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
the
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||Hi
A single quote within a string can be escapped with a second quote.
insert into #t1 (address) values ('St John''s street')
John
"G.V.Reddy" wrote:

> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in t
he
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>
>|||Thank you very much Krish and John.
Since the address is entered by the visitors on the web site, do we need to
check each and every value entered/inserted into the varchar field for the
single quotes? In case the answer is Yes, I think we can do it by writing a
function which replaces a single quote with adding another quote. Is there
any other simple method to integrate this functionality (escaping with
another single quote) into the ASP code?
Thanks in advance.
-Venkat
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
> the value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||G.V.Reddy wrote:
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we
> need to check each and every value entered/inserted into the varchar
> field for the single quotes? In case the answer is Yes, I think we
> can do it by writing a function which replaces a single quote with
> adding another quote. Is there any other simple method to integrate
> this functionality (escaping with another single quote) into the ASP
> code?
> Thanks in advance.
> -Venkat
>
Look into the use of "parameters". I don't know the exact details for *asp*,
but they are something like:
* provide placeholders in you sql string :
insert into #t1 values (?)
* create a parameter, fill it with the "plain" value ("St John's street"), n
o need
to escape quotes, then add that parameter to the command object
* execute the query
Hans Kesting|||Hi
For ASP/ADO check out the SQL Server samples
http://msdn.microsoft.com/library/d...
5ym.asp
John|||replace(strParam, "'","''")|||Yes, a function to "double up" the single quote items will help. It would be
even better to use parameterized command objects instead of concatenated SQL
strings for communicating with the database (I am guessing you are using ADO
in your ASP application). Building concatenated SQL strings leaves you
application open to SQL Injection attacks, which is a severe security issue.
For information on ADO Command objects and parameters, see:
http://msdn.microsoft.com/library/d...rsreference.asp
For information about SQL injection attacks, see:
http://search.microsoft.com/search/.../>
0&s=1&swc=0
http://www.google.com/search?hl=en&q=sql+injection
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:OxZF4q$aFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we need
to
> check each and every value entered/inserted into the varchar field for the
> single quotes? In case the answer is Yes, I think we can do it by writing
a
> function which replaces a single quote with adding another quote. Is there
> any other simple method to integrate this functionality (escaping with
> another single quote) into the ASP code?
> Thanks in advance.
> -Venkat
>
> "G.V.Reddy" <vreddyg@.go.com> wrote in message
> news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
below:
solution.
>
>sql