Issues with apostrophe and Access query

Posted on 2004-10-05
Last Modified: 2010-05-18

I have a query done through to an access database.

The query goes: Select * from TableName where where sLastName Like ' & strName & '

The issue I am encountering is when I try to query someone by the name "O'neal". I get an OledbException error. I know the apostrophe is creating the issue so this is what I am trying:

If strTemp.IndexOf("'", 0, 1) Then
   strTemp.Replace(Chr(39), Chr(34)) ' to include only a quote instead of a single quote
End If

However, the value in the strTemp remains the same as if the replacement never happened. What am I doing wrong?
Question by:Luis_Romero
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

Expert Comment

ID: 12230954
your replacing the single quote  --> ' <-- with the double quote --> " <--

so what its doing is ending your string prematurely, so it would look like this:

"Select * from TableName where where sLastName Like "O"neal"

just use the double quote convention

yourSQL = "Select * from TableName where where sLastName Like " & Chr(34) & strName & Chr(34) & ";"


LVL 44

Expert Comment

ID: 12231236
or, you can apply the Replace function to strName:

yourSQL = "Select * from TableName where where sLastName Like '*" & Replace(strName, "'", "''") & "*'")

why are you using the LIKE operator, rather than =?

If you are going to use LIKE, then you should include the Wildcard charactrer (*) in the Where Clause.


Accepted Solution

ajitanand earned 250 total points
ID: 12234053
Here is what I "used to use" for this purpose:
Function FixQuotes(ByVal myString as String) AS String
        Return "'" & Replace(myString, "'", "''") & "'"
End Function

sqlString = "Select * from TableName where where sLastName Like " & FixQuotes(strName)

Although the better (actually longer but more elegant) way is to use the command object and append the values as parameters to your query as :

sqlString = "Select * from TableName where where sLastName Like "
Dim mySqlCommand As SqlCommand = New SqlCommand(sqlString, myConnection)

workParam = new OleDbParameter("MyLikeClause", OleDbType.WChar,50)
workParam.Value = strName

The command parameters automatically fixes the quotes etc for you.

Ajit Anand
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.


Expert Comment

ID: 12235436
Replace dont replace characters in current string. You get new string instance with replaced characters.
So you need to assign result to strTemp.

If strTemp.IndexOf("'", 0, 1) Then
  strTemp =  strTemp.Replace(Chr(39), Chr(34)) ' to include only a quote instead of a single quote
End If
LVL 96

Expert Comment

by:Bob Learned
ID: 12237463
To create a SQL query looking for a single quote, you need to escape the sequence by using two single quotes.

strTemp =  strTemp.Replace(Chr(39), Chr(39) & Chr(39))


Author Comment

ID: 12240091
Thank you for all the response. Using the Parameter criteria with the OLE was the best solution because it takes care of a number of characters that otherwise I would had to manage manually.
Thank you all.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Reactjs with .NET 3 127
PowerShell: ForEach ($y in $x.licenses | where {....} 3 63
Consume a webservice via VB in Visual Studio 2015 3 45
Call to SQL server times out 5 55
In my previous article ( we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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