asked on

Issues with apostrophe and Access query

Hi,

I have a query done through ASP.net 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?

bramsquad

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) & ";"

~b

Arthur_Wood

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.

AW

ASKER CERTIFIED SOLUTION

ajitanand

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gsiric

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

Bob Learned

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))

Bob

Luis_Romero

ASKER

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.

LR