Searching through emails - the @ returns none

[markphsd]

When i try to search a column in my database that contains emails, i can't get the query results i want if there is an @ symbol entered in the text box.

Anyone know the solution? Thanks

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[GUJUm0deL]

What does your query look like? What database? What datatype is the Email column?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[markphsd]

access db, but i'll be moving it to sql server shortly. (so if that will make a difference, let me know please)

very simple select statement, works for other querys without the @ symbol

SELECT ContactFirstName, ContactLastName, ContactName, ContactEmail, ContactPhone
FROM Contacts
WHERE ContactEmail like '%#form.search#%'

email is an access text
access db is setup in coldfusion with unicode

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[GUJUm0deL]

Hmmm, there's no reason why that SELECT statement wouldn't work. Is there an actual record with ContactFirstName, ContactLastName, ContactName, ContactEmail, ContactPhone associated with the ContactEmail? In other words, are you sure the recordcount is not 0?

I'm assuming you're not getting any errors as well, right?

Ty removing the % as a test and see if that returns any results.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[markphsd]

I can do the same search by shorting the search phrase and get results.

"mark" returns restults. "mark@" does not.

the search works in a query in access.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[jcrawford1982]

have you tried cfqueryparam?

 

[jcrawford1982]

Sorry, I should have provided an example. Here it is:

SELECT ContactFirstName, ContactLastName, ContactName, ContactEmail, ContactPhone
FROM Contacts
WHERE ContactEmail like <cfqueryparam CFSQLType="CF_SQL_VARCHAR" Value="%#form.search#%">

This metho should double as a protection against SQL injection.

 

[markphsd]

i thought about useing a queryparameter, but i didn't see how it would make a difference, i know it's being defined as a string.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[GUJUm0deL]

Did you try removing the %-sign and doing a test? Maybe its the %-sign that causing havoc.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[jcrawford1982]

If you are going to be moving this to a SQL db, I wouldnt drive yourself mad over this. You should try little tweaks, but your original code should work on SQL. As i have never used ans Access DB, I can only speculate that either the @ or the % may be reserved in Access.