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?
Luis_RomeroAsked:
Who is Participating?
 
ajitanandConnect With a Mentor Commented:
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
cmd.Parameters.Add(workParam)


The command parameters automatically fixes the quotes etc for you.
--------------------------------------------------

rgds,
Ajit Anand
0
 
bramsquadCommented:
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

0
 
Arthur_WoodCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
gsiricCommented:
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
0
 
Bob LearnedCommented:
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
0
 
Luis_RomeroAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.