Help with SQL inserting strings into table

[Ian Baker]

I am using the following sql to insert the values of certain fields on a
form into another table and are trying to ascertain the right way to treat
strings. The problem is if the user enters a ' as in O'Brian or " as in
"said this" in the field an error occurs. If I use '" & MyField & "' then
the user can't use ' or if I use """ & MyField & """ then the user can not
use "

strInsert = "INSERT INTO tblKnowledgeBase ( Type, Category, AssetType, " & _
"CallDescription, MakeModel, Resolution, CallID ) " & _
"SELECT """ & ctlTypeID.Column(1) & """ AS Expr1, """ &
ctlCategoryID.Column(2) & """ AS Expr2, " & _
"""" & ctlAssetID.Column(2) & """ AS Expr3, """ & ctlCallDescription &
""" AS Expr4, " & _
"""" & ctlAssetID.Column(3) & """ AS Expr5, """ & strResolution & """ AS
Expr6, " & CallID & " AS Expr7;"

Any suggestion is appreciated
--
Regards
Ian Baker
Jackaroo Solutions Pty Ltd
(Download Jackaroo IT - an IT Mgmt and Help Desk application at
http://jackaroo.net.au)

 

[Dan Artuso]

Hi,
Try using Chr(34), which the ascii for a double quote.
Something like this:
& Chr(34) & MyField & Chr(34)

 

[Graham Mandeno]

Hi Ian

If you double any instance of your delimiter quote in the string then your
problem will be solved.

So:
Jack "the Ripper" O'Brien
Can be written as:
'Jack "the Ripper" O''Brien'

You can do this in one line, but it's easier to write a small function to do
it for you:

Public Function SqlText( InVal as Variant ) as String
SqlText = "'" & Replace(Nz(InVal), "'", "''") & "'"
End Function

Also, you can use the VALUES( ) keyword in your SQL if you are inserting
constant values instead of a selection from a recordset. This leaves you
with the rather simpler:

strInsert = "INSERT INTO tblKnowledgeBase " & _
( Type, Category, AssetType, CallDescription, " & _
MakeModel, Resolution, CallID ) VALUES (" & _
SqlText(ctlTypeID.Column(1)) & ", " & _
SqlText(ctlCategoryID.Column(2)) & ", " & _
SqlText(ctlAssetID.Column(2)) & ", " & _
SqlText(ctlCallDescription) & _
SqlText(ctlAssetID.Column(3)) & ", " & _
SqlText(strResolution) & ", " & _
CallID & ");"

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.