Does anyone have a VBScript function that will help escape common SQL
Characters.

An example would be something like inserting this SQL command with this string

string = "smith, jacob's"
SQL = "INSERT INTO MyTable (DisplayName) Values ('" & String & "')"
conn.Execute(SQL)



This would return an error. Other common charactors may include include \ '
% OR and other items. There may also be multiple entries of one or more of
these chars in the same string. any help would be appriciated. The source of
data is unpredictable.

Thanks

Bob Smith wrote:

> Does anyone have a VBScript function that will help escape common SQL
> Characters.
>
> An example would be something like inserting this SQL command with
> this string
>
> string = "smith, jacob's"
> SQL = "INSERT INTO MyTable (DisplayName) Values ('" & String & "')"
> conn.Execute(SQL)
>
> This would return an error. Other common charactors may include
> include \ ' % OR and other items. There may also be multiple entries
> of one or more of these chars in the same string. any help would be
> appriciated. The source of data is unpredictable.

Use parameters instead of dynamic sql - the need for escaping characters
disappears ... along with the need to worry about delimiting data
values. Like this:

SQL = "INSERT INTO MyTable (DisplayName) Values (?)"
set cmd=createobject("adodb.command")
arparms=array("smith, jacob's")
set cmd=createobject("adodb.command")
cmd.commandtext=SQL
Set cmd.activeconnection=conn
cmd.execute ,arparms,129
'129=adCmdText + adExecuteNoRecords



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.