Solved

Problem with apostrophe in SQL query

Posted on 2011-09-28
15
381 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
  • 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 47

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 47

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 47

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

25 Experts available now in Live!

Get 1:1 Help Now