By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Quicker/automated way to process single quotes in an INSERT statement?

P: n/a
I have several tables with quite a few fields and I'm getting errors when
trying to insert records with single quotes in the data like: name = John
O'Henry or a city name of O'Fallen

So I went ahead and added a replace to replace the ' with " but now other
fields are having the same problem and there are multiple fields involved.
This data gets into having a lot of symbols used, etc.

So rather than go through and replace these quotes in each and every field
in each table is there a quick or automated way to do this in access/vb with
maybe a single line of code or perhaps a quick way to add it in the
querybuilder?

The code in VB is so clean right now and adding all these replaces and
changing variable names will take forever and make a mess of the code. Any
suggestions?
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you try to use parameters instead of string insertions, you will avoide
these and some other types of problems.

On Wed, 11 Aug 2004 08:40:31 -0500, "Jason" <ja***@email.net> wrote:
I have several tables with quite a few fields and I'm getting errors when
trying to insert records with single quotes in the data like: name = John
O'Henry or a city name of O'Fallen

So I went ahead and added a replace to replace the ' with " but now other
fields are having the same problem and there are multiple fields involved.
This data gets into having a lot of symbols used, etc.

So rather than go through and replace these quotes in each and every field
in each table is there a quick or automated way to do this in access/vb with
maybe a single line of code or perhaps a quick way to add it in the
querybuilder?

The code in VB is so clean right now and adding all these replaces and
changing variable names will take forever and make a mess of the code. Any
suggestions?


Nov 13 '05 #2

P: n/a
I'm not following :(
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:3v********************************@4ax.com...
If you try to use parameters instead of string insertions, you will avoide
these and some other types of problems.

Nov 13 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, you only have to check/replace on the fields (aka columns) that
will probably/possibly have single-quotes. You can do that in the
queries you write using the Replace() function (Access 2K & above). By
the way you don't replace a single-quote w/ a double-quote, you just
double the same quote. E.g.:

Say the variable str = "O'Henry" you'd use Replace(str, "'", "''").
The results of this Replace() would be "O''Henry." If this value was
saved to a Text column it would be saved as O'Henry. The db
automatically converts the double single quotes to one single-quote. In
SQL:

PARAMETERS [Which Last Name] Text;
SELECT LastName
FROM Customers
WHERE LastName = Replace([Which Last Name], "'", "''")

PARAMETERS [Which Last Name] Text;
INSERT INTO Customers (LastName)
VALUES (Replace([Which Last Name], "'", "''")

You could even create a function that would replace the single-quote &
call that function from SQL (or wherever needed). E.g.:

PARAMETERS [Which Last Name] Text;
INSERT INTO Customers (LastName)
VALUES (FixQuotes([Which Last Name]))

Public Function FixQuotes(str As String) As String
FixQuotes = Replace(str, "'", "''")
End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRqDGYechKqOuFEgEQJL8ACgxBLM6pT3V2knbyrxOT2SeU nuWC8AnAwH
reUJxdavEjRY+hqajOuKXe53
=toK3
-----END PGP SIGNATURE-----
Jason wrote:
I have several tables with quite a few fields and I'm getting errors when
trying to insert records with single quotes in the data like: name = John
O'Henry or a city name of O'Fallen

So I went ahead and added a replace to replace the ' with " but now other
fields are having the same problem and there are multiple fields involved.
This data gets into having a lot of symbols used, etc.

So rather than go through and replace these quotes in each and every field
in each table is there a quick or automated way to do this in access/vb with
maybe a single line of code or perhaps a quick way to add it in the
querybuilder?

The code in VB is so clean right now and adding all these replaces and
changing variable names will take forever and make a mess of the code. Any
suggestions?


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.