Results 1 to 22 of 22

Thread: [RESOLVED] Character '

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Resolved [RESOLVED] Character '

    Hi.
    In one of the fields in a column called "NAMES" (not my case, but lets take this example) i have the name "O'HIGGINS", note it has a " ' "

    But when i use rs.fields(2) (that would call that field) i get an error...

    Any tips?

  2. #2
    Hyperactive Member
    Join Date
    Feb 2006
    Location
    Philippines
    Posts
    468

    Re: Character '

    replace (nameoffield,"'","''")

  3. #3
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Character '

    Ok but if you don't want to change the men's name?

    What is the error and when do you get it ?
    ' this gives error when you include it in SQL statement because it is reserved word
    Using Visual Basic 6.0, access 2000, Visual Studio 2005

    Rate the post that you approve

  4. #4
    Member
    Join Date
    Aug 2004
    Location
    Northern Territory, Australia
    Posts
    61

    Re: Character '

    Hey, I know this one! I had this problem with a 'FindFirst' method for a recordset after clicking a ComboBox, and this was the way around it:

    VB Code:
    1. rstFullName.FindFirst "FullName = " & Chr(34) & cboFullName.Text & Chr(34)

    Chr(34) is double quotes; enclosing a string with a single quote in double quotes will let vb recognise the whole string. Otherwise it stops at the single quote, assuming the rest is a comment. Not entirely sure why you have to use Chr(34) instead of the double quotes themselves, but hey it works.

    Peter

  5. #5
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: Character '

    i think mikee_phil is also correct, with his code, he is replacing the ' with two ' - that is - 2 single qoutes.

    in my case i am using this one (same with mikee_phil):
    VB Code:
    1. Function RemoveSingleQuote(strText As String) As String
    2. On Error GoTo HELL
    3.  
    4.     RemoveSingleQuote = Replace(strText, "'", "''")
    5.    
    6.     GoTo CleanUp
    7. HELL:
    8.     WriteError Err.Number, Err.Description, Date, "modFunctions", "RemoveSingleQuote"
    9. CleanUp:
    10. End Function

    usage:
    VB Code:
    1. SQL = "INSERT INTO myTable(myName) VALUES('" & RemoveSingleQuote(strName) & "')"
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  6. #6
    Member
    Join Date
    Aug 2004
    Location
    Northern Territory, Australia
    Posts
    61

    Re: Character '

    that would probably work, but you're changing the value of the field - changing O'Flaherty to O''Flaherty, which is not how that name is spelt.

    call the field enclosed in Chr(34)'s - trust me, it works

    p

  7. #7
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: Character '

    that would probably work, but you're changing the value of the field - changing O'Flaherty to O''Flaherty, which is not how that name is spelt.
    no, it doesn't.... try it
    Attached Images Attached Images  
    Last edited by eimroda; Apr 14th, 2006 at 04:40 AM.
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  8. #8
    Member
    Join Date
    Aug 2004
    Location
    Northern Territory, Australia
    Posts
    61

    Re: Character '

    ok, my apologies, I guess you must not be writing that change to the db

    but enclosing the field name in Chr(34) is surely easier - it doesn't need an extra line of code, let alone an extra function.

    p

  9. #9
    Shared Member
    Join Date
    May 2005
    Location
    Kashmir, India
    Posts
    2,277

    Re: Character '

    When you use two apostrophes in a field value while inserting the first apostrophe is taken as an escape sequence (like \\ in C) and only the second apostrophe will be inserted in the database.

    The best way to avoid these things is to use Prepared Statements while communicating with the database. They are much safer and faster compared to regular queries. Search the Forum for prepared statements and you will get tons of threads and posts explaining how to use prepared statements in VB.
    Use [code] source code here[/code] tags when you post source code.

    My Articles

  10. #10
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: Character '

    no probs buddy...

    Kanbei can select from the best of both worlds

    BTW, the 2 single qoutes replaced in the string with 1 single quote prevents SQL server from generating error in parsing the SQL statement. lf it encounters

    INSERT INTO tblName(myName) VALUES('O'Flaherty')

    it generates an error, but if its replaced with

    O''Flaherty (note the 2 single quotes)

    it works well. the one saved in the db is O'Flaherty. I've been using this but I don't know the explanation why. I'm calling SZLAMANY for this, huh buddy?
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  11. #11
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: Character '

    hey! i'm late... Shuja Ali has already answered it in the above post...
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  12. #12
    Member
    Join Date
    Aug 2004
    Location
    Northern Territory, Australia
    Posts
    61

    Re: Character '

    so, to recap:

    if a single quote is referenced in a SQL statement, it needs to be replaced by a double quote.

    if a single quote is referenced in VB code, can replace it with a double quote OR can surround the string with chr(34)'s.

    p

  13. #13
    Shared Member
    Join Date
    May 2005
    Location
    Kashmir, India
    Posts
    2,277

    Re: Character '

    You got it wrong. If a single quote is present you need to replace it with two single quotes and not a double quote.

    And as I mentioned, the better way of handling this is to use prepared statements. In prepapred statements you do not have to worry about any special character. And prepared statements are faster and safer.
    Use [code] source code here[/code] tags when you post source code.

    My Articles

  14. #14
    Member
    Join Date
    Aug 2004
    Location
    Northern Territory, Australia
    Posts
    61

    Re: Character '

    Shuja Ali, sorry my language was a bit loose in my last entry - where I said double quotes I meant two single quotes.

    I have never had this problem with a SQL query, of a single quote causing an error. I have however had that problem while trying to identify text in a text box (or a combobox), using the 'FindFirst' method on a DAO recordset. I found the only way around that problem was to enclose the reference to that text in Chr(34), the ascii for double quotes.

    It's not clear in what context the OP encountered the problem, if you're there, could you give us a bit more info?

    p

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: Character '

    Well.... whata discussion this created , lol

    Anyways i just tryed the chr(34) thing... cause it worked!

    So thanks everyone for their help and PeterBorroloola for the idea!

  16. #16
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: [RESOLVED] Character '

    This thread is a bit long!

    Just to clear things up a little, and explain why you are still in exactly the same situation....

    mikee_phil had the correct answer in post #2.

    Shuja Ali had the explanation spot on in post #9 - two single quotes acts as an escape sequence, so only one is used as part of the data (and the string is not "ended"). Prepared Statements are easier (and safer) as you have no need to worry about any characters like this.


    Now the reason why you are still in exactly the same situation - you are using double-quotes as the marker for the start/end of strings instead of single-quotes... what happens if you ever have any double-quotes in the data?

    Exactly the same issue will occur, but admittedly will probably happen less often.

    The correct way to fix it is as in posts 2 and 9, anything else is just a temporary fix.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: [RESOLVED] Character '

    you're right. However im sure no name has double quotes, so ill forbid the user creating a name with them.

    Anyways ill check what "prepared statements" are since im intrested...

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: [RESOLVED] Character '

    I see no reason to limit what data can be entered when the fix is so simple (just a single replace statement). Bear in mind that for another program later you will probably do the same for other fields which may be able to contain double-quotes.

    Note also that if you aren't using the replace (or prepared statements) somebody with a little bit of knowledge can destroy your database by entering particular data in the field.


    edit: for prepared statements, see method 3 of this FAQ thread
    Last edited by si_the_geek; Apr 14th, 2006 at 04:52 PM.

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: [RESOLVED] Character '

    thnaks for the link ill take a look

    BTW. if I replace the ' with " when reading from my DB... the user will see the data with the " instead of the '. Am i missing something?

  20. #20
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    Re: [RESOLVED] Character '

    Quote Originally Posted by Kanbei
    you're right. However im sure no name has double quotes
    Your data is being entered by human beings? Then, trust me, some day someone will enter something with a " in the string and your solution will fail. Escape all tics in user input and avoid having to go back in and fix things later.

  21. #21
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: [RESOLVED] Character '

    Quote Originally Posted by Kanbei
    thnaks for the link ill take a look

    BTW. if I replace the ' with " when reading from my DB... the user will see the data with the " instead of the '. Am i missing something?
    Don't replace when reading from the DB, only when putting the values into SQL statements (or .Filter etc, which are partial SQL statements).

  22. #22

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: [RESOLVED] Character '

    oh... i get it now!
    Thanks!!!

Posting Permissions

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



Click Here to Expand Forum to Full Width