Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    9

    Unanswered: Error storing varchar with apostrophe

    I'm getting the following error when I try to store
    the name Wendy O'Leary (the name is entered via a text box on an ASP page)

    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Line 1: Incorrect syntax near 'leary'.

    Posted Data is
    FirstName=wendy+o%27leary&

    Any help would be greatly appreciated
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just double up the single quotes, i.e. two single quotes in a row to insert one

    insert into nametable (firstname, lastname)
    values ('Wendy','O''Leary')

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Nov 2002
    Posts
    9
    thanks Rudy,
    But the name is entered via a web page variable and I'll never know if an apostrophe is entered (unless I scan the entry)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's exactly what you have to do

    i think there's a Replace function (i don't do ASP), so replace each single quote with two of them

    rudy

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Rudy is correct. If you are using vbscript, you should always use the replace function to replace single quotes on any character type data field before updating/inserting into a database.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    sSQL = Replace(sSQL, "'", "''")

    You may want to write an function (called CleanSQL) and put it in an Include file that you reference on each ASP page that creates SQL code. You can use it to do security checks and strip out any other unwanted characters before executing the SQL on your database server. Not doing so opens your site up to a lot of potential abuse.

    Regards,

    Hugh Scott

    Originally posted by rnealejr
    Rudy is correct. If you are using vbscript, you should always use the replace function to replace single quotes on any character type data field before updating/inserting into a database.

  7. #7
    Join Date
    Nov 2002
    Posts
    7

    Recommend learning Parameterized Command Object

    I used to double up the quotes. But I have found that the ADO Command object handles the quotes with no problems.

    Such as (pseudocode):

    Code:
    Set oCmd = New ADODB.Command
    with oCmd
    .CommandText = "ins_Person"
    .Parameters.Append CreateParameter("Name", adVarchar,adParamInput, 50, strName)
    
    .Execute Options:=adExecuteNoRecords
    End With
    No matter if the name has a quote or not, it will accept it......

    since the code above uses named enums, you will have to include the adovbs.inc of course, or you can change the named enums to thier numerical equivalents....


    -Rich

Posting Permissions

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