Code Error Problem

[anon007]

I am useing ms sql express and the code below works if I don't try to query multiple fileds. Here is the code and and the error can enyone help witht his it worked with access however not with sql?

sql = "SELECT * FROM [Project] WHERE [Project].[Name] & [Project].[ID] & [Project].[Phone] LIKE '%" &Trim(request.form("searchcriteria")) & "%' ORDER BY [Debtors].[DebtorName] ASC"
rs.open sql, conn

error

Microsoft SQL Native Client error '80040e14'

The data types varchar and varchar are incompatible in the boolean AND operator.

/control/company/admin/search.asp, line 33

 

[Sheco]

Do you really mean to do a bitwise AND ?

5 & 3 = 1 because 101 & 11 = 1

 

[anon007]

It worked with access but not with SQL

 

[anon007]

I guess I am but sence it don't work I am stumpt on a work around

 

[gmmastros]

String concatenation is done with the + symbol in SQL Server, not ampersand (&).

sql = "SELECT * FROM [Project] WHERE [Project].[Name] [!]+[/!] [Project].[ID] [!]+[/!] [Project].[Phone] LIKE '%" &Trim(request.form("searchcriteria")) & "%' ORDER BY [Debtors].[DebtorName] ASC"

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon007]

Works perfect thanks gmmastros, just so I know and for others that read this why did it work in access but not in sql?

 

[Sheco]

Because [red]&[/red] is a bitwise AND in SQL Server.

 

[gmmastros]

OK. Let me explain.

Your query was concatenating 3 columns together in to one string, and then performing a like comparison. In Access, you concatenate strings with the ampersand, but in SQL Server, you use +.

So... If you had a table like this...

[tt]
Name EyeColor FavoriteColor
------ -------- -------------
George Brown Blue
Ted Blue Red
[/tt]

Effectively, you were comparing the concatenation of the data using the like search.

So...

GeorgeBrownBlue Like '%Blue%'
TedBlueRed Like '%Blue%'

Both rows would be returned.

There is a problem with this approach, by the way. Using the same sample data I show above. Suppose you search for 'er'. None of the 3 columns, for any of the rows, contain 'er'. However, since you are concatenating the data together before doing the search, and the e in blue lines up with the r in red, the Ted row would be returned.

The better way to write this query would be...

SELECT * 
FROM   [Project] 
WHERE  [Project].[Name] Like '%" & Request.Form(searchcriteria") & "%'
       Or [Project].[ID] Like '%" & Request.Form(searchcriteria") & "%'
       OR  [Project].[Phone] LIKE '%" & request.form("searchcriteria") & "%' 
ORDER BY [Debtors].[DebtorName] ASC

This query would work with Access and SQL Server without any modification.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon007]

Thanks now I see the differents.