?
Solved

Comment character problem in SQL statement / ASP VBscript

Posted on 2004-11-15
4
Medium Priority
?
388 Views
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?

Example:

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

Delirium
0
Comment
Question by:deliriumxx
  • 2
  • 2
4 Comments
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 300 total points
ID: 12590638
' is a reserved character to represent start and end of string value, to escape it try replace the single quote ' with 2 single quotes ''

like:

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

cheers 8-)
0
 

Author Comment

by:deliriumxx
ID: 12590676
Invalid character is what I get now. Hm...

Any other ideas? LOL
0
 

Author Comment

by:deliriumxx
ID: 12590680
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 :) !
0
 
LVL 54

Expert Comment

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

Featured Post

Independent Software Vendors: 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

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

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