Search for any column?

[illufox]

I would like to create a search function where people can type anything into
the search field and it will bring up data that matches the search parameter.
So far I'm only able to have them search for parameters in a specific column
with the code below. I don't see an option to have the search look through all
columns instead of just one particular. I've tried to add all columns to the
WHERE statement but that caused an error.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_EventsDB_STRING
Recordset1.Source = "SELECT IdMeeting, Date_S, EventName, Location,
ProjectLead, Status, TypeOfMeeting, MarketingLead, Industry FROM Meeting WHERE
Industry LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' ORDER BY
Date_S ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

[Tom Muck]

"illufox" <webforumsuser@macromedia.com> wrote in message
news:d1fbf4$n6e$1@forums.macromedia.com...

> I would like to create a search function where people can type anything

into

> the search field and it will bring up data that matches the search

parameter.

> So far I'm only able to have them search for parameters in a specific

column

> with the code below. I don't see an option to have the search look

through all

> columns instead of just one particular. I've tried to add all columns to

the

> WHERE statement but that caused an error.

You have to specify each column you want to search (in pseudo-sql):

SELECT * FROM mytable
WHERE field1 like '%search%'
AND field2 LIKE '%search%'
AND field3 LIKE '%search%' etc

So basically you would write the SQL exactly as you have done, but include a
filter for each field that you want to search.

If you have my book Dreamweaver MX 2004: The Complete Reference, there is
code that shows how to do it automatically in every server language. I also
have an extension that expands on this code available for a small fee from
[url]http://www.tom-muck.com/extensions/help/DynamicSearchPHP/[/url]


--
-------------------------------------------
Tom Muck
co-author Dreamweaver MX 2004: The Complete Reference
[url]http://www.tom-muck.com/[/url]

Extending Knowledge, Daily
[url]http://www.CommunityMX.com/[/url]

[illufox]

Ok, I've done that but now I get no results at all. Shouldn't it be 'OR' insted
of 'AND'? Here is the updated code:

Recordset1.Source = "SELECT IdMeeting, Date_S, EventName, Location,
ProjectLead, Status, TypeOfMeeting, MarketingLead, Industry FROM Meeting WHERE
Industry LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
EventName LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
Location LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
ProjectLead LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
Status LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
TypeOfMeeting LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%'ORDER
BY Date_S ASC"

[Julian Roberts]

Another method might be to concatenate the fields to search. eg

select * from t where f1 & f2 & f3 like '%myParam%'

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[bthouin]

Yes of course it should be OR and not AND !

But Julian's answer is the brilliant one ! Had no idea one could
concatenate fields !

Bernard

illufox wrote:

> Ok, I've done that but now I get no results at all. Shouldn't it be 'OR' insted
> of 'AND'? Here is the updated code:
>
> Recordset1.Source = "SELECT IdMeeting, Date_S, EventName, Location,
> ProjectLead, Status, TypeOfMeeting, MarketingLead, Industry FROM Meeting WHERE
> Industry LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
> EventName LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
> Location LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
> ProjectLead LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
> Status LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND
> TypeOfMeeting LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%'ORDER
> BY Date_S ASC"
>