Solved

PERL Roth ODBC Escape Single Quote MSAccess

Posted on 2011-02-27
4
563 Views
Last Modified: 2012-08-14
I have an SQL statement that the value I am inserting / updating in the DB has an embedded single quote (e.g. Let's Grow). I cannot figure out how to escape the string in PERL using Roth's ODBC utilitiles. My backend DB is MS/Access 2007

Any help would be appreciated.
0
Comment
Question by:jchauncey60
  • 2
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34993434
Access expects you to include 2 single quotes:

UPDATE MyTable SET MyField="Let''s Grow"

Note that is [DQ]=[DQ]Let[SQ][SQ]s Grow[DQ]

[DQ]= "
[SQ] = '

I'm not sure how you'll do that in Perl, but that is the syntax Access expects you to use.
0
 

Author Comment

by:jchauncey60
ID: 34993810
Thanks for the quick feedback.  That doesn't get me completely there.  There is some trick with the Roth modules and Perl.
0
 

Author Comment

by:jchauncey60
ID: 34993889
You got me close to the answer and I figured the rest of it out.

MS/Access wants the values surrounded by single quotes and then you escape the embedded single quote with two single quotes

$value = Let's Grow
$value =~ s/'/''/g;

So in the SQL string it would look like 'Let''s Grow'
0
 
LVL 84
ID: 34996221
Yes - that was my suggestion to you:

"Access expects you to include 2 single quotes"

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

895 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

14 Experts available now in Live!

Get 1:1 Help Now