Double apostrophes

Discussion in 'ASP General' started by Joey Martin, Jul 13, 2005.

  1. Joey Martin

    Joey Martin Guest

    I'm sure this has to be a simple fix. I just cannot figure it out.

    To resolve the typical apostrope issue, I have the
    acarriername = Replace(txtcarriername.text, "'", "''")

    My problem is that 2 apostrophes are now inserted, instead of one. For
    example if someone types in IT'S NICE, when it is displayed in the input
    text box later (to allow a user to change it), it says IT''S NICE.

    This is a sql 2000 database.

    Thanks for the help.




    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Jul 13, 2005
    #1
    1. Advertising

  2. How are you inserting the data into the database? If you are using a
    command object and passing the values into a parameter, you don't need to do
    the replace since it is handled for you by the provider.

    Can you show all of your code so we don't have to guess and grasp at straws?




    "Joey Martin" <> wrote in message
    news:%...
    > I'm sure this has to be a simple fix. I just cannot figure it out.
    >
    > To resolve the typical apostrope issue, I have the
    > acarriername = Replace(txtcarriername.text, "'", "''")
    >
    > My problem is that 2 apostrophes are now inserted, instead of one. For
    > example if someone types in IT'S NICE, when it is displayed in the input
    > text box later (to allow a user to change it), it says IT''S NICE.
    >
    > This is a sql 2000 database.
    >
    > Thanks for the help.
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #2
    1. Advertising

  3. Joey Martin wrote:
    > I'm sure this has to be a simple fix. I just cannot figure it out.
    >
    > To resolve the typical apostrope issue, I have the
    > acarriername = Replace(txtcarriername.text, "'", "''")
    >
    > My problem is that 2 apostrophes are now inserted, instead of one. For
    > example if someone types in IT'S NICE, when it is displayed in the
    > input text box later (to allow a user to change it), it says IT''S
    > NICE.
    >

    Only do the replace when writing the data into the database. Do not do it
    any other time.

    Better yet, stop using dynamic sql. The only reason you have to escape the
    apostrophe is because you are not using parameters. See these:

    http://groups.google.com/groups?hl=...=1&selm=
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
    http://tinyurl.com/jyy0

    If this does not answer your question, post a short repro script so we can
    see what you're doing.

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 13, 2005
    #3
  4. Joey Martin

    Joey Martin Guest

    Current code:

    Set RS = Server.CreateObject("ADODB.Recordset")
    sqlUpdate = "SELECT * FROM ricprops WHERE propno='" & Request("id") &
    "'"

    RS.open sqlUpdate,Conn,1,3
    RS("comments") = Replace(Request.form("comments"), "'", "''")
    RS.Update
    RS.Close




    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Jul 13, 2005
    #4
  5. Ugh. How about:

    comments = replace(request.form("comments"), "'", "''")
    id = replace(request.form("id"), "'", "''")
    sql = "UPDATE ricprops SET comments = '" & comments & "' WHERE propno='" &
    id & "'"
    conn.execute sql,,129

    Or see Bob's link.

    Note you should use request.form() not the lazy request().

    Also, why is the column propno a character datatype? Terrible name--no
    implies number.
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #5
  6. Joey Martin

    Joey Martin Guest

    Bob,
    Not sure I understand your comments about not using dynamic sql. I read
    the documents and it seems as if I do it the correct way. USUALLY, I do
    not use a recordset to update variables. My current code does, because
    it's old code and I never re-wrote it. Usually, I do the following:

    if request("submit")<> "" then
    v1=Replace(Request.form("v1"), "'", "''")
    v2=Replace(Request.form("v2"), "'", "''")

    sql="update table set v1='" & v1 & "',v2='" & v2 & "' where id='1'"
    conn.execute (sql)



    Is that incorrect? Doing it this way, I still get the double apostrophe.




    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Jul 13, 2005
    #6
  7. Joey Martin

    Joey Martin Guest

    Aaron,

    What made you think propno is a CHAR datatype? It is INT.
    The way you wrote the code is how I USUALLY do it.
    But, doing it that way,as you wrote, I still receive double apostrophes.

    THANKS!!


    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Jul 13, 2005
    #7
  8. > sql="update table set v1='" & v1 & "',v2='" & v2 & "' where id='1'"
    > conn.execute (sql)
    >
    >
    >
    > Is that incorrect? Doing it this way, I still get the double apostrophe.


    Then you are also doing the replace when you DISPLAY the data, which you
    shouldn't be doing.
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #8
  9. > What made you think propno is a CHAR datatype? It is INT.

    Because in your query, you surround it with quotes:

    > WHERE propno='" & Request("id") & "'"


    If it's an INT, don't do that!

    A
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #9
  10. Joey Martin

    Joey Martin Guest

    Aaron,

    When displaying the data in my textarea box, here is the code:
    <textarea cols="30" rows="10"
    name="comments"><%=trim(rs("comments"))%></textarea>


    At that time, it displays IT''S NICE.





    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Jul 13, 2005
    #10
  11. There's something missing or that you're not telling us. Do you understand
    why replace() is used when passing data *to* the statement, but not when
    retrieving the data from the database? Are you sure you didn't enter two
    apostrophes into the form?

    While you've told us that you are doing this correctly, we have no way to
    verify that you really are. It sounds to me like you're not.

    Sorry, but I don't know how else to help you.


    > When displaying the data in my textarea box, here is the code:
    > <textarea cols="30" rows="10"
    > name="comments"><%=trim(rs("comments"))%></textarea>
    >
    >
    > At that time, it displays IT''S NICE.
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #11
  12. Joey Martin wrote:
    > Bob,
    > Not sure I understand your comments about not using dynamic sql. I
    > read the documents and it seems as if I do it the correct way.
    > USUALLY, I do not use a recordset to update variables. My current
    > code does, because it's old code and I never re-wrote it. Usually, I
    > do the following:
    >
    > if request("submit")<> "" then
    > v1=Replace(Request.form("v1"), "'", "''")
    > v2=Replace(Request.form("v2"), "'", "''")
    >
    > sql="update table set v1='" & v1 & "',v2='" & v2 & "' where id='1'"
    > conn.execute (sql)
    >

    Wait a minute. Earlier you showed this code:

    Set RS = Server.CreateObject("ADODB.Recordset")
    sqlUpdate = "SELECT * FROM ricprops WHERE propno='" & Request("id") &
    "'"

    RS.open sqlUpdate,Conn,1,3
    RS("comments") = Replace(Request.form("comments"), "'", "''")
    RS.Update
    RS.Close


    When updating a recordset field, do NOT escape (double up) the aprostrophe.
    Change it to:
    RS("comments") = Request.form("comments")

    The only time you need to escape the apostrophe is when you are building
    dynamic sql as in the update statement you show above.
    >
    >
    > Is that incorrect? Doing it this way, I still get the double
    > apostrophe.
    >


    I don't think so. Create a page with just this code in it:

    <%
    dim conn, sql, rs, input, output
    input="it's nice"
    set conn=createobject("adodb.connection")
    conn.open "your connection string"
    sql= "update ricprops set comments='" & _
    Replace(input, "'", "''") & _
    "WHERE propno=1"
    conn.execute sql,,129
    sql="select comments from ricprops WHERE propno=1"
    set rs=conn.execute(sql,,1)
    output=rs(0).value
    rs.close:set rs=nothing
    conn.close: set conn=nothing
    %>

    <html><body>
    I guarantee this will contain only one apostrophe:<BR>
    <textarea cols="30" rows="10"
    name="comments"><%=trim(rs("comments"))%></textarea>
    </body></html>

    Run the page.

    Bob Barrows


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 13, 2005
    #12
  13. Joey Martin

    Giles Guest

    "Aaron Bertrand [SQL Server MVP]" wrote
    > > Note you should use request.form() not the lazy request().


    Hi Aaron - What's the downside of request only? is the overhead of all the
    request objects being hunted through? I sometimes use it to be able to test
    processing pages with a querystring without having to rewrite the forms on
    the previous page.
    Thanks
    Giles
    Giles, Jul 13, 2005
    #13
  14. > Hi Aaron - What's the downside of request only? is the overhead of all the
    > request objects being hunted through?


    http://www.aspfaq.com/2111

    > I sometimes use it to be able to test processing pages with a querystring
    > without having to rewrite the forms on the previous page.


    You could test which method was used and handle it in a function, test
    Request.ServerVariables("REQUEST_METHOD") *once*... then you could use
    something like req("item") instead of having to change them.
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Huddle

    SQL and apostrophes

    Chris Huddle, Dec 10, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    743
    Steve C. Orr [MVP, MCSD]
    Dec 10, 2003
  2. Andy Fish

    HtmlEncode with apostrophes

    Andy Fish, Apr 6, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    8,644
    Nicole Calinoiu
    Apr 6, 2005
  3. darrel

    URLEncode doesn't like apostrophes?

    darrel, Jul 8, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    504
    darrel
    Jul 8, 2005
  4. MS
    Replies:
    3
    Views:
    16,149
    Marcin Grunwald
    Feb 22, 2005
  5. Sydex
    Replies:
    12
    Views:
    6,471
    Victor Bazarov
    Feb 17, 2005
Loading...

Share This Page