Professional Web Applications Themes

Insert a double quote into SQL - ASP Database

Hello, I am trying to insert/update a field with double quote " . But SQL database will not take it. Anyone have a solution? Thanks, ---------------------- set rs= Server.CreateObject("ADODB.Recordset") rs.open "update mytable set name= ' my name" ' where id= 100 ", strDocCon...

  1. #1

    Default Insert a double quote into SQL

    Hello,

    I am trying to insert/update a field with double quote " . But SQL database
    will not take it.

    Anyone have a solution? Thanks,

    ----------------------

    set rs= Server.CreateObject("ADODB.Recordset")


    rs.open "update mytable set name= ' my name" ' where id= 100 ", strDocCon


    Lin Guest

  2. #2

    Default Re: Insert a double quote into SQL

    ASP sees " as the end of the string. So, double it up.
    http://www.aspfaq.com/2065

    And stop using a recordset to affect data!
    http://www.aspfaq.com/2191

    connectionObject.execute "UPDATE mytable SET name='my name""' WHERE id =
    100", , 129

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Lin Ma" <abc.com> wrote in message
    news:#phx.gbl... 
    database 
    strDocCon 


    Aaron Guest

  3. #3

    Default Re: Insert a double quote into SQL

    Thank Aaron.

    It did go to the SQL DB when I add an extra double quote.

    But the problem is when I retrive the data with double quote value and
    insert into a <input > field, the quote is gone.

    Example:
    Field ToPartDescription value = something with double quote" value

    My input field value will trim the " and everything after the "

    <input name="ToPartDescription" size="30" class="Inputbox" value="<%=
    rs("ToPartDescription") %>">
    It will display only: something with doubel quote

    Any idea to fix this?

    Thanks,


    "Aaron [SQL Server MVP]" <noraa> wrote in message
    news:phx.gbl... 
    > database 
    > strDocCon 
    >
    >[/ref]


    Lin Guest

  4. #4

    Default Re: Insert a double quote into SQL

    User Server.HTMLEncode() on the value. Or, use a delimiter other than "

    Do you understand the actual problem? You're trying to make code tell the
    difference between " and " ... which it can't do without some help.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Lin Ma" <abc.com> wrote in message
    news:phx.gbl... 
    > > database 
    > > strDocCon 
    > >
    > >[/ref]
    >
    >[/ref]


    Aaron Guest

  5. #5

    Default Re: Insert a double quote into SQL

    To try to add a little as far as understanding what the problem is, I'll
    paste in an explanation I gave before. This is in regard to your textbox
    values getting truncated. The solution is what Aaron suggested about
    htmlencoding your values.


    The problem here is simply an HTML problem (but with an ASP solution). Take
    these lines of HTML:

    1. <input type=text value=Mary's cat said "meow.">

    2. <input type='text' value='Mary's cat said "meow."'>

    3. <input type="text" value="Mary's cat said "meow."">


    None of those will display what you want.

    In number 1, there is no ' or " delimiting the value of the input, so you
    will only see text up until the first space.
    ______
    [Mary's]
    ŻŻŻŻŻŻ

    In number 2, ' is the delimiter for the value of the input. So, the first
    time the browser encounters a second ', it will take that to mean the end of
    the value of the input. Anything after that is just unrecognized markup
    that the browser will ignore. So, you would see:
    ____
    [Mary]
    ŻŻŻŻ

    In number 3, " is the delimiter for the value of the input. This time, the
    first time the browser encounters a " character, it will take that to mean
    the end of the value. So, you'd see:
    ________________
    [Mary's cat said ]
    ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ


    Basically, if you're going to delimit your form values with ", you cannot
    use that character in the value. Or, if you use ' as the delimiter, you
    cannot use ' in the value. I suggest you pick a delimiter and stick with
    it. If you want to follow the "strict" rules that are being phased into
    HTML, X-HTML, or whatever, you should select the " as your value delimiter.



    Ray at work


    "Lin Ma" <abc.com> wrote in message
    news:phx.gbl... 
    >> database 
    >> strDocCon 
    >>
    >>[/ref]
    >
    >[/ref]


    Ray Guest

  6. #6

    Default Re: Insert a double quote into SQL

    The Server.HTMLEncode() works. Thanks Aaron.


    "Aaron [SQL Server MVP]" <noraa> wrote in message
    news:phx.gbl... [/ref][/ref]

    > >
    > >[/ref]
    >
    >[/ref]


    Lin Guest

  7. #7

    Default Re: Insert a double quote into SQL

    Ray,

    Thanks for the help. Have a nice day.

    Lin Ma


    "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
    message news:phx.gbl... 
    Take 
    of 
    the 
    delimiter. [/ref][/ref]

    > >
    > >[/ref]
    >
    >[/ref]


    Lin Guest

Similar Threads

  1. MX7 Double Quote
    By Ebeneezer in forum Coldfusion Server Administration
    Replies: 3
    Last Post: April 11th, 05:16 PM
  2. Single & Double Quote Problem in Database Insert/Update
    By smnbin in forum Coldfusion Database Access
    Replies: 3
    Last Post: January 27th, 09:16 AM
  3. SQL insert/update problem if coldfusion field value has single quote
    By strutsng@gmail.com in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 25th, 04:45 PM
  4. INSERT with double quote?
    By Xyerp in forum IBM DB2
    Replies: 2
    Last Post: September 24th, 08:58 AM
  5. Double quote issue.
    By Tom B in forum ASP
    Replies: 2
    Last Post: August 1st, 08:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139