none
parametrized Queries RRS feed

  • Question

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

    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)
    
    End Sub

    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


    -Nothing to see. Move along.

    Wednesday, May 16, 2012 9:00 PM

Answers

  • Is there a chance that any of the values involved will include a double quote " ? If not, try

    Private Sub getUserLeaderInfo(ByVal record As Long, ByVal rs As DAO.Recordset)
        With rs
            queryStr = "INSERT INTO t_user_ldr_info VALUES(" & _
                record & ", " & _
                Chr(34) & .Fields("Controlp") & Chr(34) & ", " & _
                Chr(34) & .Fields("Test") & Chr(34) & ", " & _
                Chr(34) & .Fields("LoginID") & Chr(34) & ", " & _
                Chr(34) & .Fields("DTG_Submit") & Chr(34) & ", " & _
                Chr(34) & .Fields("PIN") & Chr(34) & ", " & _
                Chr(34) & .Fields("SystemID") & Chr(34) & ", " & _
                Chr(34) & .Fields("Role") & Chr(34) & ", " & _
                Chr(34) & .Fields("Mission") & Chr(34) & ", " & _
                Chr(34) & .Fields("Location") & Chr(34) & ", " & _
                Chr(34) & .Fields("Other") & Chr(34) & ", " & _
                Chr(34) & .Fields("Terrain") & Chr(34) & ")"
        End With
        DoCmd.SetWarnings False
        DoCmd.RunSQL (queryStr)
    End Sub
    Added: see http://www.eileenslounge.com/viewtopic.php?p=21115#p21115 for more info.

    Regards, Hans Vogelaar


    Wednesday, May 16, 2012 9:10 PM
  • Option 1: use the original code and double the apostrophes within the values:

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

    Option 2: use the code that I posted and double the double quotes within the values:

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


    Regards, Hans Vogelaar

    • Marked as answer by blacksaibot Thursday, May 17, 2012 1:31 AM
    Wednesday, May 16, 2012 9:32 PM

All replies

  • Is there a chance that any of the values involved will include a double quote " ? If not, try

    Private Sub getUserLeaderInfo(ByVal record As Long, ByVal rs As DAO.Recordset)
        With rs
            queryStr = "INSERT INTO t_user_ldr_info VALUES(" & _
                record & ", " & _
                Chr(34) & .Fields("Controlp") & Chr(34) & ", " & _
                Chr(34) & .Fields("Test") & Chr(34) & ", " & _
                Chr(34) & .Fields("LoginID") & Chr(34) & ", " & _
                Chr(34) & .Fields("DTG_Submit") & Chr(34) & ", " & _
                Chr(34) & .Fields("PIN") & Chr(34) & ", " & _
                Chr(34) & .Fields("SystemID") & Chr(34) & ", " & _
                Chr(34) & .Fields("Role") & Chr(34) & ", " & _
                Chr(34) & .Fields("Mission") & Chr(34) & ", " & _
                Chr(34) & .Fields("Location") & Chr(34) & ", " & _
                Chr(34) & .Fields("Other") & Chr(34) & ", " & _
                Chr(34) & .Fields("Terrain") & Chr(34) & ")"
        End With
        DoCmd.SetWarnings False
        DoCmd.RunSQL (queryStr)
    End Sub
    Added: see http://www.eileenslounge.com/viewtopic.php?p=21115#p21115 for more info.

    Regards, Hans Vogelaar


    Wednesday, May 16, 2012 9:10 PM
  • There is chance. Not likely, but the data entry people are unpredictable.

    So what could I do if there are double quotes?


    -Nothing to see. Move along.


    • Edited by blacksaibot Wednesday, May 16, 2012 9:23 PM
    Wednesday, May 16, 2012 9:22 PM
  • Option 1: use the original code and double the apostrophes within the values:

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

    Option 2: use the code that I posted and double the double quotes within the values:

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


    Regards, Hans Vogelaar

    • Marked as answer by blacksaibot Thursday, May 17, 2012 1:31 AM
    Wednesday, May 16, 2012 9:32 PM
  • This is crashing my query:

    ; [kingr - Date: 5/9/2012 10:16 AM]: ***RFI Q43 asked TP2798 to describe what the "things" are he referred to in his response.***; [kingr - Date: 5/9/2012 10:45 AM]: ; [kingr - Date: 5/10/2012 9:28 AM]: ; [kingr - Date: 5/10/2012 1:08 PM]: ; [spaays - Dat

    It's coming from a MEMO column.

    HOWEVER this didn't crash my query:

    ; [kingr - Date: 5/9/2012 10:14 AM]: ; [Oakleyj - Date: 5/9/2012 11:17 AM]: ; [spaays - Date: 5/9/2012 3:40 PM]:; [DOTaE: holcombb - Date: 5/9/2012 4:22 PM]:; [DOTaE: holcombb - Date: 5/9/2012 4:23 PM]:; [DOTaE: jonest - Date: 5/9/2012 4:27 PM]:; [MCOTEA:

    Is it the asterisks???

    NEVER MIND I looked over your last thread with double quotes fixed. Thanks!


    -Nothing to see. Move along.




    Thursday, May 17, 2012 1:23 AM