search function

[MONFU]

Dear All

I am trying to do a search function for an entire site based on an Access Database. I have already created a search functionality from particular tables, like for example from the FAQ table:-

if category = "FAQ" then
sqlSearch="SELECT * from FAQ "
sqlSearch = sqlSearch & " WHERE faqQuest LIKE " & "'" & "%" & KillQuotes(searchField) & "%" & "'"
sqlSearch = sqlSearch & " OR faqAns LIKE " & "'" & "%" & KillQuotes(searchField) & "%" & "'"
sqlSearch = sqlSearch & " ORDER BY faqQuest ASC"
'response.Write(sqlSearch)
rsSearch.Open (sqlSearch)
end if

However now I wish to do an entire site search from all the tables in the database.

Can you help me out on how to do it please.

Thanks for your help and time

 

[ralphtrent]

That can be one lengthly search if you do all tables from the database. What I would do is limit the tables and just search them. I have a search function on one of my sites that only looks at 2 tables. A description table and a details table. The desc. table just hold the title to an article and the details obviously holds the details. All my other tables in my database are irrelivent to the search criteria. What is it that you are allowing your visitors to search for?

What you can do is create another table or an array in your asp code, that holds the names of the tables that you created. If you use the system tables that Access provides it can be nasty because trying to figure out what is an Access provider table and what is a User create table is a nightmare. So you can create a array/table. If you do a table, throw the results into an array.

Either way, then you can do this.
for i = 0 to ubound(tablearray)
search(tablearray(i)
next

Sub search(tableName)
sql = "select * from " & tablename
.
.
.
end Sub

Hope this make sense. If not, let me know.

Ralph

 

[MONFU]

hmmmm very interesting.

Well the tables I am allowing the user to seach in are basically 9 tables. but I did not understand the code perfectly