#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    24
    Rep Power
    0

    Character Conversion


    Hello,

    I need to input into an Access database a string. My problem occurs when the string that I grab from an HTML form has a ' in it.

    I understand why it is doing it. However I do not understand how to fix it.

    My question is, how do you grab a string from an ASP page such as: "Hello what's up" and place it into an database through ADO. The problem is the single quote. I know how to insert values into a database.

    Thank-you
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Shimonoseki / JAPAN
    Posts
    58
    Rep Power
    12
    What kind of database you are using ?

    In MySQL for example there is an escape character backslash ( \ ) which allows you to work with some special characters in a string:
    I think there must be an equivalent in MSSQL or others.

    For MySQL:
    \\' A single quote character.
    \" A double quote character.
    \b A backspace character.
    \n A newline character.
    \r A carriage return character.
    \t A tab character.
    \\ A backslash character.

    First you have to replace all single quotes in your strings to ( \\' ) and than insert them to database:

    Code:
    YourNewString = Replace(YourString,"'","\\'")
    SQLtext="insert into YourTbl(Colname) values('" & YourNewString & "')"
    Sweet smell of a great sorrow lies over the land. Plumes of smoke rise, merge into the leaden sky. A man lies and dreams of green fields and rivers, but awakes to a morning with no reason for waking. He's haunted by the memory of lost paradise. In his youth or dream, he can't be precise. He's chained forever to a world that's departed. It's not enough, it's not enough.
    Gilmour
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    24
    Rep Power
    0
    I'm using an Access Database
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Shimonoseki / JAPAN
    Posts
    58
    Rep Power
    12
    It is same with MySQL...

    You can use the info in my first post
    Sweet smell of a great sorrow lies over the land. Plumes of smoke rise, merge into the leaden sky. A man lies and dreams of green fields and rivers, but awakes to a morning with no reason for waking. He's haunted by the memory of lost paradise. In his youth or dream, he can't be precise. He's chained forever to a world that's departed. It's not enough, it's not enough.
    Gilmour
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    24
    Rep Power
    0
    I already tried using replace(str, "'", "\'")

    However, when I use this, and I try to Update the information in the database, I get the following error:

    Microsoft JET Database Engine (0x80040E14)
    In operator without () in query expression ''1. \' In '.

    I tried replacing the \' with other characters, and those work fine, however the value in the database is then incorrect. I also tried putting "\" & chr(39) for the last parameter of the replace function, however, this didn't work properly either.

IMN logo majestic logo threadwatch logo seochat tools logo