For those that like ready made function type of things, here's something
for you. This functions converts a string to be suitable for handing over
to the back end, with appropriate characters escaped and single quotes
surrounding if necessary. It also handles NULL. I escape using \ so:
input output
----------------
hello 'hello'
bla'h 'bla\'h'
c:\linux 'c:\\linux'
<null> NULL
Here's the listing:
Function escapeString(text As Variant) As String
'Converts a string to be suitable for handing over to the back end, with all appropriate characters escaped
' and single quotes surrounding if necessary
Dim pos As Integer, ret As String
If IsNull(text) Then
escapeString = "NULL"
Else
ret = text
pos = 0
Do
pos = InStr(pos + 1, ret, "\") 'we must do \ first
If pos > 0 Then
ret = Left(ret, pos - 1) & "\" & Mid(ret, pos)
pos = pos + 1
End If
Loop Until pos = 0
pos = 0
Do
pos = InStr(pos + 1, ret, "'")
If pos > 0 Then
ret = Left(ret, pos - 1) & "\" & Mid(ret, pos)
pos = pos + 1
End If
Loop Until pos = 0
escapeString = "'" & ret & "'"
End If
End Function
For Corey's case, it would probably be useful to make a wrapper function to
add a comma on the end, eg..
Function wrapper(text As Variant) As String
escapeString = escapeString(text) & ", "
End Function
And then just do this:
> Here's the insert statement:
pg_cn.Execute "INSERT into itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h,d,itemno," & _
"finish,mtostatus) values(" & _
wrapper(rs!jobno) & wrapper(rs!area) & _
wrapper(rs!floor) & wrapper(rs!building) & _
wrapper(rs!room) & wrapper(rs!elevation) & _
wrapper(rs!phase) & wrapper(rs!cycle) & _
wrapper(rs!model) & wrapper(rs!w) & _
wrapper(rs!h) & wrapper(rs!d) & _
wrapper(rs!itemno) & wrapper(rs!finish) & _
escapeString(rs!mtostatus) & ")"
Remember to call escapeString instead of wrapper for the last one..
Someone may want to add this to a howto or something (hint, hint)..
-Cedar
On Thu, 14 Mar 2002, Henshall, Stuart - WCP wrote:
> Try double quoteing (ie '' for ').
> Also you can escape it using \'.
> Of course bveign able to use \ as an escape character means that when they
> really have one you have to give \\ :)
> Cheers,
> - Stuart
