SQL Updates/Inserts with data with double quotes

Question

I have a database that collects client information using assessments and other tools. Often in the assessments there are double quotes in the data when - as may be expected - a direct quote is captured. When I run sql updates (Access front end using VBA to SQL Server 2008 R2 backend) it blows up on double quotes in the data. In the interim I've asked staff to use single quotes when they enter data, but that is an unsustainable solution as they forget and the program crashes when it hits the double quotes. The datatype is nvarchar(max).

The current VBA string looks like this:

strInsertSQL = "INSERT INTO tblIRPDetail(IRPID, SectionID, Challenge, Goal, Objective, Intervention, IntDate) VALUES(" & intNewID & ", " & intSection & ", """ & strChallenge & """, """ & strGoal & """, """ & strObjective & """, """ & strIntervention & """, """ & strIntDate & """);"

Essentially any of the strVariables could have single or double quotes in any combination. It works for single quotes but not doubles. Surely this is a fairly common issue, I'm hoping someone has a simple solution!

Thanks in advance!

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

Parameterize you query and add the values as parameters instead.

This might help: parameterized query in ms access 2003 using vba

edited May 23 '17 at 12:04

Community

1
1

answered Oct 19 '12 at 11:36

Magnus Eklund

415
1
5
15

I considered that, but one issue is that it seems to limit the parameter to 255 characters. The vast majority of our entries are much longer text than that. – NPO_Coder Oct 19 '12 at 11:51

score 0 · Answer 2 · answered Oct 19 '12 at 12:39

0

give me a minute and ill copy over the methods i used to get this performed.

In the mean time why not do a Replace(val, """, "'")? That way any instance of a " in the string will be replaced with a single quote '?

answered Oct 19 '12 at 12:39

GoldBishop

2,820
4
47
82

score 0 · Accepted Answer · answered Oct 19 '12 at 14:52

An Access SQL statement can use either single or double quotes for text values. So you could use single quotes in your INSERT statement, but then you would run into a similar problem when the text to insert includes single quotes and which you could then resolve by doubling up the single quotes within the insert text:

Replace(strChallenge, "'", "''")

That doesn't seem like much of an improvement. OTOH, since you're using VBA to insert a row to your table, you don't need to use an INSERT query.

Open your table as a DAO.Recordset, add a new row, then assign your variable values to the corresponding fields.

Public Sub NPO_AddRow()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblIRPDetail", dbOpenTable, dbAppendOnly)
    With rs
        .AddNew
        !IRPID = intNewID
        !SectionID = intSection
        !Challenge = strChallenge
        !Goal = strGoal
        !Objective = strObjective
        !Intervention = strIntervention
        !IntDate = strIntDate
        .Update
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

Note that, unlike a parameter query, this approach will allow you to add text strings longer than 255 characters.

Well that sure makes it easy. Thanks so much! – NPO_Coder Oct 19 '12 at 16:45 — NPO_Coder, Oct 19 '12 at 16:45

SQL Updates/Inserts with data with double quotes

3 Answers3