SQL INSERT 1

[KarloZ]

hello everyone, i have this small problem with sql insert. i have a variable that holds the data with a single quote included as part of the string and is to be inserted in a table.
whenever i execute the docmd.execute insert- its giving errors. i am assuming its the quote that caues it. is there a solution for that? coz there are strings that needs the single quote, what will i do? thank you in advance

e.g.
cTitle = "Sam's Adventure"
docmd.execute "Insert into TITLE(title)" _
& " values('"& cTitle &"');"

 

[MoGryph]

You're going to have to write a procedure to parse out any extraneous characters that confuse SQL, and handle them accordingly.

Here's an example of one, that takes care of your problem, but you probably also want to include in it, some checking for a quote (&quot as well.

Function Sequelize(strIn As String) As String
    Dim i As Integer
    i = InStr(strIn, "'")
    While i > 0
        strIn = Left(strIn, i - 1) & "''" & Mid(strIn, i + 1)
        If i <> Len(strIn) - 2 Then
            i = InStr(i + 2, strIn, &quot;'&quot;)
        Else
            i = 0
        End If
    Wend
    Debug.Print strIn
    
End Function

Then, you'd just plug in the parsing function at the point of insertion in the string, like this:

DoCmd.RunSQL "INSERT INTO TITLE(title)" _
     & " values('"& Sequelize(cTitle) &"');"

If you don't see the trick by reading the code, it's fairly simple. You need to replace any single quotes ('), with 2 single quotes. What I haven't included is that you'd also have to replace any double quotes (&quot found, with 2 double quotes.

That should do it for ya.

-MoGryph
[8O)

 

[KarloZ]

thanks, that was helpful!

 

[MoGryph]

Hey Karloz-

Hey, here's something else you might want to look at...
I don't use Access2000 too much, so didn't know the function existed. (Perhaps it exists in 97 too, but that's at work, and I'm home now).

MUCH faster revision, using the REPLACE() function. Check it out in Access97, it might exist- I've just never noticed before, and I think Access2000's Help (which I personally hate) pulled it up with a higher rank than 97's.

My guess is that it's much faster, since it most likely uses Access's .DLL file functions to perform it, and they're faster than any code we can write in VBA. Plus- I've added the additional code to take care of the Double Quotes. (&quot Incidentally- if you cross over to some other languages, they'll complain about back-slashes (\). Many languages like to use that as an "Escape Code", meaning, use the next character literally. We shouldn't have to worry about that here, but if you run into it in the future, do NOT use any of the code I've given you, but replace any (&quot with (\&quot, not ("&quot. If that's confused you, don't worry about it. Just use the following code as far as Access is concerned 8O).

Function SQLize(strIn As String) As String

'   CHR(34) is a Double Quote("), CHR(39) is a Single Quote(')

    Dim Tmp As String
    Tmp = Replace(strIn, Chr(34), Chr(34) & Chr(34), , , vbBinaryCompare)
    Tmp = Replace(Tmp, "'", "''", , , vbBinaryCompare)
    SQLize = Tmp
End Function