[AccessD] Building SQL statement

John,

Perhaps the SQL statement could be rewritten to use parameters,
something like:

PARAMETERS WebNewsSourceID Long, Link Text ( 255 ), 
    Title Text ( 255 ), ArticleDescription Text ( 255 );
INSERT INTO WebNewsItem ( WebNewsSourceID, Link, Title,
ArticleDescription )
SELECT [WebNewsSourceID] AS WebNewsSourceID, [Link] AS Link, 
    [Title] AS Title, [ArticleDescription] AS ArticleDescription;

so that you could create and execute a querydef where you could fill in
its parameters without worrying about unexpected string termination.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> John W. Colby
> Sent: Wednesday, August 24, 2005 11:23 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Building SQL statement
> 
> 
> I'm attempting to build a SQL statement to append data into a 
> record.  One
> of the fields is text, including (possibly) any valid text 
> including ' - for
> example in contractions such as "aren't" etc.
> 
> My SQL statement is building up a statement in the format:
> 
>             lstrSQL = "INSERT INTO WebNewsItem ( 
> WebNewsSourceID, Link,
> Title, ArticleDescription ) " & _
>                         "SELECT " & webNewsSourceID & " AS 
> WebNewsSourceID,
> '" & link & "' AS Link, '" & title & "' AS Title, '" & 
> description & "' AS
> ArticleDescription;"
> 
> The problem is that description can contain the ' character 
> and thus I end
> up with an invalid SQL statement since the ' embedded in 
> description "looks
> like" the ' that is supposed to enclose the text.
> 
<snip>
> 
> John W. Colby
> www.ColbyConsulting.com 
> 
> Contribute your unused CPU cycles to a good cause:
> http://folding.stanford.edu/