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

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.


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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 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