When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, Errors


Question:

Why do I get an error when I try to insert a value into a database that contains an apostrophe?


[Print this FAQ]

Answer: OK, you've been given an assignment to create a web page that collects your client's information and stores this information in a database. This is your first big project, and a chance for you to prove your worth to the company! All that you need to do is grab the client's company name and phone number! Painstakingly, you write the code, two pages, an HTML page with a form to collect the user input, and an ASP page to retrieve that information and slap it into a database. Your ASP page looks something like this:

<%
  'Get the form data
  Dim strPhoneNumber, strCompanyName
  strPhoneNumber = Request.form("PHONE")
  strCompanyName = Request.form("NAME")

  'Make connection to database
  ...

  'Construct SQL String
  Dim strSQL
  strSQL = "INSERT ClientTable (CompName,Phone) " & _
           "VAULES ('" & strCompanyName & _
           "','" & strPhoneNumber & "')"

  ...
%>

Ah, you've done a smashing job! You are truly an ASP expert, wait until the boss sees this, he will be so proud! Speaking of which, here he comes, wanting to give your app a little test. OK, no biggie. He sits down, loads up the form. For phone number he enters "123-345-6778" and for Company name he enters "Startbuck's Coffee." When he submits the form, he gets an ADO error!!! Oh crap! There goes that raise!

What happened? Why did an error occur? The reason has to do with apostrophes. strCompanyName contains an apostrophe, so when strSQL is constructed, it equals: INSERT ClientTable (CompanyName, Phone) VALUES('Startbuck's Coffee','123-345-6778')! Note the apostrophe! What is SQL going to think? Where does the Company Name string end? After the k in Startbuck's or after the last e in Coffee? Since SQL becomes confused, your script won't work!

So does this mean that your company can only take on clients who don't have an apostrophe in their company name? Thanksfully, no. SQL isn't very bright, but it isn't very dumb either. If SQL sees two apostrophes, one right after the other, it assumes you want just a single approstrophe its place. The two apostrophes don't confuse SQL into not knowing where the end is. So, all we have to do is tell the user to enter the company name as "Starbuck''s Coffee," right?

Well, no. That would be mean. What we will do is write a single line of code that will replace all instances of single apostrophes with two apostrophes. Here is how!

strCompanyName = Replace(Request.form("NAME"), "'", "''")

That will take the string in Request.form("NAME"), search for all single apostrophes, and replace them with two aprostrophes! That's all you need to do! Had you done this, your boss's test would have worked, you would have been promoted, you would have eventually become filthy rich! Now you know why the apostrophe thing is so important.

For more information on this error be sure to check out:

-- FAQ: How do I get SQL to accept an apostrophe in various queries, such as when I try to add a user named "O'Brien"?

-- Microsoft KB Article on the Issue

Happy Programming!


FAQ posted by Scott Mitchell at 3/2/2001 3:58:24 PM to the Databases, Errors category. This FAQ has been viewed 49,332 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article