I have an ASP page that performs a Search in a MS Access Database. The Search originally only looked at one table (links) in my database. I added another table with identical columns but different data called (bandlinks) This script was already made and I altered it to work for several tables, but when somebody searches
it only looks at the "links" table. I need the query to search BOTH tables and show ALL the info in one listing. In the script i am altering, there are a lot of extra codes that maybe you guys know but the confusing part is the 4 or 5 times that they repeat the SELECT query. Thats not my concern yet but here is the LONG page of code... I'm REALLY sorry for posting such a long mess but I don't want anything left out of the answer!


***********************************************
<%
'Link List
Dim selStat

selStat = "links.linkid, links.title, links.url, links.description, links.hits, links.datein, "_
& "links.catid, links.popular, links.votes, links.rating, links.posts, links.threads, links.linktype, links.picture"

Dim currentpage
Dim pagecount
Dim linkcount
Dim SortSQL
Dim lastcat
Dim catlist
Dim errorstatus
Dim SQLquery
Dim chcolor
Dim lastx
Dim popsort
Dim retCats

retCats = LV_retcats

if dbtype = 1 then
popsort = "ORDER BY popular ASC"
else
popsort = "ORDER BY popular DESC"
end if

' This is the Highlight Color for Categories:
chcolor = "#FFFF00"
'Edit ^^^^^^^ To change the color

Set catlist = Server.CreateObject("ADODB.RecordSet")

linkcount = 0
lastcat = 0
errorstatus = 0

If Request.QueryString("page") = "" Then
currentpage = 1
else
currentpage = Cint(Request.QueryString("page"))
end if

'-----------------------------------
SortSQL = "ORDER BY "

SELECT Case SortOrder
Case 0
SortSQL = popsort
Case 1
SortSQL = SortSQL & " title ASC"
Case 2
SortSQL = SortSQL & " title DESC"
Case 3
SortSQL = SortSQL & " hits ASC"
Case 4
SortSQL = SortSQL & " hits DESC"
Case 5
SortSQL = SortSQL & " votes ASC"
Case 6
SortSQL = SortSQL & " votes DESC"
Case 7
SortSQL = SortSQL & " posts ASC"
Case 8
SortSQL = SortSQL & " posts DESC"
Case 9
SortSQL = SortSQL & " datein DESC"
Case 10
SortSQL = SortSQL & " datein ASC"
Case 11
SortSQL = SortSQL & " rating DESC"
Case 12
SortSQL = SortSQL & " rating ASC"
End SELECT

SortSQL = SortSQL & ", catid ASC"

'-----------------------------------

if request.querystring("func") = "search" then

Dim strText
Dim wCount
Dim lastpos
Dim dist
Dim words(100)

lastpos = 1
dist = 1
wCount = 0
strText = myquery

SQLquery = "(" &parsethis(strText ,words ,wCount, 2) & ")"
SQLquery = Replace(SQLquery, "<FIELD1>", "title")
SQLquery = Replace(SQLquery, "<FIELD2>", "description")

If NOT mytype = "" then
SQLquery = SQLquery & " AND catid = " & tree
end if
if len(trim(strText)) = 0 then
errorstatus = 2
else
SQLstr = "SELECT " & selStat & " FROM links WHERE " & SQLquery & " AND active = " & myTrue & " " & SortSQL & ";"
end if
end if
if Request.QueryString("func") = "new" then
Dim newdate
newdate = (date - newcutoff)
if mydbtype = 1 then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " & myTrue & " AND datein > #" & newdate & "# ORDER BY datein DESC, catid ASC;"
end if
if mydbtype = 2 then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " & myTrue & " AND datein > '" & newdate & "' ORDER BY datein DESC, catid ASC;"
end if
end if
if Request.QueryString("func") = "hot" then
if whsort = 1 then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " & myTrue _
& " AND hits >= " & whhits & " AND rating >= " & whrating & " ORDER BY hits DESC, rating DESC;"
else
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " & myTrue _
& " AND hits >= " & whhits & " AND rating >= " & whrating & " ORDER BY rating DESC, hits DESC;"
end if
end if
if Request.QueryString("func") = "referrers" then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " & myTrue & " AND ldayin = " & day(date()) & " ORDER BY dhitsin DESC;"
end if

'&*( Return categories if applicable )&*()&*(&*()&*(&*()&*()&*()&*()&*(&*()&*()&*&*()&* (&*()&*()

if mytype = "" AND currentpage = 1 AND Request.QueryString("func") = "search" AND errorstatus = 0 then
Set catsRec = Server.CreateObject("ADODB.RecordSet")
catsRec.Open "SELECT TOP " & retCats & " catid, subname FROM cats WHERE " & Replace(parsethis(strText ,words ,wCount, 1), "<FIELD1>", "name") & " AND display LIKE '%default%';", objConn, 3, 3, 1
Response.Write "<font face=""" & deffont & """ size=""" & deffsize & """>" & sercats & "</font><BR>"

If catsRec.EOF then
Response.Write "<font face=""" & deffont & """ size=""" & deffsize & """>" & sercatsnone & "</font>"
end if
Dim myCat
While NOT catsRec.EOF
myCat = highlight(catsRec("subname"), words, wCount, chcolor)
Response.Write "<font face=""" & deffont & """ size=""" & deffsize & """>" & Replace(catsertemp,"<CAT>", "<a href=""catredir.asp?cat=" & catsRec("catid") & """>" & myCat & "</a>") & "</font><BR>"
catsRec.MoveNext
Wend
Set catsRec = Nothing
Response.Write "<BR><BR>"
end if

' &*(&*()&*()&*()&*()&*(&*()&*()&*&*()&*(&*()&*()&*( &*()&*()&*()&*()&*(&*()&*()&*&*()&*(&*()&*()
if errorstatus = 0 then
linkRec.PageSize = pagesize
linkRec.CacheSize = pagesize
linkRec.Open SQLstr, ObjConn, adOpenStatic, adLockReadOnly, adCmdText
pagecount = linkRec.pagecount
If currentpage > pagecount then
currentpage = pagecount
end if
If currentpage < 1 then
currentpage = 1
end if
else
pagecount = 0
end if
if errorstatus = 2 then
Response.Write "<font face=""" & deffont & """ size=""" & deffsize & """>" & noquery & "</font>"
elseif pagecount = 0 then
Response.Write "<font face=""" & deffont & """ size=""" & deffsize & """>" & nolinks & "</font>"
else
%><!-- #INCLUDE FILE="paging.asp" --><%
linkRec.AbsolutePage = currentpage
Response.Write "<p><font face=""" & deffont & """ size=""" & deffsize & """>" & Replace(Replace(Replace(linksfound ,"<LINKSFOUND>", linkRec.recordcount), "<PGA>", currentpage ), "<PGB>", pagecount ) & "</font></p>"
While linkcount < pagesize AND Not linkRec.EOF
if lastcat <> linkRec(Links_catid) then
Set catlist = Server.CreateObject("ADODB.RecordSet")
catlist.Open "SELECT catid, subname FROM cats WHERE catid = " & linkRec(Links_catid) & ";", objConn, 3, 3, 1
lastcat = linkRec(Links_catid)
Response.Write "<font face=""" & deffont & """ size=""" & deffsize & """>" & Replace(Replace(catTemp, "<CATNAME>", catlist(1)), "<CATLINK>", "catredir.asp?cat=" & catlist(0)) & "</font>"
end if
%><!-- #INCLUDE FILE="linktemp.inc" --><%
linkRec.MoveNext
linkcount = linkcount + 1
Wend
end if
if errorstatus = 0 then
linkRec.Close
end if
Set catlist = Nothing
Response.Write pgHTML
%>

Thank you for any help with this.. :-)

Why are you doing this twice:

Set catlist = Server.CreateObject("ADODB.RecordSet")

The easiest way to debug this is to response.write "the sql statement" - copy and paste that in access to run as a query and see if it runs.

Your sql statements only select from links. You just want all records from both databases ?

Actually this is code that was already written from a freeware company. I'm not sure why Set catlist is done twice but it works great right now. It only works great on the website when you're doing a SEARCH on just one table. The current table its searching is called Links. Another forum told me to put "links, bandlinks" (bandlinks being the second table i want searched)... but when i just put a comma and the next table I get this error.....

No Categories Found..

Microsoft JET Database Engine error '80004005'

The specified field 'title' could refer to more than one table listed in the FROM clause of your SQL statement.

/qlinklist.asp, line 162

*this error is AFTER I add the table (bandlinks) with the comma just after the table that already exists (links)

To query multiple tables that have the same structure why not use a union query??

ok.. i'll search on how to perform UNION queries... i'm not educated at all in this field. I'm just an artist learning to get by till I can hire somebody. Thank you so much for the advice!

okie, a quick example of the sql....

effectively you could build 1 sql statement in your code, then do something like...

make some sort of sense??