Storing appostrophe's in SQL

J

Jeff

I have a text box that allows the user to type in free
form text. I want to save it to a SQLServer database
table. If the user types in <Here's Johnny!>, I can insert
it into SQLServer by inserting the text string <Here''s
Johnny!>

My question: is there a function that does this, or do I
process the text string before I insert it to double all
the appostrophe's?

Thanks!
 
M

Michel Pierron

Hi Jeff,
for example:
Function FixApostrophy(ByVal sSQL As String) As String
Dim sPhrase As String, wLength As Integer, m As Integer
wLength = Len(sSQL)
For m = 1 To wLength
If Mid(sSQL, m, 1) = "'" Then
sPhrase = sPhrase & "''"
Else
sPhrase = sPhrase & Mid(sSQL, m, 1)
End If
Next
FixApostrophy = sPhrase
End Function

MP
 
R

Rob Bovey

Hi Jeff,

If your code will always be running on Excel 2000 or higher versions of
Excel you can use the VBA Replace function to do this.

Dim szSQLString As String
szSQLString = Replace(txtEntry.Text, "'", "''")

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
J

Jeff

That's a great solution for when we migrate everyone to >=
Excel 2000. But for now I have a lot of users on '97.

Thanks - Jeff
 
M

Michel Pierron

Hi Jeff,
you can use Application.Substitute
Dim szSQLString As String
szSQLString = Application.Substitute(txtEntry.Text, "'", "''")

MP
 
Top