SQL Search Command

  • Thread starter Thread starter Charles A. Lackman
  • Start date Start date
C

Charles A. Lackman

Hello,

I have created an application that queries an Jet4 Database (Access) and am
using the following command to return a particular results:

ASelect = "SELECT * FROM Employee WHERE [Description] LIKE '%" &
txtDescription.text & "%' ORDER BY [Last Name]"

This works great but when the Description textbox has a ' in it an error is
returned. Is there a way to allow ' inside the sql string?

I have tried:

ASelect = "SELECT * FROM Employee WHERE [Description] LIKE [%" &
txtDescription.text & "%] ORDER BY [Last Name]"

and it does not work. Any Suggestions would be greatly appreciated.

Thanks,

Chuck
 
Yes, Don't use concatenated SQL. If you click on the link, I've written
about why extensively in my blog, but it's just plain bad b/c it's buggy,
it's hack prone, and it's slow not to mention hard to maintain. You can do
a replace single qoutes with two single quotes but invariably you will
forget to do this at some point or some co-worker will and you'll spend time
tracking it down, fixing it and redeploying it. You'll be lucky if you find
it b/c ' aren't used in a lot of words so it'll probably not show itself
until its in the customer's hands. Anyway, you can do ASelect =
String.Replace("'", "''")

You can use the replace I mention above, but do so at your own risk.
Instead, use Parameter objects. Also, I'd recommend changing the Last Name
column to Last_Name or LastName and changing description to a non reserved
word b/c Access and OleDbClient don't play nice with such names and if you
or someone else forgets, it'll probably throw a syntax error.

HTH,

Bill
 
Thank You Bill,
Where is the Link?

Thanks,

Chuck

William Ryan eMVP said:
Yes, Don't use concatenated SQL. If you click on the link, I've written
about why extensively in my blog, but it's just plain bad b/c it's buggy,
it's hack prone, and it's slow not to mention hard to maintain. You can do
a replace single qoutes with two single quotes but invariably you will
forget to do this at some point or some co-worker will and you'll spend time
tracking it down, fixing it and redeploying it. You'll be lucky if you find
it b/c ' aren't used in a lot of words so it'll probably not show itself
until its in the customer's hands. Anyway, you can do ASelect =
String.Replace("'", "''")

You can use the replace I mention above, but do so at your own risk.
Instead, use Parameter objects. Also, I'd recommend changing the Last Name
column to Last_Name or LastName and changing description to a non reserved
word b/c Access and OleDbClient don't play nice with such names and if you
or someone else forgets, it'll probably throw a syntax error.

HTH,

Bill


Charles A. Lackman said:
Hello,

I have created an application that queries an Jet4 Database (Access) and am
using the following command to return a particular results:

ASelect = "SELECT * FROM Employee WHERE [Description] LIKE '%" &
txtDescription.text & "%' ORDER BY [Last Name]"

This works great but when the Description textbox has a ' in it an error is
returned. Is there a way to allow ' inside the sql string?

I have tried:

ASelect = "SELECT * FROM Employee WHERE [Description] LIKE [%" &
txtDescription.text & "%] ORDER BY [Last Name]"

and it does not work. Any Suggestions would be greatly appreciated.

Thanks,

Chuck
Click to expand...
Click to expand...
 
Back
Top