If I am not wrong, you are trying to search for the KEYWORD (entered in the textbox) against all CHECKED columns among those 6 checkboxes, from all the tables CHECKED among those 66 table Checkboxes.
This is actually taking all the columns that are checked and compare for search keyword in it for every table.
Assume you have CHECKED the BOOK, SPOKE and TEXT from the COLUMNS and CHECKED the tables as Genesis, Isaiah, Romans... then you should contruct an sql statement that would check it this way.
where BOOK like '%' + strSearchKeyWord + '%' OR
SPOKE like '%' + strSearchKeyWord + '%' OR
TEXT like '%' + strSearchKeyWord + '%'
Now replace GENESIS with Isaiah in the next query and then with ROMANS in the next query and finally you should UNION ALL and that should contruct your recordset. Which is round about way of acheiving.
And Surendran's solution would not fit this. coz' as per his code, the sql statement would look like
strQry="select * from " & strFields & ""
select * from Genesis, Isaiah, Romans
this would not solve the purpose, instead result in huge result set of 9 columns(imaging if all 66 table are checked, it would result in 66 * 3 = 198 columns per row
) because, all the three tables have the same column names(book, spoke, text) in it that woudl result in repetion and you cannot differentiate which has come from which table. And you are not using object referencing there, instead using a *. Also WHERE clause is missing, so what would you be searching for atlast?
Same as the case of RajaniKrisna's solution, only difference is that he has suggested to use that variable in the COLUMNs list, which would not solve the purpose.
I feel that there is a flaw in designing this database structure. When all those 66 tables are having same number of columns all with same names too(I believe so, as per the UI design I could see there), which are similar to all 66 tables, having the data stored in different tables is a real blunder, that makes things complex for you.
You could have designed a single table to hold all that.
(name it the rightway, that suits the context)
So there would be 66 different values for BOOK column each having a reference to different BOOKID, similary all other columns would have its own value pertaining to each BOOKID.
So based on the selection made in the asp page, you could easily contruct you SELECT query that would look like...
strSearchKeyWord = Request.Form("txtSearch")
strColumns would hold "Book, Spoke, Text" separated with comma,
provided that all those 6 checkboxes are given the same name.
strColumns = Request.Form(chkColumns)
Similarly strBooks would hold BOOKID of Genesis, Isaiah, Romans as per selection made,
where the checkboxes are already set with value as BOOKID.
Assume that the value of strBooks finally would have "1,2,3"
strBooks = Request.Form(chkBooks)
strSql = "Select <YourColumnName(s)> from SearchStringMatchTable Where "
arrSearchColumns = split(strColumns,",")
For i = 0 to Ubound(arrSearchColumns)
strSql = strSql & arrSearchColumns(i) & " like '%" & strSearchKeyWord & "%'"
If i < Ubound(arrSearchColumns) then
strSql = strSql & " OR "
strSql = strSql & " AND "
strSql = strSql & "BookId in (" & strBooks & ")"
Assuming your Seach Keyword is "King
" then the output of strSql string would look like
Select <YourColumnName(s)> from SearchStringMatchTable
like '%King%' OR Spoke
like '%King%' OR Text
AND BookId in (1,2,3)
Does this make sense?
Hope this helps.
- Vijay G
Strive for Perfection