' Problem
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: ' Problem

  1. #1
    Join Date
    May 2002
    Location
    Colombo,Sri Lanka
    Posts
    1,110

    ' Problem

    Hi Gurus
    I have a text field which contains a ' (eg:Hero's)

    If I do a Any SQL statment swith this THat will CRASH!!!!

    WHat I can do is replace' with ''
    But any any other alternative.

    Thankx in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    15
    Where to you use it in your SQL statement ?

    When you use it after LIKE or = you can do this :

    ... LIKE "Hero's" ... this will work.

    If Hero's is a table name you better rename it.

    I hope that will help you
    Best regards
    Markus

  3. #3
    Join Date
    May 2002
    Location
    Colombo,Sri Lanka
    Posts
    1,110
    Say gv_strServer = Hero's Day

    Insert Into Server (ServerName)
    VALUES ('" & gv_strServer & ")"

  4. #4
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    15
    I try it and this work :

    Set rsC = CurrentProject.Connection

    strBu = "Hero's"
    strSQL = "INSERT INTO ins VALUES(2,""" & strBu & """);"

    Debug.Print strSQL
    rsA.Open strSQL, ActiveConnection:=rsC, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

    If rsA.State <> adStateClosed Then
    rsA.Close
    End If

    Set rsA = Nothing

    In your Statement i think the Number of " is wrong. Try this out.
    I hope this will help you
    Best regards
    Markus

  5. #5
    Join Date
    Feb 2001
    Location
    Stamford CT USA
    Posts
    2,167
    Replace all the one single quote with two single quotes. For example, Hero's becomes Hero''s (note the second one has two single quotes instead of a double-quote). You can easily do so by Replace(szStr, "'", "''").

    I think anyway,
    -Cool Bizs

  6. #6
    Join Date
    Oct 2003
    Location
    srilanka
    Posts
    88
    One can suppress the apostrophe like this
    Public Function Validateapostrophe(KA As Integer) As Integer
    If KA = 39 Then
    Validateapostrophe = 0
    Else
    Validateapostrophe = KA
    End If
    End Function


    When Saving
    Fields("title")= Replace(TxtTitle.Text,"'", "!")
    ! - is a rarely used character so u can do with this one.
    when dislplaying
    TxtTitle.Text = Replace(.Fields("title"), "!", "'") & ""

    Hope this will help you

  7. #7
    Join Date
    May 2002
    Location
    Colombo,Sri Lanka
    Posts
    1,110
    I posted this question about one year back!!

    Good to know that old discussion are viewed by people

    Thankx rvvimal

    But what will happen if my text contain !

  8. #8
    Join Date
    Jul 2000
    Location
    Milano, Italy
    Posts
    7,726

    Coolbiz got it...

    (An old question...)
    As coolBiz said, trick is:
    double ' chars
    ie:
    strSlq"Say hello to john's car"
    strSql=replace(strSql,"[volor=red]'[/color],"''")
    This works for vb and sql server.

    (out of context?)
    If instead you're using VbScript to pass values to a Javascript code, and HTMLEncode or UrlEncode cannot help you, you can still use a kind of Javascript Encode via vbScript:

    Code:
    Function jsEncode(msgs) 
    	' Encodes message for preparation in a JavaScript function call.		
    	
    	jsEncode = msgs & ""
    	jsEncode = Replace(jsEncode, "\", "\\")
    	jsEncode = Replace(jsEncode, "'", "\'")
    	JSEncode = Replace(JSEncode,"""","&quot;",1,-1,1)
    	JSEncode = Replace(JSEncode,"<","&lt;",1,-1,1)
    	JSEncode = Replace(JSEncode,">","&gt;",1,-1,1)
    	jsEncode = Replace(jsEncode, vbCrLf, "\n")
    	jsEncode = Replace(jsEncode, vbLf, "\r")
    	jsEncode = Replace(jsEncode, vbTab, "\t")
    		
    End Function
    Then you can have:
    <td><input id="rdCust" type="radio" size="20" name="rdCust" value='<%=tmpIDCustomer%>'
    onclick="javascript:setTheseValues('<%=tmpIDCustomer%>',
    '<%=tmpCustomerIsLeaf%>','<%= jsEncode(tmpCustomerName)%>');"
    ></td>

    That is:
    In every language you can find a way to encode what the language would think it is a "special" char, to make it consider it a "normal" one. You have only to find how...
    ...at present time, using mainly Net 4.0, Vs 2010



    Special thanks to Lothar "the Great" Haensler, Chris Eastwood , dr_Michael, ClearCode, Iouri and
    all the other wonderful people who made and make Codeguru a great place.
    Come back soon, you Gurus.

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center