Using ASP code to Insert single quote into Oracle

Posted on 2003-11-14
Last Modified: 2008-01-16
I am created  apply information web application form.  I would like to save user input information with single quote into oracle databace.
 I try used function Replace(SQL,"'","''")
for my code example:  
 sql="insert into Person "
 sql= sql & "( lastname, firstname ) " 
 sql= sql & "values ('"
 sql= sql & request.form(replace("lastname","'","''")) & "', '"
 sql= sql & request.form(replace("firstname","'","''")) & "') "
 set objrec = objconn.execute(sql)

But the replace funtion doesn't work in oracle database.  Please help!  How can I use  ASP code to  insert single quote into oracle databace? Thanks.
Question by:LiLib
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
LVL 19

Accepted Solution

Dexstar earned 50 total points
ID: 9750488

>  I try used function Replace(SQL,"'","''")
> for my code example:  
>  sql="insert into Person "
>  sql= sql & "( lastname, firstname, ) " 
>  sql= sql & "values ('"
>  sql= sql & request.form(replace("lastname","'","''")) & "', '"
>  sql= sql & request.form(replace("firstname","'","''")) & "') "
>  set objrec = objconn.execute(sql)

Try this:

Create a function to do the modification for you, like this:

     Function FixSQL( strSQL )
          FixSQL = "'" & Replace(strSQL, "'", "''") & "'"
     End Function

Then use it like this:
     sql="insert into Person "
     sql= sql & "( lastname, firstname, ) " 
     sql= sql & "values ("
     sql= sql & FixSQL(request.form("lastname")) & ", "
     sql= sql & FixSQL(request.form("firstname")) & ") "

Hope That Helps,

Author Comment

ID: 9750555
Hi Dex,

Thank you very much.  I will try it.

LVL 19

Expert Comment

ID: 9750583

> Thank you very much.  I will try it.

If it works for you, please accept my answer.  If it doesn't work for you, post a message saying what's wrong, and we'll work together to fix it.  :)

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!


Expert Comment

ID: 9751467
The replace function is working fine, but you are using it in wrong way. You can't replace the Form control with this function.
( as used in your code:   request.form(replace("lastname","'","''")))

To replace the value in request object, Try the following:

Let me know if it works.


Author Comment

ID: 9760365
Hello Dex* and Jammyg,
Both your functions work perfectly in my project.  Thank both of you for nice help.

Expert Comment

ID: 12154831
This Question is now classified abandoned as no comment has been added to this question in more than 21 days.

I will leave the following recommendation on this question to the moderators in the Cleanup topic area:
    Accept: Dexstar {http:#9750488}

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

EE Cleanup Volunteer
LVL 19

Expert Comment

ID: 12176100
Works for me...


Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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