parametrized Queries (1 Viewer)

[blacksaibot]

I am looking for a good tutorial on parametrized queries in VBA.

I am creating a query based on results of another query... but one of the strings contains a single quote and destroys my second query and it cannot execute properly.

My code:

Private Sub getUserLeaderInfo(ByVal record As Long, ByVal rs As DAO.Recordset)
    With rs
        queryStr = "INSERT INTO t_user_ldr_info VALUES(" & _
            record & ", " & _
            "'" & .Fields("Controlp") & "', " & _
            "'" & .Fields("Test") & "', " & _
            "'" & .Fields("LoginID") & "', " & _
            "'" & .Fields("DTG_Submit") & "', " & _
            "'" & .Fields("PIN") & "', " & _
            "'" & .Fields("SystemID") & "', " & _
            "'" & .Fields("Role") & "', " & _
            "'" & .Fields("Mission") & "', " & _
            "'" & .Fields("Location") & "', " & _
            "'" & .Fields("Other") & "', " & _
            "'" & .Fields("Terrain") & "')"
    End With
                    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (queryStr)

EndSub

How can I turn this into a parametrized query?
Yes, I've googled it... but I don't get much out of the crap that comes up in the results.

Thanks in advance

 

[spikepl]

replace stuff like
",'" & MyString & "',"

with

"," & Chr(34) & MyString & Chr(34) & ","

 

[blacksaibot]

replace stuff like
",'" & MyString & "',"

with

"," & Chr(34) & MyString & Chr(34) & ","

How will that help my problem if MyString contains the single quote...?

Like if ...

MyString = "I don't get this"

so the query string contains

'I don't get this'

and it breaks because it thinks the string is 'I don'

Get it? or am I not getting it?

 

[VilaRestal]

Here's how I think:

Private Sub getUserLeaderInfo(ByVal record As Long, ByVal rs As DAO.Recordset)
   Dim qdf As QueryDef
   Dim queryStr As String
   queryStr = "INSERT INTO t_user_ldr_info VALUES(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12)"
   Set qdf = CurrentDb.CreateQueryDef("", queryStr)
   qdf.Parameters!p1 = record
   With rs
       qdf.Parameters!p2 = .Fields("Controlp")
       qdf.Parameters!p3 = .Fields("Test")
       qdf.Parameters!p4 = .Fields("LoginID")
       qdf.Parameters!p5 = .Fields("DTG_Submit")
       qdf.Parameters!p6 = .Fields("PIN")
       qdf.Parameters!p7 = .Fields("SystemID")
       qdf.Parameters!p8 = .Fields("Role")
       qdf.Parameters!p9 = .Fields("Mission")
       qdf.Parameters!p10 = .Fields("Location")
       qdf.Parameters!p11 = .Fields("Other")
       qdf.Parameters!p12 = .Fields("Terrain")
   End With  
   qdf.Execute
EndSub

 

[VilaRestal]

How will that help my problem if MyString contains the single quote...?

Like if ...

MyString = "I don't get this"

so the query string contains

'I don't get this'

and it breaks because it thinks the string is 'I don'

Get it? or am I not getting it?

Indeed, the easy way round it is to use Replace(str, "'","''") but parameterized queries are much better and avoid any chance of SQL injection.

 

[blacksaibot]

Your solution worked spikepl... I don't get why. But that's awesome.
I'd love a technical explanation as to why.

 

[VilaRestal]

Because it uses quotation marks (Chr(34)) rather than apostrophes. Try it with a string with a quotation mark in it...