allowing use of apostrophe's in ASP form and SQL statement with variables

Posted on 2008-11-05
Medium Priority
Last Modified: 2012-05-05
Hi there,
We have an ASP form and backend database / table with about 80 fields.
The user fills out the form and their entries become variables used in an SQL INSERT string like this snippet..

Then if surname = O'Reilly for example, we will get this error:
Syntax error (missing operator) in query expression

How can we update the code to allow apostrophes to be used without breaking our SQL string?
stringSQL = "INSERT INTO table (surname,firstname) VALUES ('" & request.form("txtSurname") & "','" & request.form("txtFirstName") & "')"

Open in new window

Question by:northtecicts
LVL 17

Accepted Solution

HuyBD earned 800 total points
ID: 22892004
try this
stringSQL = "INSERT INTO table (surname,firstname) VALUES ('" & replace(request.form("txtSurname"),"'","'") & "','" & replace(request.form("txtFirstName"),"'","'") & "')"

Open in new window

LVL 54

Assisted Solution

b0lsc0tt earned 200 total points
ID: 22892197

You need to escape the apostrophe by placing another one in front of it.  E.g.

Replace(Request.Form("txtSurname"), "'", "''")

That will make it safe for an SQL statement or query.  Let me know if you have any questions or need more information.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question