Professional Web Applications Themes

Update SET ? - ASP Database

Taking data from a form, and using UPDATE tablename SET Seems to be working fine... EXCEPT.. Certain characters cause an error when the update happens. These include: ' " ' causes an SQL error (Syntax error (missing operator) in query expression) " data after or between the "" is not displayed.. How do I trap these ? and stop these errors ? Thanks...

  1. #1

    Default Update SET ?

    Taking data from a form, and using UPDATE tablename SET

    Seems to be working fine... EXCEPT..

    Certain characters cause an error when the update happens.

    These include:
    ' "

    ' causes an SQL error (Syntax error (missing operator) in query expression)

    " data after or between the "" is not displayed..

    How do I trap these ? and stop these errors ?

    Thanks


    TomT Guest

  2. #2

    Default Re: Update SET ?

    TomT wrote: 

    Assuming you are using dynamic sql (not recommended), use Replace() to
    double up the single quote character. Characters are "escaped" by doubling
    them up. The escaped characters are automatically converted into the single
    literal character when the string is processed.

    http://www.aspfaq.com/show.asp?id=2035

    This whole issue can be avoided (along with preventing SQL Injection) y
    passing data via parameters.

    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 Guest

  3. #3

    Default Re: Update SET ?

    As for the "

    This isn't an issue with building your query. It is merely an issue with
    building a string. Example:

    sVar = "The sign over the door says "Employees Only.""

    When this is interpreted by the scripting host, it will see that " before
    Employees and take that to mean the end of your string. Then, any
    characters that appear after it will just cause confusion, hence an error.
    The way to "escape" quotes in VB Script (is that what you're using) is by
    doubling them. IE:

    sVar = "The sign over the door says ""Employees Only."""

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

    As for the '

    ' is the delimiter used mostly for text in SQL queries. If you have a ' in
    your text that you're updating, inserting, or whatever, you need to "escape"
    that for the query. This is done by doubling the ' character. So, if you
    have this:

    sLastname = "O'Brien"

    You'd want to essentially do:

    sSQL = "select something from somewhere where lastname='" &
    Replace(sLastname, "'", "''") & "'"

    That would produce a sSQL value of

    select something from somewhere where lastname='O''Brien'

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

    For more detailed assistance, post your code and your errors when you are
    posting about code and errors.

    Ray at work






    "TomT" <co.uk> wrote in message
    news:41337b84$0$20248$dial.pipex.com... 


    Ray Guest

Similar Threads

  1. why is the "Adobe Acrobat 6.0.1 Update" message there at the top? -- the update doesn't work
    By john_cummin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 6
    Last Post: August 5th, 01:51 PM
  2. 6.0.1 update...
    By FromZto850@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 2
    Last Post: April 26th, 04:52 PM
  3. 5.0.7 Update Pack 1 requires SCO Update license?
    By Scott McMillan in forum SCO
    Replies: 0
    Last Post: July 30th, 08:04 PM
  4. After Update
    By Jacob in forum Microsoft Access
    Replies: 3
    Last Post: July 18th, 04:20 PM
  5. Specifying "do not update" values in "additive" UPDATE sprocs
    By Joel Thornton in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 10:27 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