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

 

[Chris O'C via AccessMonster.com]

You should:

1 - avoid runsql. It doesn't tell you when an error occurs, it doesn't roll
back a partially completed transaction, it prompts you about what it's about
to do, requiring user interaction unless you turn off confirmation of these
messages or use the poor programming practice of setwarnings, which causes
problems when it doesn't get turned back on, like saving changes to forms
without prompting when closing the form.

2 - use replace(field1, "'", "''")

Example:

Dim strSQL as String
strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
strSQL = strSQL & " SELECT " & BatchID & ",'" & replace(CustomerName, "'",
"''") & "'," & Amount
Currentdb.execute strSQL, dbfailonerror


and make sure you have error handling in the proc. If there's an error the
operation is rolled back and the error handler message tells the user what
went wrong. If there's no error, there's nothing the user has to do, like
acknowledge 481 records are about to be updated that pops up or needs
additional work to avoid the message. And .execute runs faster than runsql.

Chris
Microsoft MVP

 

[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.