Handling an "apostrophe" in a search

Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Handling an "apostrophe" in a search

  1. #1
    Leonard Ridley Guest

    Default Handling an "apostrophe" in a search

    I&#039ve run across a problem when entering an apostrophe in a searched field. An example would be: When I enter a name, such as, "See&#039s Candy" I get the following error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e14&#039 <BR><BR>[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression &#039Choc.State=&#039CA&#039 AND Choc.Name alike&#039%See&#039s Candies%&#039 ORDER BY Choc.City, Choc.Name&#039. <BR><BR>I&#039m assuming it is the "apostrophe" in the word See&#039s that is causing this. What can I do to fix this problem.<BR><BR>Thanx,<BR>Leonard Ridley<BR>leonardridley@yahoo.com<BR>

  2. #2
    Ian Stallings Guest

    Default RE: Handling an

    before submitting a query to a DB you must clean up<BR>the string. I like to use this function to check for<BR>apostrophes:<BR><BR>FUNCTION CheckString (s, endchar)<BR> pos = InStr(s, "&#039")<BR> While pos > 0<BR> s = Mid(s, 1, pos) & "&#039" & Mid(s, pos + 1)<BR> pos = InStr(pos + 2, s, "&#039")<BR> Wend<BR> CheckString="&#039" & s & "&#039" & endchar<BR>END FUNCTION<BR><BR>I then call this function in the sql statement like so:<BR><BR>insert_sql = insert_sql & CheckString(strVar,",")<BR><BR><BR>Only strings should be cleaned up<BR>though and the field in the database<BR>has to be specified as varchar.<BR><BR>I hope this helps.

  3. #3
    João Vilaça Guest

    Default RE: Handling an apostrophe in a SQL String

    &nbsp;<BR>I use str=replace(str, "&#039", "&#039&#039")<BR><BR>Isn&#039t this better ?<BR><BR><BR><BR>JV<BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    142

    Default RE: Handling an apostrophe in a SQL String

    That is what I do...


  5. #5
    Alistair MacNeil Guest

    Default RE: Handling an apostrophe in a SQL String

    I&#039m a newcomer to ASP and have run into this apostrophe problem but I can&#039t work out where to insert your fix. Can you help?

  6. #6
    Nasir Naqvi Guest

    Default RE: Handling an apostrophe in a SQL String

    You may use double quotes too by using big breakits i.e<BR> ssql=[select song from oh where contains (song,&#039] + ["] + request("searchtext") +["] & [&#039)]<BR><BR>OR<BR><BR>ssql=[select Field as [book&#039s name] from tableName

  7. #7
    Anamika Guest

    Default RE: Handling an apostrophe in a SQL String

    Instead use this code<BR>&#060;%<BR> Function Purify(cString)<BR> cString = Replace(cString,"&#039","&#039&#124&#124chr(39)&#1 24&#124&#039")<BR> Purify = cString<BR> End Function<BR>%&#062;

  8. #8
    anamika Guest

    Default RE: Handling an

    use this code before inserting <BR>&#060;%<BR> Function Purify(cString)<BR> cString = Replace(cString,"&#039","&#039&#124&#124chr(39)&#1 24&#124&#039")<BR><BR> Purify = cString<BR> End Function<BR>%&#062;

  9. #9
    Join Date
    Dec 1969
    Posts
    226

    Default RE: Handling an

    you could try the following:<BR><BR>replace(searchstr,"&#039", "") this would remove the &#039 and therefore search for "Sees Candy"

  10. #10
    Join Date
    Dec 1969
    Posts
    12

    Default RE: Handling an

    Before inserting write this<BR>Replace(str,"&#039","~")<BR>"str" would be your request.form("whatever")<BR>"~" replaces "&#039" before inserting into the database,while retrieving it replaces back "&#039".<BR>I am sure this will help,try it and let me know.

Posting Permissions

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