' 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.

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

Say gv_strServer = Hero's Day

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

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

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

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

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 !

(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,"""",""",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...