Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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?
0
Luis_Romero
Asked:
Luis_Romero
1 Solution
 
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
 
ajitanandCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now