How to handle SQL statements with apostrophes in strings? 2

[davidd31415]

I am creating records using SQL as follows:

Dim DB as DAO.Database
Set DB = CurrentDb
strSQL = "INSERT INTO Transactions (TextField) VALUES ('"  & textString & "')"
DB.Execute (strSQL)
DB.Close
Set DB = Nothing

If textString contains an apostrophe the SQL statement is corrupted. How do I stop this from happening while allowing apostrophes to be stored in TextField?

Thanks,

David

 

[GhostWolf]

Use the Replace function to add a second apostrophe for each one it finds:

VALUES ('" & [red]Replace([/red]textString[red],"'","''")[/red] & "')"

 

[RuralGuy]

VALUES (" & Chr(34) & textString & Chr(34) & ")"


HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[PHV]

RG, what happens if textString contains a double quote ?

 

[davidd31415]

Perfect.

Thanks for the quick replies- much appreciated.

David

 

[RuralGuy]

PHV,
*Then* you have to resort to changing the textString but it is not my first choice. I resist changing users data as much as I can. For me it is always a last resort.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[davidd31415]

Double quotes do not work with the Chr(34) method, haven't tried the other yet.

 

[PHV]

I disagree, you haven't to change users data but use foolproof SQL syntax (ie doubling the delimiting quote inside the text as suggested by GhostWolf and posted by myself in many threads ...)

 

[RuralGuy]

David: I knew double quotes would not work but you talked about the apostrophe.

PHV: I now agree that the Replace method will work all of the time in this situation and as such is a better solution here.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[davidd31415]

RuralGuy: I was just answering PHV's question.

I switched to the Replace() method since it seems to be the better way to go.