SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP Puzzler : using ' & " in field text

    I have an ASP app that updates several feilds of a database.

    Some of the feilds have text that includes apostrophes (') or Quotation marks (")

    Whenever the ASP encounters a field with ' or " (such as "3 years' experience") I get an arror message :

    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression

    Surely, there has to be an easy way around this...

    Any help?

    Thanks
    CM

  2. #2
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    Panhandle of Florida (White Sand/Brown Skin)
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to the informative mind of Shane... I was helped thru that very same problem!

    This is what he told me to do to stop the error I was receiving from the quotes...

    Code:
    strFieldText = Replace(strFieldText, "'", "''")
    It fixed that exact same error for me

  3. #3
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm working on SQL Server, and I replaced all "'" with "/'", works for me.

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    hmmm...

    Still doesn't seem to be working...

    quotation marks " are okay... just when I enter a ' into the application.

    Maybe I'm not enterering the code you reccommended in the right place

    On a side note, I can enter "'" and a ' will appear as I want it, but in other locations, it will read the ' instead of '.

    Thanks for your help

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    um that last post was supposed to say

    & # 3 9 ; (without the spaces)

    as the way I can get the ASP to enter the ' that I want. But other ASP applications show the & # 3 9 ; and not the ' that I want.

    Thanks

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't quite follow your last posts tscpa1 but maybe the following will make things a bit clearer. What you need to do is replace all single quotes (Chr(39)) with 2 single quotes (Chr(39) & Chr(39)).

    Code:
    strInput = Replace(strInput, Chr(39), Chr(39) & Chr(39))
    Last edited by shane; Jun 4, 2001 at 12:07.

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where should I put that line?

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to apply the Replace to any text before you put it into the db. Maybe a example will make it clearer.

    Code:
    CONST DBCONN_STR = <connection info>
    
    'Get some text from a form field 'info'
    strInput = Request.Form("info")
    
    'As the text 'info' may contain single quotes, they need to be
    'replaced by 2 single quotes
    strInput = Replace(strInput, Chr(39), Chr(39) & Chr(39))
    
    'Build the sql string with 'cleaned' info string
    strSQL = "INSERT INTO <table name> (infofield) " & _
    "VALUES ('" & strInput & "')"
    
    'The 'info' text is now ok to insert into db
    
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open DBCONN_STR
    objConn.Execute strSQL, , adExecuteNoRecords

    You may find it quicker to use the code SeFu posted, which does the same. I posted the code using the Chr function as it makes it easier to read without syntax highlighting.

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <h1>SUCESS!</h1>

    I wonder if people who know this ASP thing really well feel as good when they overcome a problems as us clueless beginners do.

    Thanks all, esp. shane, for your help. Eventually, even I can understand some basic concepts you try to demonstrate.

    -c

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think everyone feels good when they get code to work as wanted.

    Glad I could help tscpa1.

  11. #11
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A little late, but my suggestion would've been to use Server.HTMLEncode. But that would've been a bad suggestion.

    Glad you were helped.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)


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
  •