Solved

VB.net/SQL dealing with apostrophes in strings

Posted on 2007-11-29
4
4,585 Views
Last Modified: 2010-04-21
Hi,
I am trying to do a very simple program with a VB.net front end and an Access 2002 back end. I have a list of names and since I am in Ireland many of them have an apostrophe in them. The program has no problems with names like "Sean Murphy" but needless to say the OLEDBCommand gets a bit antsy when it's "Sean O' Murphy". Is there anyway that I can get SQL to realise that the apostrophe is part of the string and to ignore it and treat it like an ordinary character?

Open in new window

0
Comment
Question by:BozM
4 Comments
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 500 total points
ID: 20375362
Replace Sean O' Murphy with Sean O'' Murphy before sending it to the database.  Note that I used two single quotes there, not one double quote.  That will escape the quote so that it will be stored in the database correctly.  

Another option I highly recommend is to use the parameterized queries for OleDb.  Then, instead of a statement like this:
    cmd.CommandText = "INSERT INTO Names (FullName) VALUES ('Sean O''Murphy')"

You'd have a string like this:
    cmd.CommandText = "INSERT INTO Names (FullName) VALUES (?)"

And add paraemters like this:
    cmd.Parameters.Add("?").Value = "Sean O'Murphy"

Notice that I was able to use a single quote there with no extra work.  This will also let me save the command for later use, and I can just change the value of the parameter and run it again.  So I set the command up once, and then as I go through the loop I just have something like this:
    cmd.Parameters(0).Value = strNameVariable
    cmd.ExecuteNonQuery()

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20375381
Double up the single quotes inside the string.
Easiest way is to write a function -
Public Function QuoteString(Value as string) as string
Quotestring =  "'" & Replace(Value, "'", "''") & "'"
End function
0
 
LVL 5

Expert Comment

by:mydasx
ID: 20375575
Paramaterize your sql statements rather then using adhoc queries.  This will fix this issue as well as prevent sql injection hacking on your application.
0
 

Author Closing Comment

by:BozM
ID: 31411714
That's great, thanks to all of you for your help. I used your syntax for the parameterised queries for INSERT and UPDATE statements and they worked perfectly. Thank you.
0

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

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 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

13 Experts available now in Live!

Get 1:1 Help Now