[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

PERL Roth ODBC Escape Single Quote MSAccess

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
jchauncey60
Asked:
jchauncey60
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
jchauncey60Author Commented:
Thanks for the quick feedback.  That doesn't get me completely there.  There is some trick with the Roth modules and Perl.
0
 
jchauncey60Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes - that was my suggestion to you:

"Access expects you to include 2 single quotes"

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now