Solved

Single Quote In Key Problem

Posted on 2014-02-20
10
205 Views
Last Modified: 2014-03-17
Hi Folks,
In MS Access 2003 when I run the code below (part of a big chunk of code) it has a problem with the data in the following field when the field contains a single quote character.  e.g. (')
Dim PARM_ADDRESS_KEY As String

Data Example:
CDNL7M 0P9***5483 WALKER'S LINE**********************BURLINGTON ON L7M 0P9******************* *******************************************************************

   'MsgBox "Step 1110 - Update With Member Home Address Data."
   On Error Resume Next 'Mandatory to stop BOF / EOF Error.
   Dim RS_10_MEMBER_MSTR_1110 As New ADODB.Recordset
   Dim Cmd_MEMBER_HOME_ADDRESS_KEY As String
   Cmd_MEMBER_HOME_ADDRESS_KEY = "SELECT HOME_ADDRESS_KEY, SYS_MEMBER_KEY, SYS_MEMBER_LKUP " & _
                                 "FROM 10_MEMBER_MSTR " & _
                                 "WHERE HOME_ADDRESS_KEY = '" & PARM_ADDRESS_KEY & "';"
   RS_10_MEMBER_MSTR_1110.Open Cmd_MEMBER_HOME_ADDRESS_KEY, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   RS_10_MEMBER_MSTR_1110.MoveFirst
   While RS_10_MEMBER_MSTR_1110.EOF = False
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006.AddNew
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_KEY = [PARM_ADDRESS_KEY]
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!POSTAL_CODE = [WRK_POSTAL_CODE]
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_TYPE = "H01"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_TYPE_SEQ = "A01"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_KEY = RS_10_MEMBER_MSTR_1110!SYS_MEMBER_KEY
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_CONTEXT = "Member - Home Address 01"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_INFO = RS_10_MEMBER_MSTR_1110!SYS_MEMBER_LKUP
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_TABLE = "10_MEMBER_MSTR"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_FIELD = "HOME_ADDRESS_KEY"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!BASE_REC = [WRK_BASE_REC]
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006.Update
      [ADDRESS_USE_COUNTER] = [ADDRESS_USE_COUNTER] + 1
      RS_10_MEMBER_MSTR_1110.MoveNext
   Wend
   RS_10_MEMBER_MSTR_1110.Close

How can I eliminate the problem of the single quote being in a field that is used for a keyed lookup?

Thanks.
Bob Collison.
0
Comment
Question by:Bob_Collison
  • 5
  • 3
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
you have 3 options:
* use the "other" quotes:
WHERE yourfield = " & chr$(34) & yourstring & chr$(34))

* escape the quotes:
WHERE yourfield = '" & replace(yourstring, "'","''") & "' "

* use a parametrized query, if possible (recommended, actually)
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Angel III,
Thanks for the solution.

I have a problem with understanding syntax so could you please provide the code for my particular case for each of your solutions?

Thanks.
Bob Colison
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Angel III,
I tried the first two solutions with the following code and both didn't solve the problem.
"WHERE HOME_ADDRESS_KEY = " & Chr$(34) & PARM_ADDRESS_KEY & Chr$(34)
"WHERE HOME_ADDRESS_KEY = '" & Replace(PARM_ADDRESS_KEY, "'", "''") & "' "

Have I done something wrong?

I don't have any idea what you mean by * use a parametrized query so will wait for a further update from you.

Thanks.  Bob Collison
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Angel III,

With reference to my last update.  I didn't make the change to all of the occurrences of the code.  I have since done that and both Solutions 1 and 2 work.

Sorry for the misleading update.

I am still interested in the syntax for Solution 3 as you have suggested that it is the best one.

Thanks.  Bob Collison.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
0
 

Author Comment

by:Bob_Collison
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for Bob_Collison's comment #a39874642

for the following reason:

Multiple solutions were provided all of which are applicable in different situations.
Quick response and great solutions.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if my suggestions worked, please don't close with your own comment, but with mine
thanks
0
 

Author Comment

by:Bob_Collison
Comment Utility
Fast response and great solution!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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