' characters in queries Help! 
Author Message
 ' characters in queries Help!

I am having a problem with a query that adds apostropies
in the query causing it to not show any results. I would
appreciate some help.

I have a parameterized query in a dataenvironment that
ends in

LIKE ?

In my code I have aa string that is the following:
txtQuery1Text = "[%] '" & txtQuery1.Text & "'" & "[%]"
I then use txtQuery1Text as the parameter but if the user
enters f for example, the query string shows %'f'% and the
query wont show any results. But if i leave out the
certain formatting and just have:

txtQuery1Text = "[%]" & txtQuery1.Text & "[%]"

if shows %f% which also doesnt work.

Any suggestions on why this wont work?????



Sun, 22 May 2005 02:42:52 GMT  
 ' characters in queries Help!
Matt,

You could double all single quotes inside your value using Replace VB
function

txtQuery1Text = "[%] '" & Replace(txtQuery1.Text,"'","''") & "'" & "[%]"

another way is to pass value as a parameter. Then provider should care about
that situation itself

--
Val Mazur
Microsoft MVP


Quote:
> I am having a problem with a query that adds apostropies
> in the query causing it to not show any results. I would
> appreciate some help.

> I have a parameterized query in a dataenvironment that
> ends in

> LIKE ?

> In my code I have aa string that is the following:
> txtQuery1Text = "[%] '" & txtQuery1.Text & "'" & "[%]"
> I then use txtQuery1Text as the parameter but if the user
> enters f for example, the query string shows %'f'% and the
> query wont show any results. But if i leave out the
> certain formatting and just have:

> txtQuery1Text = "[%]" & txtQuery1.Text & "[%]"

> if shows %f% which also doesnt work.

> Any suggestions on why this wont work?????



Sun, 22 May 2005 02:46:58 GMT  
 ' characters in queries Help!
Thank you for your suggestion but funny enough i get the
same exact results with or without the replace function.
Any other suggestions? i want to be able to do this
without having to have the user manually enter the %'s.

Quote:
>-----Original Message-----
>Matt,

>You could double all single quotes inside your value
using Replace VB
>function

>txtQuery1Text = "[%] '" & Replace

(txtQuery1.Text,"'","''") & "'" & "[%]"
Quote:

>another way is to pass value as a parameter. Then

provider should care about
Quote:
>that situation itself

>--
>Val Mazur
>Microsoft MVP


message

>> I am having a problem with a query that adds apostropies
>> in the query causing it to not show any results. I would
>> appreciate some help.

>> I have a parameterized query in a dataenvironment that
>> ends in

>> LIKE ?

>> In my code I have aa string that is the following:
>> txtQuery1Text = "[%] '" & txtQuery1.Text & "'" & "[%]"
>> I then use txtQuery1Text as the parameter but if the
user
>> enters f for example, the query string shows %'f'% and
the
>> query wont show any results. But if i leave out the
>> certain formatting and just have:

>> txtQuery1Text = "[%]" & txtQuery1.Text & "[%]"

>> if shows %f% which also doesnt work.

>> Any suggestions on why this wont work?????

>.



Sun, 22 May 2005 03:02:34 GMT  
 ' characters in queries Help!
The problem is that the single quotes are needed to "frame" the LIKE
expression.

    "SELECT author FROM authors WHERE author LIKE 'William%'  "

Make sure the single quotes are OUTSIDE of the whole expression as shown
above. And like Val suggested, you should use a Command Parameter to specify
these values and let ADO deal with the framing on its own.

--
________________________________
William (Bill) Vaughn, Author, Trainer, Mentor
Microsoft Pacwest Regional Director, MVP
"ADO.NET and ADO Examples and Best Practices for VB Programmers--2nd
Edition" (ISBN: 1-893115-68-2)
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
________________________________

Quote:
> I am having a problem with a query that adds apostropies
> in the query causing it to not show any results. I would
> appreciate some help.

> I have a parameterized query in a dataenvironment that
> ends in

> LIKE ?

> In my code I have aa string that is the following:
> txtQuery1Text = "[%] '" & txtQuery1.Text & "'" & "[%]"
> I then use txtQuery1Text as the parameter but if the user
> enters f for example, the query string shows %'f'% and the
> query wont show any results. But if i leave out the
> certain formatting and just have:

> txtQuery1Text = "[%]" & txtQuery1.Text & "[%]"

> if shows %f% which also doesnt work.

> Any suggestions on why this wont work?????



Sun, 22 May 2005 03:33:36 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. character '^' inside a sql query

2. Need help with SQL query 'AND'ing a field against a constant

3. Help I can't get Character Fields From DBASE file that contain Null bytes

4. I'm having a problem with a 'special' character in a query

5. Typing '*' character in Richtext control

6. SQL and ' (ASCII 39) character problem

7. Escape '%' character

8. '|' Character in SQL Syntax

9. '|' Character in SQL Syntax

10. Escape Character for 'Replace' Function

11. VBScript and MS Agent character 'instances'

12. How to escape '%' character

 

 
Powered by phpBB® Forum Software