Solved

Problem with apostrophe in SQL query

Posted on 2011-09-28
15
384 Views
Last Modified: 2012-05-12
I use the following code to modify the query def of a pass through query that pulls data from SQL server by a username value.

The problem occurs when the username has an apostrophe in it, like: O'Connell

The resulting query def becomes:
SELECT * From OE_V_TC_Employees (NOLOCK) WHERE(UserName = 'O'Connell')

But the pass through query bombs with an ODBC run time error Error 3146 ODBC call Failed.

Is there a way I can alter the code that sets the query def so that it will work with names that have an apostrophe and those that do not?  I have no control over the usernames and they cannot be changed to remove the apostrophe.
Set qdf = CurrentDb.QueryDefs("PT_OE_V_TC_Employees_Username")
                              sSQL = "SELECT * From OE_V_TC_Employees (NOLOCK) " & vbCrLf
                              sSQL = sSQL & "WHERE(UserName = '" & varUsername & "')"
                              qdf.SQL = sSQL

Open in new window

0
Comment
Question by:snyperj
[X]
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
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 75
ID: 36799384
Try this:

Set qdf = CurrentDb.QueryDefs("PT_OE_V_TC_Employees_Username")
                              sSQL = "SELECT * From OE_V_TC_Employees (NOLOCK) " & vbCrLf
                              sSQL = sSQL & "WHERE(UserName = " &  Chr(34)  & varUsername & Chr(34) & ")"
                              qdf.SQL = sSQL

mx
0
 
LVL 75
ID: 36799725

Minor change ... getting rid of parens.

Set qdf = CurrentDb.QueryDefs("PT_OE_V_TC_Employees_Username")
                              sSQL = "SELECT * From OE_V_TC_Employees (NOLOCK) " & vbCrLf
                              sSQL = sSQL & "WHERE [UserName] = " &  Chr(34)  & varUsername & Chr(34)
                              qdf.SQL = sSQL
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 36801046
How about changing:

varUsername

to:

Replace(varUsername, "'", "''")

Thats:

Replace(varUsername, [double quote][single quote][double quote], [double quote][single quote][single quote][double quote])
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:snyperj
ID: 36811552
MX- your code changed the def to

SELECT * From OE_V_TC_Employees (NOLOCK)
WHERE [UserName] = "O'Connell"

...but that still gives the odbc error, and now the odbc error also occurs for jsmith


Kaufmed, your code changed the def to:

SELECT * From OE_V_TC_Employees (NOLOCK)
WHERE(UserName = 'O''Connor')

0
 
LVL 75
ID: 36812646
Then it's some other issue, because surround with double quotes - Chr(34) eliminates the issue with the single quote ... and "O'Connell" ... is correct.

mx
0
 

Author Comment

by:snyperj
ID: 36812675
hmmm... not sure why then... I still get the 3146 error on O'Connor and this is what I get on a user without the apostrophe


pic.jpg
0
 
LVL 75
ID: 36812684
Must have something to do with SQL Server ...

Maybe this:

Set qdf = CurrentDb.QueryDefs("PT_OE_V_TC_Employees_Username")
                              sSQL = "SELECT * From OE_V_TC_Employees (NOLOCK) " & vbCrLf
                              sSQL = sSQL & "WHERE Replace([UserName], Chr(39), "")  = " &  Chr(34)  & Replace(varUsername, Chr(34),"") & Chr(34)
                              qdf.SQL = sSQL
0
 

Author Comment

by:snyperj
ID: 36814062
That results in:


SELECT * From OE_V_TC_Employees (NOLOCK) WHERE Replace([UserName], Chr(39), ")  = "jsmith"


which also raises an error.


0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36814209
SQL Server uses single quotes to identify text fields.

Try:

strSQL = "SELECT * FROM OE_V_TC_Employees (NOLOCK) " _
            & "WHERE [UserName] = '" & Replace(varUserName, "'", "''") & "'"
qdf.SQL = strSQL

This should result in a query string that looks like:

SELECT * From OE_V_TC_Employees (NOLOCK) WHERE [UserName] = 'O''Connell'

where the '' between the O and the C in O''Connel is actually two apostrophes, which will be interpretted by SQL Server as a single apostrophe.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 36814575
Kaufmed, your code changed the def to:

SELECT * From OE_V_TC_Employees (NOLOCK)
WHERE(UserName = 'O''Connor')
Exactly. See fyed's post as to why  = )
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36814604
Kaufmed,

Sorry for stepping on your recommendation,  I missed it as I was reviewing the other recommendations and discussion.  

I like the way you explicitly spelled out the [double quote][single quote] ...  Think I'll use that in future explainations.

;-)
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 36814626
It's cool. I failed to explain why that method would be used, so your post is the perfect complement  = )
0
 

Author Closing Comment

by:snyperj
ID: 36815217
kaufmed- thanks, sorry I was 1/2 asleep when I posted that.
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36815291
Just for point of information,

If you are working in Access against a SQL Server database, you should say so in your initial post as syntax varies between Access and SQL Server, and you might consider adding the SQL Query zone, as that attract the SQL Server experts.
0
 
LVL 75
ID: 36816247
Actually:

"pass through query that pulls data from SQL server "

mx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 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