Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: problems with a search query from 2 tables

    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.. :-)

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    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)



    Originally posted by rnealejr
    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 ?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    To query multiple tables that have the same structure why not use a union query??

  5. #5
    Join Date
    Jan 2004
    Posts
    3
    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!

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, a quick example of the sql....

    Code:
    select field1, field2, field3 from links
    union
    select field1, field2, field3 from bandlinks
    effectively you could build 1 sql statement in your code, then do something like...

    Code:
    basesql = "select field1, field2, field3 from "
    mysql = ""
    for each table
      if mysql <> "" then mysql = mysql & vbCRLF & "union" & vbCRLF 
      mysql = mysql + basesql + tablename
    next
    make some sort of sense??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •