[AccessD] Building SQL statement

Darn!  The code got mangled and wrapped.  Lets try it again ..
.
Dim bld As New System.Text.StringBuilder

bld.Append("INSERT INTO WebNewsItem ( WebNewsSourceID, Link, Title,
ArticleDescription ) ") 

bld.AppendFormat(" "SELECT {0} AS WebNewsSourceID, ", webNewsSourceID) 

bld.AppendFormat("'{0}' AS Link, ", link) 

bld.AppendFormat("'{0}' AS Title, ", title) 

bld.AppendFormat("'{0}' AS ArticleDescription;",
description.Replace("'", "''"))

lstrSQL = bld.ToString


Charlotte


-----Original Message-----
From: Charlotte Foust 
Sent: Friday, August 26, 2005 9:19 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Building SQL statement


John,  

You might have some cleaner, easier to read code if you made use of a
StringBuilder in .Net.  Once you get the hang of them, you never revert
to VB style concatenation again.

Dim bld As New System.Text.StringBuilder

bld.Append("INSERT INTO WebNewsItem ( WebNewsSourceID, Link, Title,
ArticleDescription ) ") bld.AppendFormat(" "SELECT {0} AS
WebNewsSourceID, ", webNewsSourceID) bld.AppendFormat("'{0}' AS Link, ",
link) bld.AppendFormat("'{0}' AS Title, ", title)
bld.AppendFormat("'{0}' AS ArticleDescription;",
description.Replace("'", "''"))

lstrSQL = bld.ToString


Charlotte


-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com] 
Sent: Friday, August 26, 2005 7:55 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Building SQL statement


I thank everyone for their solutions.  In fact this is in VB.Net.  Fir
this particular problem I think the easiest solution will be to open a
recordset and place the string into a specific field.  I do understand
the issue though and the solution if I do want to build a SQL statement.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Friday, August 26, 2005 10:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Building SQL statement


John,

    Add-In named  SQLToVBAString_ver2.zip, available in folder
0_Utilities And Add-Ins, in files section of Yahoo group named
MS_Access_Professionals might be of interest to you.

    Once installed, it coverts an SQL statement into VBA string (duly
taking care of extra quotes if any) and copies the contents to the
clipboard for direct use in VBA editor.

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: John W. Colby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, August 25, 2005 08:53
  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.

  I can't for the life of me remember how I am supposed to handle this.

  I could punt and open a recordset, create a new record, and place the
data
  directly into the field, which I may end up doing but I sure would
prefer to
  just do the "append query" thing.  It is (was) much simpler.

  John W. Colby
  www.ColbyConsulting.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com