Re: Maybe more of a VB question, but here goes

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