Comment character problem in SQL statement / ASP VBscript

Posted on 2004-11-15
Last Modified: 2008-03-17
I am using some variables in my INSERT INTO SQL statement and I noticed that when one of my variables contains a VB comment  (') character I get an error message about sql string syntax error. Logcaly I understand what is hapening, but still ..... is there a simple solution to this?


This is what my variable looks like

MyVar = "abc123-2'"  //     abc123-2' - so the variable has ' sign in the end

And the statement is

mySQL = "INSERT INTO "MyTableName" (Column1, Column2)  VALUES ('" & TextVar & "','" & MyVar & "')"

I wonder,,, :)

Thanks in advance

Question by:deliriumxx
    LVL 48

    Accepted Solution

    ' is a reserved character to represent start and end of string value, to escape it try replace the single quote ' with 2 single quotes ''


    mySQL = "INSERT INTO "MyTableName" (Column1, Column2)  VALUES ('" & replace$(TextVar,"'","''") & "','" & Replace$(MyVar,"'","''") & "')"

    cheers 8-)

    Author Comment

    Invalid character is what I get now. Hm...

    Any other ideas? LOL

    Author Comment

    yayyyyy.... :)  I had a little typo... works :)  

    Thank you very very much!! I don't know why I didn't think of Replace....
    Guess the hours got to my concentration.

    Thanks,,, a lot :) !
    LVL 48

    Expert Comment

    by:Ryan Chong
    It's ok, deliriumxx. Glad could make helps here 8-) cheers

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now