Problem with Single quote in LIKE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Problem with Single quote in LIKE clause

Author

Topic

loacker5
Starting Member

10 Posts

Posted - 2004-11-16 : 01:02:33

Hi i'm searching for a record in a table and used a string with a single quote for search and gets an error "Invalid argument".. how do you resolve this?

dim Name as string
Name= "People's"

"Select * From Customer Where CustomerName Like '"& Name &"'"

The sql tends to see my syntax as CustomerName Like 'People's' which is wrong..

I tried putting a bracket between them but doesn't work as well..

Does anyone know the right syntax?

regards, Lloyd

Kristen
Test

22859 Posts

Posted - 2004-11-16 : 01:35:33

You need to double-up any embedded single quotes

Select * From Customer Where CustomerName Like 'People''s'

Kristen

loacker5
Starting Member

10 Posts

Posted - 2004-11-16 : 01:46:44

Thanks for the reply..

Yes it's ok.. but what if I want to search dynamically in other words the search string varies..? It could have or not have a punctuation in it.

regards, Lloyd

Kristen
Test

22859 Posts

Posted - 2004-11-16 : 02:56:57

double-up any embedded single quotes

Dunno exact syntax, but presumably its something like

Name = REPLACE(Name, "'", "''")

Kristen

loacker5
Starting Member

10 Posts

Posted - 2004-11-16 : 03:46:29

That'll be a good alternative.. but does anyone know how the syntax works without using any outside function to make the syntax correct?

loacker5
Starting Member

10 Posts

Posted - 2004-11-16 : 03:56:41

I mean.. there should be a right way of using it since the developer would have tested all possible string for that right?

regards, Lloyd

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-16 : 09:51:57

the right way is to use a query and a parameter.

are you trying to return a DAO recordset?

if so, something like this is prefered:

1) create a query like what you wrote, but leave a parameter in place of where you are putting the string. i.e., save this SQL below as a regular Access query, call it "TestQuery":

Select * From Customer Where CustomerName Like [pCustName]

Note that if you try to run the query, it will prompt you for pCustName -- that is a parameter.

2. in your code, you can execute the query (returning a recordset) passing in a parameter like this:

dim db as DAO.database
dim q as DAO.QueryDef
dim r as DAO.Recordset

set db = currentdb

set q = db.querydefs("TestQuery")
q.parameters!pCustName = somevariable ' here you assign the value you need
set r = q.OpenRecordset()

that way, you are passing in values to a SQL statement not building SQL strings dynamically -- no conversion or handling of quotes is necessary. There are other ways to do this as well, using global variables and custom VB functions if you need to return data for a report or form instead of in a recordset in VB.

It all depends on exaclty what you are trying to produce for results. But I try to always use the approach of passing parameters "by value" as opposed to building SQL strings and passing as text strings, whenever possible. (note that this applies to SQL Server as well as to Access)

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-16 : 09:56:28

Note that if the SQL statement varies a lot, you can create a temporary query "on the fly" instead of saving one in your MDB file, like this:

dim db as DAO.database
dim q as DAO.QueryDef
dim r as DAO.Recordset

set db = currentdb

set q = db.createquerydef("","select * from customers where custName like [pCustName]")
q.parameters!pCustName = somevariable ' here you assign the value you need
set r = q.OpenRecordset()

This is a really cool trick that almost no one ever uses. it's quite efficient especially if you re-use the SQL over and over, and again, it requires no parsing of SQL statements if you pass everything as a parameter.

- Jeff

loacker5
Starting Member

10 Posts

Posted - 2004-11-18 : 23:02:53

yes it's ok for dao. but jeff, can you work on the syntax for ado? how do i open the recordset with parameter?

thanks and best regards,
Lloyd

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-19 : 09:18:52

ADO is the same basic thing:

    

Dim c As ADODB.Command
Dim r As ADODB.Recordset
    
Set c = New ADODB.Command
c.ActiveConnection = CurrentProject.Connection
c.CommandText = "select * from Sites where Company LIke [pCo] & '%'"
c.Parameters.Refresh
    
c.Parameters("pCo").Value = "02"

Set r = c.Execute
Do While Not r.EOF
    Debug.Print r!Company
    r.MoveNext
Loop
r.close

Does this help? And, again, you are better off creating queries and saving them in the database and opening them directly, as opposed to generating SQL text on the fly.

- Jeff

nicentral
Starting Member

2 Posts

Posted - 2004-12-21 : 15:25:37

I've tried the ADO method on Access 2000, XP, and 2003 to no avail.

Here's the code that I am using

Dim c As ADODB.Command

Dim r As ADODB.Recordset

Set c = New ADODB.Command
c.ActiveConnection = CurrentProject.Connection
c.CommandText = "SELECT * FROM Query1 WHERE NAME LIKE [pCo]"
c.Parameters.Refresh

c.Parameters("pCo").Value = "AQL's"

Set r = c.Execute
Do While Not r.EOF
Debug.Print r!Name
r.MoveNext
Loop

What I get when I run this is an error 3265 Item cannot be found in the collection corresponding to the requested name or ordinal.

Am I missing something here?

regards, Andy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-21 : 16:15:47

for some reason, ADO has trouble with named parameters. I just noticed this as well. So, refernce the parameters by their position, as opposed to their name, and it works fine. That is, try changing:

c.Parameters("pCo").Value = "AQL's"

to

c.Parameters(0).Value = "AQL's"

- Jeff

nicentral
Starting Member

2 Posts

Posted - 2004-12-21 : 16:18:22

Thanks Jeff, that worked great!

-- Andy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-21 : 16:30:33

no problem. you can do this, to, if you like:

c.CommandText = "SELECT * FROM Query1 WHERE NAME LIKE ?"

since ADO doesn't work well with named parameters anyway, you can just use ?.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources