You could do one of the following two:

1) Create a stored procedure and pass the parameters via the command object,
in which case the apostrophe problem will be eliminated.

2) Create your own request.form function, e.g. like this:

   Function FRequest(fld)
     'Retrieves a form field value and prepares it for use in sql string
     FRequest = Trim(Replace(Request.Form(fld),"'","''"))
   End Function

-Kim

> -----Original Message-----
> From: Chirag Shah [mailto:chiragiit@y...]
> Sent: 26. juli 2002 17:06
> To: ASP Databases
> Subject: [asp_databases] one more time Who is SQL guru..?
>
>
> This is as usual SQL apostrophe probelm:
>
> I know the answer using VBScript Replace function liek this:
> -----------------------------------------------------------
> strCompanyName = Replace(Request.form("NAME"), "'", "''")
> ----------------------------------------------------------
>
> but things become mess if I SQL statement is this big
>
> ---------------------------------------------------------
> "INSERT INTO Leo
> (Name,AccountNo,BusinessName,BusinessAddress,City,State,ZipCode,Ph
> one,Have_
> Computer)VALUES ('" & Replace(Request.form("Name"),"'","''") & "','" &
> Replace(Request.form("AccountNo"),"'","''") & "','" & Replace(Request.form
> ("BusinessAddress"),"'","''") & "','" & Replace(Request.form
> ("BusinessName"),"'","''") & "','" & Replace(Request.form
> ("City"),"'","''") & "','" & Request.form("State") & "','" & Request.form
> ("ZipCode") & "','" & Request.form("Phone") & "','" & Request.form
> ("Have_Computer") & "')"
> ---------------------------------------------------------------
>
> Any simple soltuion other than using Replace for each
> Request.form because
> I am going to have 37 different fields in this database.

This is as usual SQL apostrophe probelm:

I know the answer using VBScript Replace function liek this:
-----------------------------------------------------------
strCompanyName = Replace(Request.form("NAME"), "'", "''") 
----------------------------------------------------------

but things become mess if I SQL statement is this big

---------------------------------------------------------
"INSERT INTO Leo 
(Name,AccountNo,BusinessName,BusinessAddress,City,State,ZipCode,Phone,Have_
Computer)VALUES ('" & Replace(Request.form("Name"),"'","''") & "','" & 
Replace(Request.form("AccountNo"),"'","''") & "','" & Replace(Request.form
("BusinessAddress"),"'","''") & "','" & Replace(Request.form
("BusinessName"),"'","''") & "','" & Replace(Request.form
("City"),"'","''") & "','" & Request.form("State") & "','" & Request.form
("ZipCode") & "','" & Request.form("Phone") & "','" & Request.form
("Have_Computer") & "')"
---------------------------------------------------------------

Any simple soltuion other than using Replace for each Request.form because 
I am going to have 37 different fields in this database.