Solved

Issues with apostrophe and Access query

Posted on 2004-10-05
6
383 Views
Last Modified: 2010-05-18
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?
0
Comment
Question by:Luis_Romero
[X]
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
6 Comments
 
LVL 8

Expert Comment

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

~b

0
 
LVL 44

Expert Comment

by:Arthur_Wood
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.

AW
0
 
LVL 5

Accepted Solution

by:
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
cmd.Parameters.Add(workParam)


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

rgds,
Ajit Anand
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 7

Expert Comment

by:gsiric
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
0
 
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))

Bob
0
 

Author Comment

by:Luis_Romero
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.

LR
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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