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

Posted on 2008-11-05
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

    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


    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now