I am having a problem in inserting and updating fields if a string 
variable contains text which has a single quotation.  It is throwing off 
my SQL statements. Could someone please advise on a way to avoid this 
problem. - I noticed in the search engine on this site that "'" seems to 
be an "ignored" word, so when it is enterd an error also appears.

This is a sample of the code 
<%

DIM strToday
strToday = DATE

SQL = "INSERT INTO ClientList"
SQL = SQL & " (ClientFirstName, ClientLastName, CompanyID, ClientDept, "
SQL = SQL & "ClientEmail, ClientWorkPhone, ClientWorkExtension) "
SQL = SQL & "VALUES ('john','Oconner',3,'dept','email@e...','454-
3444',343)"

Connection.Execute(SQL)
%>


I receive the following error if the Oconner variable is in the 
format O'conner.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) 
in query expression ''O'conner',3,'dept','email@e...','454-3444',343)'. 

/testasp/add_client.asp, line 340 


Could someone please advise on how to avoid this problem?
Thanks
Greg

Hi Greg,

In SQL single quotes denote the start or end of a string. On your asp page
prepare your string data types by replacing the single quote with a double
single quote. SQL will treat this as an escaping of the single quote. In
Here is an example.

Replace(ClientLastName,"'","''")

Ethan

-----Original Message-----
From: Greg Haywood [mailto:haywood@w...]
Sent: Thursday, April 25, 2002 10:00 PM
To: Access ASP
Subject: [access_asp] Adding or Updating with '

I am having a problem in inserting and updating fields if a string
variable contains text which has a single quotation.  It is throwing off
my SQL statements. Could someone please advise on a way to avoid this
problem. - I noticed in the search engine on this site that "'" seems to
be an "ignored" word, so when it is enterd an error also appears.

This is a sample of the code
<%

DIM strToday
strToday = DATE

SQL = "INSERT INTO ClientList"
SQL = SQL & " (ClientFirstName, ClientLastName, CompanyID, ClientDept, "
SQL = SQL & "ClientEmail, ClientWorkPhone, ClientWorkExtension) "
SQL = SQL & "VALUES ('john','Oconner',3,'dept','email@e...','454-
3444',343)"

Connection.Execute(SQL)
%>


I receive the following error if the Oconner variable is in the
format O'conner.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression ''O'conner',3,'dept','email@e...','454-3444',343)'.

/testasp/add_client.asp, line 340


Could someone please advise on how to avoid this problem?
Thanks
Greg

www.adopenstatic.com/faq/80040e14.asp

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Greg Haywood" <haywood@w...>
Subject: [access_asp] Adding or Updating with '


: I am having a problem in inserting and updating fields if a string 
: variable contains text which has a single quotation.  It is throwing off 
: my SQL statements. Could someone please advise on a way to avoid this 
: problem. - I noticed in the search engine on this site that "'" seems to 
: be an "ignored" word, so when it is enterd an error also appears.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> www.adopenstatic.com/faq/80040e14.asp

Cheers
Ken

Thats great, thanks for your help Ken and Ethan