[AccessD] FW: CodeL

Mark:

I'm using apostrophe and single quote interchangeably.

The data contains an occurrence of a double apostrophe.  

Whether he delimits it in the SQL statement with a double quote (by using a
double double quote) or an apostrophe, he gets an error.

Regards,

Rocky





 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Friday, March 07, 2008 11:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FW: CodeL



Is there a difference between and apostrophe and a single quote?

I think the delimeter is the problem...you are using a delimeter...that is
also text in your data?

Mark A. Matte


> From: rockysmolin at bchacc.com
> To: accessd at databaseadvisors.com
> Date: Fri, 7 Mar 2008 11:19:30 -0800
> Subject: Re: [AccessD] FW: CodeL
>
> Gustav:
>
> Within the data that he's trying to insert into the SQL table there is 
> a pair of apostrophes. Will your code have the effect of replacing 
> them? I'm not sure he wants to replace the apostrophes with quote 
> marks. Are apostrophes not allowed in a SQL character field?
>
> Thanks and regards,
>
> Rocky
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav 
> Brock
> Sent: Friday, March 07, 2008 10:55 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] FW: CodeL
>
> Hi Rocky
>
> It is probably double single-quotes and not single double-quotes that 
> are needed.
> Try with:
>
> "Set [Memo] = '" & Replace(vMemo, "'", "''") & "' " & _
>
> /gustav
>
>
>>>> rockysmolin at bchacc.com 07-03-2008 19:44>>>
> Dear List:
>
> Client (see below) is connecting to a SQL back end with ADO (he's 
> doing his own programming and calling me occasionally for help).
>
> When trying to update a memo field with embedded apostrophes he get a 
> syntax error. He says that when he connected with ODBC he didn't have the
problem.
>
> The code below errors on the Conn.Execute with "incorrect syntax".
>
> I told him to change the apostrophe delimiters around vMemo to double 
> quote marks. Then he gets an error which says 'can't be longer than 
> 128 characters. Memo field is defined as nvarChar 4000.
>
> I'm pretty weak in SQL and ADO. Is there a solution to this?
>
> MTIA
>
> Rocky
>
> 'ADO connection to MS Sql:
>
> vMemo is a declared Variant that is loaded with the memo filed and 
> then when dirty, I am attempting to update the SQL back end. CLID is 
> the integer ID number of the current client
>
> Conn.Execute "UPDATE tClients " & _
> "Set [Memo] = '" & vMemo & "' " & _
> "WHERE ClientIndex=" & CLID & ""
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.516 / Virus Database: 269.21.6/1316 - Release Date: 
> 3/6/2008
> 6:58 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Shed those extra pounds with MSN and The Biggest Loser!
http://biggestloser.msn.com/
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.516 / Virus Database: 269.21.6/1316 - Release Date: 3/6/2008
6:58 PM