Escaping punctuations and quotes

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • techbuddha
    New Member
    • Jul 2007
    • 14

    Escaping punctuations and quotes

    Hi I am new to VB. I have an access database that has data that uses the single quote like in (don't) or (g'night).

    I have create a procedure that will do a search and replace, Which I thought should be simply search for (') and replace it with (& "'" &), but that just leads to a billion (& "'" &) of these being inserted. Okay maybe it's not that much but it is a lot.

    Isn't there an easier way to get around "data" that uses characters that produce errors in "code". I saw an example in Perl on this site but I don't get it how to translate that into VB.

    many thanks from the "geek in training"
  • Killer42
    Recognized Expert Expert
    • Oct 2006
    • 8429

    #2
    I don't quite follow. What sort of conversion would you like to do?

    Comment

    • techbuddha
      New Member
      • Jul 2007
      • 14

      #3
      Originally posted by Killer42
      I don't quite follow. What sort of conversion would you like to do?
      When I run the current script and one record has a field that has a single quote in it . I get an error. I am assuming the VB is expecting and matching single qoute. My question basically, I guess, is how do encapsulate the offending character OR what kind of substitution can I do for the offending character that will prevent the error.

      The error occurs when I am doing an insert and the offending value looks something like this ( b'ad cha'ract'er). But usually it is just one ' .

      I don't want to change the data the user is entering. I want them to enter the data the way they get it or the way the word is spelled. I just don't want words like M'duqes to give me an error cause there is no matching single quote .

      so no transformation. more like hmmm...... chameleoning... .. masqurading ..... character deception possibly ?? ;-)

      right now my work around is to substitute ' with `. The later is whatever that thing is next to the #1 key in the upper left hand corner, but I think I am going down the wrong road when I start altering the data to fit the code instead of writing the code to fit the data. Not so???

      Comment

      • Killer42
        Recognized Expert Expert
        • Oct 2006
        • 8429

        #4
        Hm... well, I did read something recently about being able to delimit strings in SQL with # instead of '. Perhaps that would help. You'll probably find more help for this in the Access forum, I think.

        Actually, there is an entry in their tips and tricks covering apostrophes. Perhaps it will help. You can find it in their T&T index, here. Just use the text-search function of your browser (or your own eyes, of course) to scan for "Quotes".

        Comment

        • kentgorrell
          New Member
          • Jul 2007
          • 11

          #5
          In Jet (Access) you should be able to contain a string between double quotes - three each side. eg
          strSQL = "SELECT * FROM tblNames WHERE Last_Name = """ & strName & """;"
          You see when you use the three quotes you end up with one. You may like to see the result by inserting debug.print strSQL to print the string to the immediate window. it should look like
          SELECT * FROM tblNames WHERE Last_Name = "O'Reilly";

          Comment

          • Killer42
            Recognized Expert Expert
            • Oct 2006
            • 8429

            #6
            Originally posted by kentgorrell
            In Jet (Access) you should be able to contain a string between double quotes - three each side. eg
            strSQL = "SELECT * FROM tblNames WHERE Last_Name = """ & strName & """;" ...
            Thanks for that. You can also get this effect by using Chr$(34) for the double-quote character.

            Comment

            • kentgorrell
              New Member
              • Jul 2007
              • 11

              #7
              You are right CHR$(34) can be easier than trying to keep count of the double quotes.

              Comment

              • Killer42
                Recognized Expert Expert
                • Oct 2006
                • 8429

                #8
                Originally posted by kentgorrell
                You are right CHR$(34) can be easier than trying to keep count of the double quotes.
                I think some versions of VB have a named constant you can use, like vbQuotes or something. Can't recall for sure, though. I know I have created one myself a few times, as it makes the code more readable.

                Comment

                • techbuddha
                  New Member
                  • Jul 2007
                  • 14

                  #9
                  so then the code could look like this

                  strSQL = "SELECT * FROM tblNames WHERE Last_Name = chr$(34) & strName & chr$(34);"

                  Comment

                  • Killer42
                    Recognized Expert Expert
                    • Oct 2006
                    • 8429

                    #10
                    Originally posted by techbuddha
                    so then the code could look like this

                    strSQL = "SELECT * FROM tblNames WHERE Last_Name = chr$(34) & strName & chr$(34);"
                    Not quite. You need to put the value of each variable into the string, not the name of the variable. So your example would be more like...[CODE=vb]strSQL = "SELECT * FROM tblNames WHERE Last_Name = " & Chr$(34) & strName & Chr$(34) & ";"[/CODE]

                    Comment

                    • fplesco
                      New Member
                      • Jul 2007
                      • 82

                      #11
                      Originally posted by Killer42
                      Not quite. You need to put the value of each variable into the string, not the name of the variable. So your example would be more like...[CODE=vb]strSQL = "SELECT * FROM tblNames WHERE Last_Name = " & Chr$(34) & strName & Chr$(34) & ";"[/CODE]
                      Hi there -

                      Maybe you can give this code a try:

                      Code:
                      strSQL = "SELECT * FROM tblNames WHERE Last_Name = '" &  Replace(strName,"'","''") & "'"
                      Basically, just REPLACE a single qoute by 2 single quotes. Same with saving values into the table. Just put REPLACE(<variab le or text object>,"'","'' ")
                      to every text object or variable that you know, can contain a QUOTE.

                      Comment

                      • techbuddha
                        New Member
                        • Jul 2007
                        • 14

                        #12
                        thanks folks I think I am getting the hang of it ; -)

                        Comment

                        • Killer42
                          Recognized Expert Expert
                          • Oct 2006
                          • 8429

                          #13
                          Originally posted by fplesco
                          ... Basically, just REPLACE a single qoute by 2 single quotes ...
                          Thanks for that, fplesco. In switching around between different programming languages, SQL and so on, I didn't remember that SQL could handle them as doubles like that.

                          Don't recall whether I mentioned it or not, but I just read recently that you can also use hashes (#) to delimit a string in SQL. That might resolve the immediate issue. Unless it's possible to have hashes in the string, of course - then you're back to square one. :)

                          Comment

                          Working...