[gilgalbiblewhee]

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

This is what I was doing:

<%
'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

[rokslide]

so you want to do an update as well as your selection or what?

[gilgalbiblewhee]

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":

<%
'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.

[rokslide]

okie, sorry for my not understanding but what is the problem with this line that you have commented out...

'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)

[gilgalbiblewhee]

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

[rokslide]

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

[gilgalbiblewhee]

Here it is.

Microsoft VBScript compilation error '800a0400'

Expected statement

/wheelofgod/letters.asp, line 25

End Function
^

letter.asp:

<%
'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:

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

[gilgalbiblewhee]

Ok the 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.

[rokslide]

aren't you missing a where clause??? something like...

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") & "'"

[gilgalbiblewhee]

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.

[rokslide]

like I said I think you need a where clause like this....

    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)