Single-quote in SQL Statement?

[Guest]

Hi all,

I'm inserting values into a table using: CurrentDb.Execute strSQL,
dbFailOnError

Everything works fine, except when I'm adding a string value that sometimes
contains the single-quote. ie. someone wrote "didn't"

How can get those values included in the string for SQL?

I think I'd have to do a find/replace inside the string to replace all
occurrences of ' with something else, but I don't know how to do it, or what
character I need to replace it with. I'm looking into Replace, but haven't
gotten it working yet.

Any help is appreciated.

Thanks,
Jay

 

[Tim Ferguson]

Everything works fine, except when I'm adding a string value that
sometimes contains the single-quote. ie. someone wrote "didn't"

Whatever you are using to delimit the (internal) string, needs to be
doubled if it occurs inside it:

MyFile = "A Man Called ""Horse""."

adoSQL = "SELECT 'O''Reilly' AS LastName"
jetSQL = "DELETE FROM MyTable " & _
"WHERE NickName = ""Duane """"The Boss"""" Hookom"";"



It's easiest to do this with the Replace command:

public function SQLString(SomeText as String)
' change this to a double quote for DAO/Jet, or
' leave it at single quote for ADO
'
private const DelimChar = "'"

' add the quotes on the outside and double up any
' on the inside
SQLString = DelimChar & _
Replace(SomeText, DelimChar, String$(DelimChar,2)) & _
DelimChar

End Function


jetSQL = "WHERE MyName = " & SQLString(MyTextBox.Text)


Hope that helps


Tim F

 

[Douglas J Steele]

You need to replace all occurrences of single quote with two single quotes.
Assuming you're using Access 2000 or newer, the Replace function is all you
need:

It's difficult to give you a definitive answer without knowing how you build
up strSQL. Generically, you'd use something like:

strSQL = "UPDATE MyTable SET MyField = '" & Replace(strValue, "'", "''") &
"'"

Exagerated for clarity, that's

strSQL = "UPDATE MyTable SET MyField = ' " & Replace(strValue, " ' ", " ' '
") & " ' "

Alternatively, you could use double-quotes as your delimiter:

strSQL = "UPDATE MyTable SET MyField = """ & strValue & """"

or

strSQL = "UPDATE MyTable SET MyField = " & Chr$(34) & strValue & Chr$(34)

but then you'll run into problems with strings that contain double quotes.

You might want to read my April. 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Guest]

Thanks to you both, that solved it.

Jay

You need to replace all occurrences of single quote with two single quotes.
Assuming you're using Access 2000 or newer, the Replace function is all you
need:

It's difficult to give you a definitive answer without knowing how you build
up strSQL. Generically, you'd use something like:

strSQL = "UPDATE MyTable SET MyField = '" & Replace(strValue, "'", "''") &
"'"

Exagerated for clarity, that's

strSQL = "UPDATE MyTable SET MyField = ' " & Replace(strValue, " ' ", " ' '
") & " ' "

Alternatively, you could use double-quotes as your delimiter:

strSQL = "UPDATE MyTable SET MyField = """ & strValue & """"

or

strSQL = "UPDATE MyTable SET MyField = " & Chr$(34) & strValue & Chr$(34)

but then you'll run into problems with strings that contain double quotes.

You might want to read my April. 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html