Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with apostrophe in SQL query

Posted on 2011-09-28
15
Medium Priority
?
388 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 2000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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
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
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
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 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