none
How do I avoid character conflicts using SQL to write to tables RRS feed

  • Question

  • I want to add records, and update records. But I'm having trouble with some characters when I build SQL statements using VBA and variables. Like apostrophes.

    1) Is there a better way to insert/update records than building SQL command strings that would avoid this?

    2) What are all the different characters I should be aware of which will mess up a SQL insert/update command string if I have to build them like I am?

    Thanks for your help
    Friday, October 30, 2015 3:00 PM

Answers

  • HTHP,

    Yes, I google it. DAO can't use longtext param...

    workaround is use recordset to add record

    or use ado (You have to add reference to microsoft ActiveX Data Objects):

    Public Sub BannerConfig_addNewBanner()
    
        Dim insertBannerSQL As ADODB.Command
    
        Set insertBannerSQL = New ADODB.Command
        insertBannerSQL.Activeconnection = CurrentProject.Connection
    
        insertBannerSQL.CommandText =  "PARAMETERS qdCustomerID NUMBER, OrderID TEXT(35), OrderLinesID TEXT(35), BannerType TEXT(35), Caption TEXT(25), Section TEXT(15), Category TEXT(225), DestinationURL LONGTEXT, DisplayURL TEXT(255), ScriptBanner BIT, ScriptBannerCode TEXT(255), State TEXT(50), City TEXT(80), PromoCode TEXT(255), DescriptL1 TEXT(30), DescriptL2 TEXT(30);" & _
                                        "INSERT INTO WebDirectoryBanners (CustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromotionalCode, DescriptionLine1, DescriptionLine2)" & _
                                        "VALUES (qdCustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromoCode, DescriptL1, DescriptL2)")
    
        insertBannerSQL.commandType = adCmdText
    
        with insertBannerSQL.Parameters
            .Item("qdCustomerID") = 12345
            .Item("orderID") = 12345
            .Item("orderLinesID") = 12345
            .Item("bannerType") = BannerConfig_BannerType
            .Item("Caption") = BannerConfig_Caption
            .Item("Section") = BannerConfig_Section
            .Item("Category") = BannerConfig_Category
            .Item("DestinationURL") = BannerConfig_DestinationURL
            .Item("DisplayURL") = BannerConfig_DisplayURL
            .Item("ScriptBanner") = BannerConfig_ScriptBanner
            .Item("ScriptBannerCode") = BannerConfig_ScriptBannerCode
            .Item("State") = BannerConfig_State
            .Item("City") = BannerConfig_City
            .Item("PromoCode") = BannerConfig_PromotionalCode
            .Item("DescriptL1") = BannerConfig_DescriptionLine1
            .Item("DescriptL2") = BannerConfig_DescriptionLine2
        End With
        insertBannerSQL.Execute
    
        clearBannerConfigSettings
    End Sub


    Michał


    Tuesday, November 3, 2015 6:48 PM

All replies

  • Hi,

    The characters that could run into problems within an SQL statement usually have to do with data delimiters. Check out this blog article for some ideas. Hope that helps...

    Friday, October 30, 2015 3:04 PM
  • Good post. But still leaves me with questions. That's a fine example of one single quote. But, what if I have some random unknown combination of numerous single quotes and double quotes in the string being concatenated into the SQL command string? Say I just do Values(""" & strTextField & "")" will that work in this situation? Or do things like that become more complex?
    Friday, October 30, 2015 4:40 PM
  • HTHP,

    If you use dynamic sql for insert/update use parameter in query object:

    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", _
            "PARAMETERS param1 TEXT(255), param2 TEXT(255);" & _
            "insert into tableName (field1, field2)" & _
            "values ([param1,param2])")
    qdf!param1 = varContainParam1
    qdf!param2 = varContainParam2
    qdf.execute


    Michał

    Friday, October 30, 2015 4:49 PM
  • Good post. But still leaves me with questions. That's a fine example of one single quote. But, what if I have some random unknown combination of numerous single quotes and double quotes in the string being concatenated into the SQL command string? Say I just do Values(""" & strTextField & "")" will that work in this situation? Or do things like that become more complex?

    For that special case, I might recommend trying the following format:

    "...VALUES(" & Replace(Replace(strTextField,"'","''"),"""","""""") & ")"

    Hope that helps...

    Friday, October 30, 2015 5:08 PM
  • How do I declare a datatype of max text chars? TEXT(MAX) doesn't work so the code below  throws error. Is there a place somewhere with all the datatypes you can declare in a querydef? I'm searching and can't find one. Took me a minute to get the BIT field by trial/error, but where can I find a list?

    Set insertBannerSQL = CurrentDb.CreateQueryDef("", "PARAMETERS qdCustomerID NUMBER, OrderID NUMBER, OrderLinesID NUMBER, BannerType TEXT(35), Caption TEXT(25), Section TEXT(15), Category TEXT(225), DestinationURL TEXT(MAX), DisplayURL TEXT(30), ScriptBanner BIT, ScriptBannerCode TEXT(MAX), State TEXT(50), City TEXT(80), PromoCode TEXT(255), DescriptL1 TEXT(30), DescriptL2 TEXT(30);" & _ "INSERT INTO WebDirectoryBanners (CustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromotionalCode, DescriptionLine1, DescriptionLine2)" & _ "VALUES (qdCustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromoCode, DescriptL1, DescriptL2)")
    • Edited by HTHP Friday, October 30, 2015 7:05 PM
    Friday, October 30, 2015 6:21 PM
  • Ms Access don't have text(max) try use MEMO type...

    Michał

    Friday, October 30, 2015 11:04 PM
  • >>>How do I declare a datatype of max text chars? TEXT(MAX) doesn't work so the code below  throws error. Is there a place somewhere with all the datatypes you can declare in a querydef?

    Accoding to your description, I have made a sample query with TEXT(MAX), then I got errors.

    Text

    Purpose:Use to store up to 255 characters of text. For larger text fields, use the Memo data type.

    If you need to konw data types in Access, you could refer to that there are ten different data types in Access:

    Attachment    Files, such as digital photos. Multiple files can be attached per record. This data type is not available in earlier versions of Access.

    AutoNumber    Numbers that are automatically generated for each record.

    Currency    Monetary values.

    Date/Time    Dates and times.

    Hyperlink    Hyperlinks, such as e-mail addresses.

    Memo    Long blocks of text and text that use text formatting. A typical use of a Memo field would be a detailed product description.

    Number    Numeric values, such as distances. Note that there is a separate data type for currency.

    OLE Object    OLE objects, such as Word documents.

    Text    Short, alphanumeric values, such as a last name or a street address.

    Yes/No    Boolean values.

    For more information, click here to refer about Introduction to data types and field properties

    Monday, November 2, 2015 8:41 AM
  • I tried changing DestinationURL TEXT(MAX) to DestinationURL MEMO below... still gives same error. What would the syntax be?

    Set insertBannerSQL = CurrentDb.CreateQueryDef("", "PARAMETERS qdCustomerID NUMBER, OrderID TEXT(35), OrderLinesID TEXT(35), BannerType TEXT(35), Caption TEXT(25), Section TEXT(15), Category TEXT(225), DestinationURL MEMO, DisplayURL TEXT(30), ScriptBanner BIT, ScriptBannerCode TEXT(255), State TEXT(50), City TEXT(80), PromoCode TEXT(255), DescriptL1 TEXT(30), DescriptL2 TEXT(30);" & _ "INSERT INTO WebDirectoryBanners (CustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromotionalCode, DescriptionLine1, DescriptionLine2)" & _ "VALUES (qdCustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromoCode, DescriptL1, DescriptL2)")
    Monday, November 2, 2015 5:27 PM
  • If you use 2013 version use LongText it replaced memo type

    Michał

    Monday, November 2, 2015 6:32 PM
  • That seemed to be acceptable for the CurrentDb.CreateQueryDef parameters, but now I'm receiving an error any time the LONGTEXT field has more than 255 characters.

    Run-time Error '3271': Invalid Property Value
    Error on this line Here:
    insertBannerSQL!DestinationURL = BannerConfig_DestinationURL

    Variable BannerConfig_DestinationURL is declared as a STRING. This error does not appear if this variable contains under 255 characters, only over 255.

    Here is total code:
    Public Sub BannerConfig_addNewBanner()

    Dim insertBannerSQL As DAO.QueryDef
    Set insertBannerSQL = CurrentDb.CreateQueryDef("", "PARAMETERS qdCustomerID NUMBER, OrderID TEXT(35), OrderLinesID TEXT(35), BannerType TEXT(35), Caption TEXT(25), Section TEXT(15), Category TEXT(225), DestinationURL LONGTEXT, DisplayURL TEXT(255), ScriptBanner BIT, ScriptBannerCode TEXT(255), State TEXT(50), City TEXT(80), PromoCode TEXT(255), DescriptL1 TEXT(30), DescriptL2 TEXT(30);" & _
    "INSERT INTO WebDirectoryBanners (CustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromotionalCode, DescriptionLine1, DescriptionLine2)" & _
    "VALUES (qdCustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromoCode, DescriptL1, DescriptL2)")

    insertBannerSQL!qdCustomerID = 12345
    insertBannerSQL!orderID = 12345
    insertBannerSQL!orderLinesID = 12345
    insertBannerSQL!bannerType = BannerConfig_BannerType
    insertBannerSQL!Caption = BannerConfig_Caption
    insertBannerSQL!Section = BannerConfig_Section
    insertBannerSQL!Category = BannerConfig_Category
    insertBannerSQL!DestinationURL = BannerConfig_DestinationURL
    insertBannerSQL!DisplayURL = BannerConfig_DisplayURL
    insertBannerSQL!ScriptBanner = BannerConfig_ScriptBanner
    insertBannerSQL!ScriptBannerCode = BannerConfig_ScriptBannerCode
    insertBannerSQL!State = BannerConfig_State
    insertBannerSQL!City = BannerConfig_City
    insertBannerSQL!PromoCode = BannerConfig_PromotionalCode
    insertBannerSQL!DescriptL1 = BannerConfig_DescriptionLine1
    insertBannerSQL!DescriptL2 = BannerConfig_DescriptionLine2

    insertBannerSQL.Execute

    clearBannerConfigSettings
    End Sub
    Tuesday, November 3, 2015 5:34 PM
  • HTHP,

    Yes, I google it. DAO can't use longtext param...

    workaround is use recordset to add record

    or use ado (You have to add reference to microsoft ActiveX Data Objects):

    Public Sub BannerConfig_addNewBanner()
    
        Dim insertBannerSQL As ADODB.Command
    
        Set insertBannerSQL = New ADODB.Command
        insertBannerSQL.Activeconnection = CurrentProject.Connection
    
        insertBannerSQL.CommandText =  "PARAMETERS qdCustomerID NUMBER, OrderID TEXT(35), OrderLinesID TEXT(35), BannerType TEXT(35), Caption TEXT(25), Section TEXT(15), Category TEXT(225), DestinationURL LONGTEXT, DisplayURL TEXT(255), ScriptBanner BIT, ScriptBannerCode TEXT(255), State TEXT(50), City TEXT(80), PromoCode TEXT(255), DescriptL1 TEXT(30), DescriptL2 TEXT(30);" & _
                                        "INSERT INTO WebDirectoryBanners (CustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromotionalCode, DescriptionLine1, DescriptionLine2)" & _
                                        "VALUES (qdCustomerID, OrderID, OrderLinesID, BannerType, Caption, Section, Category, DestinationURL, DisplayURL, ScriptBanner, ScriptBannerCode, State, City, PromoCode, DescriptL1, DescriptL2)")
    
        insertBannerSQL.commandType = adCmdText
    
        with insertBannerSQL.Parameters
            .Item("qdCustomerID") = 12345
            .Item("orderID") = 12345
            .Item("orderLinesID") = 12345
            .Item("bannerType") = BannerConfig_BannerType
            .Item("Caption") = BannerConfig_Caption
            .Item("Section") = BannerConfig_Section
            .Item("Category") = BannerConfig_Category
            .Item("DestinationURL") = BannerConfig_DestinationURL
            .Item("DisplayURL") = BannerConfig_DisplayURL
            .Item("ScriptBanner") = BannerConfig_ScriptBanner
            .Item("ScriptBannerCode") = BannerConfig_ScriptBannerCode
            .Item("State") = BannerConfig_State
            .Item("City") = BannerConfig_City
            .Item("PromoCode") = BannerConfig_PromotionalCode
            .Item("DescriptL1") = BannerConfig_DescriptionLine1
            .Item("DescriptL2") = BannerConfig_DescriptionLine2
        End With
        insertBannerSQL.Execute
    
        clearBannerConfigSettings
    End Sub


    Michał


    Tuesday, November 3, 2015 6:48 PM