THE -> ' <- (Apostrophe) Problem-VBForums
Results 1 to 27 of 27

Thread: THE -> ' <- (Apostrophe) Problem

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 1999
    Location
    London
    Posts
    40

    Question THE -> ' <- (Apostrophe) Problem

    Hi all,

    What is the most common solution for the issues caused by using the ' character aka chr(39) aka Apostrophe in access databases 2.0 AND 7.0?

    I'm so habituated to test any string for this "little pest" replacing it by chr(96) or sometimes just removing it from the string, that I ever forget to search for a most efficient and proper solution.

    But now I'm in a situation that I CANNOT change the string (files names).

    Maybe it is something so easy that I'm afraid to ask LOL

    Thanks guys.
    MBS

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    The problem doesn't exist if you use parameter queries instead of cobbling dynamically generated SQL together on the fly.

    This also helps resolve serialization issues related to data types with punctuation (non-integer numeric types, date and time types).

    By using prepared parameter queries they only have to be compiled into the DBMSs internal queryplan format once, making repeated queries run faster.

  3. #3
    Fanatic Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    519

    Re: THE -> ' <- (Apostrophe) Problem

    The best solution is to not concatenate strings to build queries, use parameters instead.

    If you search these forums you should find a lot of posts on the subject, http://www.vbforums.com/showthread.p...-my-SQL-string is a good place to start

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,099

    Re: THE -> ' <- (Apostrophe) Problem

    You mean like when you're inserting the data into the database or something? Oh that's easy. Don't use string concatenatenation. Use parameters.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    Here is a simple example.

    It accepts typed in data and stores it into the single-column CSV file "demo.txt" via Jet 4.0 and ADO:

    Name:  sshot.png
Views: 78
Size:  1.9 KB


    Here is an example of a created "demo.txt" file:

    Code:
    TextField
    "Hello World!"
    "O'Leary did it, not me!"
    """Bang"" went the tire as it blew."
    "Nothing to it."
    """Fred's Bank"" holds Joe's life savings."
    Not much code here really. A lot of it deals with the UI and making sure we have at least an empty "demo.txt" before we begin:

    Code:
    Option Explicit
    
    Private Connection As ADODB.Connection
    Private Command As ADODB.Command
    
    Private Sub Command1_Click()
        With Text1
            Connection.InsertNewText .Text
            .Text = vbNullString
            .SetFocus
        End With
    End Sub
    
    Private Sub Form_Load()
        Dim F As Integer
    
        ChDir App.Path
        ChDrive App.Path
    
        'If no file yet, create an empty one:
        On Error Resume Next
        GetAttr "demo.txt"
        If Err Then
            On Error GoTo 0
            F = FreeFile(0)
            Open "demo.txt" For Output As #F
            Print #F, "TextField"
            Close #F
        Else
            On Error GoTo 0
        End If
    
        Set Connection = New Connection
        Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                      & "Data Source='.';" _
                      & "Extended Properties='Text;Hdr=Yes;Format=Delimited'"
        Set Command = New ADODB.Command
        With Command
            .CommandType = adCmdText
            .CommandText = "PARAMETERS [NewText] TEXT(255);" _
                         & "INSERT INTO [demo.txt]([TextField]) " _
                         & "VALUES([NewText])"
            .Prepared = True
            .Name = "InsertNewText"
            Set .ActiveConnection = Connection
        End With
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        Connection.Close
    End Sub
    
    Private Sub Text1_Change()
        Command1.Enabled = Len(Text1.Text) > 0
    End Sub
    Attached Files Attached Files

  6. #6

    Thread Starter
    Member
    Join Date
    Jul 1999
    Location
    London
    Posts
    40

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by dilettante View Post
    The problem doesn't exist if you use parameter queries instead of cobbling dynamically generated SQL together on the fly
    Quote Originally Posted by PlausiblyDamp View Post
    The best solution is to not concatenate strings to build queries, use parameters instead.
    Quote Originally Posted by techgnome View Post
    Don't use string concatenatenation. Use parameters.
    Thanks so much for the answers, links and samples guys I will look at it over the weekend.

    Wish you all a great weekend.

    Regards,
    MBS
    MBS

  7. #7
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    The more versed in databases here can correct me if I'm wrong, but for query's SQL string building don't you solve that problem just replacing ' with '' (two single quotes)?
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    That was a common trick used in old web scripts, but it can fail badly.

    Depending on the DBMS there may be other problem characters: quote, NUL, backslash, etc.

    And you still have the problem with date/time and number formats.

  9. #9
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by dilettante View Post
    That was a common trick used in old web scripts, but it can fail badly.

    Depending on the DBMS there may be other problem characters: quote, NUL, backslash, etc.
    Yes, you need to check for NULL values in the case they can be found in the data. But that's another issue not related to the characters that must be escaped.
    I don't know whether it was used by other technologies, but in Access databases it's not just a trick (like something undocumented) but the offficial way to escape the single quote character, because it has an special meaning in the query syntax.

    Quote Originally Posted by dilettante View Post
    backslash, etc.
    I don't know what the problem with the backslash is.

    Quote Originally Posted by dilettante View Post
    And you still have the problem with date/time and number formats.
    Yes, they must be properly handled.
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  10. #10
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    Er, NUL is a character (vbNullChar, ChrW$(0), etc.), a separate issue from NULL the special value.

    I'm surprised we don't see more problems from "comma as decimal point" folks inserting values like 1.1, because most SQL dialects want "period as decimal point."

  11. #11
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by dilettante View Post
    Er, NUL is a character (vbNullChar, ChrW$(0), etc.), a separate issue from NULL the special value.
    And how must be handled that?
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    Depends on the DBMS. But just use parameter queries and all of these issues go away.

  13. #13
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    562

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by Eduardo- View Post
    And how must be handled that?
    Hi Eduardo,

    you can handle it like this..
    Code:
    'liefert einen Stringwert in HochKommata eingeschlossen
    Public Function strStr(sString As String) As String
    
       Dim s As String
       
          If Len(sString) = 0 Then
             strStr = "NULL"
             Exit Function
          End If
       
          'entfernt doppelte Hochkommata ''
          s = sString
          Do While InStr(1, s, Chr(39) & Chr(39)) > 0
             s = Replace(s, Chr(39) & Chr(39), Chr(39))
          Loop
          'Hochkommata doppeln
          s = Replace(s, Chr(39), Chr(39) & Chr(39))
    
          'entfernt doppelte DoppelHochkommata "
          Do While InStr(1, s, Chr(34) & Chr(34)) > 0
             s = Replace(s, Chr(34) & Chr(34), Chr(34))
          Loop
          'Hochkommata doppeln
          s = Replace(s, Chr(34), Chr(34) & Chr(34))
    
    
          'Zeichenkette in Hochkommata einschliessen
          strStr = Chr(39) & s & Chr(39)
    
    End Function
    all the other's can be handled to.

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    Hi Chris,

    Quote Originally Posted by ChrisE View Post
    Hi Eduardo,

    you can handle it like this..
    Code:
    'liefert einen Stringwert in HochKommata eingeschlossen
    Public Function strStr(sString As String) As String
    
       Dim s As String
       
          If Len(sString) = 0 Then
             strStr = "NULL"
             Exit Function
          End If
       
          'entfernt doppelte Hochkommata ''
          s = sString
          Do While InStr(1, s, Chr(39) & Chr(39)) > 0
             s = Replace(s, Chr(39) & Chr(39), Chr(39))
          Loop
          'Hochkommata doppeln
          s = Replace(s, Chr(39), Chr(39) & Chr(39))
    
          'entfernt doppelte DoppelHochkommata "
          Do While InStr(1, s, Chr(34) & Chr(34)) > 0
             s = Replace(s, Chr(34) & Chr(34), Chr(34))
          Loop
          'Hochkommata doppeln
          s = Replace(s, Chr(34), Chr(34) & Chr(34))
    
    
          'Zeichenkette in Hochkommata einschliessen
          strStr = Chr(39) & s & Chr(39)
    
    End Function
    all the other's can be handled to.

    regards
    Chris
    Why are you changing the double quotes, I don't see a problem with that.

    What I do is to store in the database the string untouched and then in the queries I replace simple quotes with two simple quotes:

    Code:
    Set Rec = DB.OpenRecorset ("SELECT * FROM Table WHERE Name LIKE '*" & FixQuotes(SearchTerm) & "*'")
    
    Public Function FixQuotes(nText As String) As String
        FixQuotes = Replace(nText, ', '')
    End Function
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  15. #15
    Fanatic Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    519

    Re: THE -> ' <- (Apostrophe) Problem

    Or just do it properly and use parameters, why use "tricks" to change the data you are putting in the database, and then needing to restore it when reading it out, when using parameters solves this problem as well as many others and also protects against things like SQL injection.

  16. #16
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by PlausiblyDamp View Post
    Or just do it properly and use parameters, why use "tricks" to change the data you are putting in the database, and then needing to restore it when reading it out, when using parameters solves this problem as well as many others and also protects against things like SQL injection.
    Do you mean to have the queries stored in the database?
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  17. #17
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    562

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by PlausiblyDamp View Post
    Or just do it properly and use parameters, why use "tricks" to change the data you are putting in the database, and then needing to restore it when reading it out, when using parameters solves this problem as well as many others and also protects against things like SQL injection.
    Hi,

    have you never asked yourself what the Access developers have programmed in order to send Data to the table?

    for example
    Code:
    cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, txt_empid.Text)
    what is programmed behind adVarChar or any other parameter ? probably the same 'tricks'


    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  18. #18
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by Eduardo- View Post
    Do you mean to have the queries stored in the database?
    See post #5 above.

    The simple example there uses an ADO Command object containing a named parameter query. The name becomes an extended method of the Connection object and parameters can be passed as with any method call:

    Code:
    Private Sub Command1_Click()
        With Text1
            Connection.InsertNewText .Text
            .Text = vbNullString
            .SetFocus
        End With
    End Sub
    This can also be done with stored procedures, even the simple ones that Jet and ACE support that seem to be QueryDefs under the covers.

  19. #19
    Fanatic Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    519

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by ChrisE View Post
    Hi,

    have you never asked yourself what the Access developers have programmed in order to send Data to the table?

    for example
    Code:
    cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, txt_empid.Text)
    what is programmed behind adVarChar or any other parameter ? probably the same 'tricks'


    regards
    Chris
    However they are doing this it certainly isn't just string concatenation or some other "trick" this kind of thing is fundamental to how databases work and is not relying on string concatenation or simple tricks to manipulate data in the same way that has been suggested here,

  20. #20
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    I believe that under the covers there are indeed "tricks" employed. These are the same kinds of "tricks" we might use ourselves within data passed to/from APIs. It might involve escaping text, or it might involve a length header followed by binary bytes for each data item.

    But none of that matters because each DBMS does it a different way.

    An ODBC Driver or OLE DB Provider has a published client API and a private server API. The Driver/Provider knows how to talk to the DB engine and this "talk" can be any form they want.

    Code:
    {Program}--{ADO}--{Provider}--{DB Engine}
    
    {Program}--{ADO}--{MSDASQL shim Provider}--{Driver}--{DB Engine}
    We don't know and don't need to know the private API.

  21. #21
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    So you want to throw out all the SQL language alltogether because of the string delimiter character for literals.
    You can also throw VB because it has the same issue with double quotes.
    Last edited by Eduardo-; Sep 23rd, 2017 at 05:17 PM.
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  22. #22
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,500

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by Eduardo- View Post
    What I do is to store in the database the string untouched and then in the queries I replace simple quotes with two simple quotes:
    Code:
    Set Rec = DB.OpenRecorset ("SELECT * FROM Table WHERE Name LIKE '*" & FixQuotes(SearchTerm) & "*'")
    
    Public Function FixQuotes(nText As String) As String
        FixQuotes = Replace(nText, ', '')
    End Function
    This method is also very common when using the ADO recordset's Filter and Find methods.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  23. #23
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by Eduardo- View Post
    So you want to throw out all the SQL language alltogether because of the string delimiter character for literals.
    You can also throw VB because it has the same issue with double quotes.
    No. Did you read anything posted above?

    Code:
        With Command
            .CommandType = adCmdText
            .CommandText = "PARAMETERS [NewText] TEXT(255);" _
                         & "INSERT INTO [demo.txt]([TextField]) " _
                         & "VALUES([NewText])"
            .Prepared = True
            .Name = "InsertNewText"
            Set .ActiveConnection = Connection
        End With

  24. #24
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    This also leads to the discussion between ADO and DAO, and for local Access databases I prefer DAO because it is faster and also simpler, and the code much more straightforward.
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  25. #25
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    536

    Re: THE -> ' <- (Apostrophe) Problem

    Quote Originally Posted by dilettante View Post
    Here is a simple example.

    It accepts typed in data and stores it into the single-column CSV file "demo.txt" via Jet 4.0 and ADO:

    Name:  sshot.png
Views: 78
Size:  1.9 KB


    Here is an example of a created "demo.txt" file:

    Code:
    TextField
    "Hello World!"
    "O'Leary did it, not me!"
    """Bang"" went the tire as it blew."
    "Nothing to it."
    """Fred's Bank"" holds Joe's life savings."
    Not much code here really. A lot of it deals with the UI and making sure we have at least an empty "demo.txt" before we begin:

    Code:
    Option Explicit
    
    Private Connection As ADODB.Connection
    Private Command As ADODB.Command
    
    Private Sub Command1_Click()
        With Text1
            Connection.InsertNewText .Text
            .Text = vbNullString
            .SetFocus
        End With
    End Sub
    
    Private Sub Form_Load()
        Dim F As Integer
    
        ChDir App.Path
        ChDrive App.Path
    
        'If no file yet, create an empty one:
        On Error Resume Next
        GetAttr "demo.txt"
        If Err Then
            On Error GoTo 0
            F = FreeFile(0)
            Open "demo.txt" For Output As #F
            Print #F, "TextField"
            Close #F
        Else
            On Error GoTo 0
        End If
    
        Set Connection = New Connection
        Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                      & "Data Source='.';" _
                      & "Extended Properties='Text;Hdr=Yes;Format=Delimited'"
        Set Command = New ADODB.Command
        With Command
            .CommandType = adCmdText
            .CommandText = "PARAMETERS [NewText] TEXT(255);" _
                         & "INSERT INTO [demo.txt]([TextField]) " _
                         & "VALUES([NewText])"
            .Prepared = True
            .Name = "InsertNewText"
            Set .ActiveConnection = Connection
        End With
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        Connection.Close
    End Sub
    
    Private Sub Text1_Change()
        Command1.Enabled = Len(Text1.Text) > 0
    End Sub
    When I open the project in the IDE, the first time it executes the line
    Code:
    Connection.InsertNewText .Text
    I get the error -2147467259 (80004005) Selected collating sequence not supported by the operating system.

    After that, I don't get the error any more.

    But if I close the project, and open it again, the fist time it goes to that line it throws the same error again.
    Also only the firt time.
    MSDN online for VB6 - Language Reference - Controls Reference
    Download MSDN October 2001: disk 1, 2 and 3

  26. #26
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem

    Why quote the entire post? This isn't the UseNet, it is a forum web site. Users can scroll back or you can just put in a link to the post. Or just quote what you need.


    This is a well known flaw in the VB6 IDE.

    A result returned on the stack after the call isn't handled properly. Lots of ADO methods can produce this problem, but you normally only see it when using something like the Jet Text IISAM or a Desktop ODBC Driver as used here. The Microsoft workaround: run again. At least it doesn't happen in compiled programs.


    I swear, lately every thread ends with a Tums Festival.
    Last edited by dilettante; Sep 23rd, 2017 at 10:05 PM.

  27. #27
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: THE -> ' <- (Apostrophe) Problem


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.