[AccessD] FW: CodeL

Gustav:

IIUC, the data already has an occurrence of two single quotes embedded in
it.  And I think he wants it to go into the db that way.  

He gets an error whether he uses single quotes or a pair of double quotes in
the SQL statement to delimit the string that goes into the SQL table.

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 11:43 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] FW: CodeL

Hi Rocky

Yes, it will replace any single-quote with two single-quotes.

They are "allowed" but when building a string like your friend does, a
single single-quote or double-quote will jam the code:

"Set [Memo] = 'O'Hare' " & _

/gustav


>>> rockysmolin at bchacc.com 07-03-2008 20:19 >>>
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