Bitten by the apostrophe/SQL bug

[Brian]

Well, I'm a dunce. I just ran into what has to be one of the oldest problems
around, and while I have a couple ways of getting past it, I'm interested in
the best or most efficient way.

I was using Docmd.RunSQL to insert records but forgot that some names have
apostrophes in them.

BatchID being Long, CustomerName being String, and Amount being Currency:

Dim strSQL as String
'fields to insert
strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
'insert this data
strSQL = strSQL & " SELECT " & BatchID & ",'" & CustomerName & "'," & Amount
Docmd.RunSQL strSQL

Of course, it worked fine for the Smiths but choked on O'Donnell. (This is
in the middle of a long loop through a recordset, not just a single record.)

Should I:

1. Use a query & pass it the data as parameters?
2. Do a Replace (CustomerName,"'","")

Some more clever idea?

 

[Allen Browne]

Suggestions:

1. Use the double-quote rather than the single quote.
Double-quotes are quite uncommon in names.
This kind of thing:
strSQL = strSQL & " SELECT " & BatchID & _
", """ & CustomerName & """, " & Amount

If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

2. Use Execute with dbFailOnError:
This gives you much better information about the resuts than RunSQL.

Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[John W. Vinson]

Well, I'm a dunce. I just ran into what has to be one of the oldest problems
around, and while I have a couple ways of getting past it, I'm interested in
the best or most efficient way.

I was using Docmd.RunSQL to insert records but forgot that some names have
apostrophes in them.

BatchID being Long, CustomerName being String, and Amount being Currency:

Dim strSQL as String
'fields to insert
strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
'insert this data
strSQL = strSQL & " SELECT " & BatchID & ",'" & CustomerName & "'," & Amount
Docmd.RunSQL strSQL

Of course, it worked fine for the Smiths but choked on O'Donnell. (This is
in the middle of a long loop through a recordset, not just a single record.)

Should I:

1. Use a query & pass it the data as parameters?
2. Do a Replace (CustomerName,"'","")

Some more clever idea?

Third option: delimit with " instead of with '.

strSQL = strSQL & " SELECT " & BatchID & ",""" & CustomerName & """," & Amount

strSQL will resemble

SELECT 123, "O'Donnell", 55.00

and will insert the name with no quibbles.

 

[Brian]

Thank you, Allen (and Chris & John).

Every day, I learn something new. Both the double-quotes in this context &
the Execute method of the DB using the SQL string are new to me.