Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: how to make column of table 1 = to column of table 2?

    I want MS Access 2000 database column of table 1 = column of table 2.

    This is what I was doing:
    Code:
    <%
    'Option Explicit
    'Response.End 
    Response.Buffer=false
    
    Dim letter
    
    'SQL = "SELECT bible.book_spoke, biblewheel_url.letter FROM bible, biblewheel_url WHERE bible.book_spoke=biblewheel_url.book_spoke" 
    
        SQL = "SELECT * FROM biblewheel_url WHERE "
        strConn = GetConnectionString()
    
    SQL = SQL & "letter(biblewheel_url.book_spoke) = rs(bible.book_spoke)"
    
        Set letter = Server.CreateObject("ADODB.Recordset")
        letter.CursorLocation = adUseClient
    
        letter.Open SQL, strConn, adOpenForwardOnly, adLockReadOnly
    %>
    <%=letter("url")%>
    <%
        letter.Close
        set letter = Nothing
    %>
    and to pull url address of table 2 to which record it is =to

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    so you want to do an update as well as your selection or what?

  3. #3
    Join Date
    Jul 2004
    Posts
    494

    No update

    If updating changing data, no. I just want table 1 column to match in number (like '015' of table 1 column with '015' with table 2 column). Because table 2 has urls.

    I tried a few things like change function to call function or sub and it didn't work. The problem was the recognition of "letter":

    Code:
    <%
    'Option Explicit
    'Response.End 
    Response.Buffer=false
    Dim letter
    
    call ShowResults()
    ShowResults(letter)
    'SQL = "SELECT bible.book_spoke, biblewheel_url.letter FROM bible, biblewheel_url WHERE bible.book_spoke=biblewheel_url.book_spoke" 
    
        SQL = "SELECT * FROM biblewheel_url WHERE "
        strConn = GetConnectionString()
    
    'SQL = SQL & "letter(biblewheel_url.book_spoke) = rs(bible.book_spoke)"
    SQL = SQL & "letter(biblewheel_url.book_spoke) = '" & spoke & "'"
        
    
    Set letter = Server.CreateObject("ADODB.Recordset")
        letter.CursorLocation = adUseClient
    
        letter.Open SQL, strConn, adOpenForwardOnly, adLockReadOnly
    %>
    <%=letter("url")%>
    <%
    End Function
        letter.Close
        set letter = Nothing
    %>
    This is actually an include asp file. This is place in <A></A> to bring the proper URL.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, sorry for my not understanding but what is the problem with this line that you have commented out...
    Code:
    'SQL = "SELECT bible.book_spoke, biblewheel_url.letter FROM bible, biblewheel_url WHERE bible.book_spoke=biblewheel_url.book_spoke"
    which would appear to do the kind of thing you are talking about (personally I'd use a join but the choice is yours)

  5. #5
    Join Date
    Jul 2004
    Posts
    494

    well

    I think I have found the answer. But I think this is an obstacle:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] Undefined function 'letter' in expression.

    /wheelofgod/letters.asp, line 18

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    errr,....great.... you should know better by no then to post just an error message without any code for us to look at to help you.....

  7. #7
    Join Date
    Jul 2004
    Posts
    494
    Here it is.

    Microsoft VBScript compilation error '800a0400'

    Expected statement

    /wheelofgod/letters.asp, line 25

    End Function
    ^
    letter.asp:
    Code:
    <%
    'Option Explicit
    'Response.End 
    Response.Buffer=false
    Dim letter
    
    call ShowResults()
    ShowResults(letter)
    'SQL = "SELECT bible.book_spoke, biblewheel_url.letter FROM bible, biblewheel_url WHERE bible.book_spoke=biblewheel_url.book_spoke" 
    
        SQL = "SELECT * FROM biblewheel_url WHERE "
        strConn = GetConnectionString()
    
    'SQL = SQL & "letter(biblewheel_url.book_spoke) = rs(bible.book_spoke)"
    SQL = SQL & "letter(biblewheel_url.book_spoke) = '" & spoke & "'"
        
    
    Set letter = Server.CreateObject("ADODB.Recordset")
        letter.CursorLocation = adUseClient
    
        letter.Open SQL, strConn, adOpenForwardOnly, adLockReadOnly
    %>
    <%=letter("url")%>
    <%
    End Function
        letter.Close
        set letter = Nothing
    %>

    Used in kjvresp.asp:
    Code:
    <A href="<!--#include file="letters.asp"-->">
    <IMG SRC="hebrew/<%=RS("book_spoke")%>.jpg" ALT="Book&nbsp;Spoke&nbsp;&nbsp;<%=RS("book_spoke")%>">
    </A>

  8. #8
    Join Date
    Jul 2004
    Posts
    494

    Some things solved...

    Ok the code:
    Code:
       letterSQL = "SELECT bible.id, "
        letterSQL = letterSQL & "biblewheel_url.url "
        letterSQL = letterSQL & "FROM biblewheel_url "
        letterSQL = letterSQL & "INNER JOIN bible "
        letterSQL = letterSQL & "ON biblewheel_url.book_spoke = "
        letterSQL = letterSQL & " bible.book_spoke "
    dealt the "letter" problem. I deleted "function" as well.

    It's it's not giving the proper record. Let's say I'm looking for book_spoke (from the 1st table columns) 015, I expect it to link with book_spoke (from the 2nd table columns) and pick up the proper url from the url column from the 2nd table columns.

    I get the first link in the url column not 015.

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    aren't you missing a where clause??? something like...
    Code:
    letterSQL = "SELECT bible.id, "
        letterSQL = letterSQL & "biblewheel_url.url "
        letterSQL = letterSQL & "FROM biblewheel_url "
        letterSQL = letterSQL & "INNER JOIN bible "
        letterSQL = letterSQL & "ON biblewheel_url.book_spoke = "
        letterSQL = letterSQL & " bible.book_spoke "
        letterSQL = letterSQL & " where bible.book_spoke='" & request("book_spoke") & "'"

  10. #10
    Join Date
    Jul 2004
    Posts
    494

    got it from a book

    I got it from a book which showed how to link 2 tables. Even if there was a need for a "where" I wouldn't know how to continue. I thought this was descriptive enough.

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    like I said I think you need a where clause like this....
    Code:
        letterSQL = "SELECT bible.id, "
        letterSQL = letterSQL & "biblewheel_url.url "
        letterSQL = letterSQL & "FROM biblewheel_url "
        letterSQL = letterSQL & "INNER JOIN bible "
        letterSQL = letterSQL & "ON biblewheel_url.book_spoke = "
        letterSQL = letterSQL & " bible.book_spoke "
        letterSQL = letterSQL & " where bible.book_spoke='" & request("book_spoke") & "'"
    This will select all the records where the url matches in both tables and the book spoke is set to whatever value you want (eg. 015)

Posting Permissions

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