Solved

Problem with apostrophe in SQL query

Posted on 2011-09-28
15
383 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
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: 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 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