Text Delimiters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nigel Vandyk - 16 Apr 2007 21:58 GMT

I am sure that lots of people must know the answer to this but I can't find
it anywhere.

If I want to insert a Firstname and Surname into an SQLCE table called
Clients I would use

INSERT INTO Clients (Firstname,Surname) VALUES('Fred','Bloggs')

but what happens if I want to insert Paddy O'Brian or Michael O'Connor

Because we are using an apostrophe in their names, this will cause the
Insert statement to fail

In Access I would use chr(34) to delimit the strings. Is there a way round
this in SQLSERVERCE?

Signature

With best wishes

Nigel Vandyk

Website:www.astarsoftware.co.uk

Reply to this Message

William (Bill) Vaughn - 16 Apr 2007 22:24 GMT

This problem can be solved by either doubling the single quotes as in
... 'O''Malley',...
or (better yet) use Parameters for your input parameters. These solve other
framing issues (as when using dates etc.)

hth

Signature

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

>I am sure that lots of people must know the answer to this but I can't find
>it anywhere.
[quoted text clipped - 11 lines]
> In Access I would use chr(34) to delimit the strings. Is there a way round
> this in SQLSERVERCE?

Reply to this Message

Nigel Vandyk - 16 Apr 2007 22:40 GMT

Thanks very much for that, Bill

Signature

With best wishes

Nigel Vandyk

Website:www.astarsoftware.co.uk

> This problem can be solved by either doubling the single quotes as in
> ... 'O''Malley',...
[quoted text clipped - 18 lines]
>> In Access I would use chr(34) to delimit the strings. Is there a way
>> round this in SQLSERVERCE?

Reply to this Message

Adam - 16 Apr 2007 22:57 GMT

Dnia Mon, 16 Apr 2007 21:58:07 +0100, Nigel Vandyk napisał(a):

> I am sure that lots of people must know the answer to this but I can't find
> it anywhere.
> In Access I would use chr(34) to delimit the strings. Is there a way round
> this in SQLSERVERCE?

Use Parameters

SQL = "INSERT INTO USERS (name, surname) VALUES (?, ?)";
zapytanieSQL = new SqlCeCommand(SQL, polaczenie);
zapytanieSQL.Parameters.Add(new SqlCeParameter("name",
SqlDbType.nvarchar));
zapytanieSQL.Parameters.Add(new SqlCeParameter("surname",
SqlDbType.nvarchar));
zapytanieSQL.Prepare();
zapytanieSQL.Parameters["name"].Value = your string
zapytanieSQL.Parameters["surname"].Value = your string
zapytanieSQL.ExecuteNonQuery();
zapytanieSQL.Parameters.Clear();

Reply to this Message