Inserting Apostrophe generates error

Posted on 2003-03-18
Medium Priority
Last Modified: 2008-02-01
When I insert or update any record in a table from any ASP page that contains some form input text element, at the time of updating record that contains any apostrophe generates error.
I replaces single qoute to two times and then save. and now it saves.
but what is the solution of this problem.
Question by:AhmadKhan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

xenon_je earned 200 total points
ID: 8158507
The solution is that you need to 'prepare' a string before saving. So any ' character needs to be doubled.
This is the solution which (if I understood well) you already found........

Expert Comment

ID: 8158547
SQL looks for the ' to open and close strings, 'string', if you have an ' in the string it breaks the string abnormally 'st'ring' causing an error. depending on how your coding it you can use Replace(variabl, "'" "''") which will store the ' as a single not a double ''.

Expert Comment

ID: 8158560
Didn't spot you were using ASP

Try this

Function RequestFormAp(name)
          temp = Request.Form(name)
          temp = replace(temp,"'","''")
          RequestFormAp = temp
     End Function
     addid = RequestFormAp("addid")
     add1 = RequestFormAp("add1")
     add2 = RequestFormAp("add2")
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

LVL 10

Expert Comment

ID: 8160610
Pretty much the same as above just pass any strings to be inserted / updated to the SQLSafe function

Function SQLSafe(TheString)
     SQLSafe = Replace(TheString, "'", "''")
End Function

thequery = "insert into tblname (TextField) values (" & SQLSafe(myDirtyString)&")"

Expert Comment

ID: 9275759
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 11183448
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: xenon_je http:#8158507

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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