Solved

Problem with apostrophe in SQL query

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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility

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 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:snyperj
Comment Utility
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)
Comment Utility
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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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)
Comment Utility
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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
Comment Utility
kaufmed- thanks, sorry I was 1/2 asleep when I posted that.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Actually:

"pass through query that pulls data from SQL server "

mx
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

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

15 Experts available now in Live!

Get 1:1 Help Now