Request date from two tables 1

[adolsun]

I’ve created 2 tables (MS Access XP) articles and authors.
Articles table has these fields (id, title, body, authorID) and Authors has (authorID and name). I linked between them by authored) I wanted to display first a list of all authors (authors.asp) then when you click on any other you can see a list of all articles belong to him/her (articles.asp).

authors.asp is working and this the code:

<%
selectnewsSQL="select * from authors"
set rsnews=ADO.execute(selectnewsSQL)
%>

<%
do while not rsnews.eof
%>

<a href="articles.asp?field=authors&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("name")%></a>

<%
rsnews.movenext
loop
%>
<%
ADO.close
Set ADO=Nothing
%>

The problem is with articles.asp page. This is the code:

<%
authorid=request.querystring("authorid")
selectnewsSQL="select * from authors, articles where authorid=" & authorid
set rsnews=ADO.execute(selectnewsSQL)
%>

<%=rsnews("title")%>
<%=rsnews("name")%>
<%rsnews("body")")%>

<%
ADO.close
Set ADO=Nothing
%>

The error message I receive is: The specified field 'authorid' could refer to more than one table listed in the FROM clause of your SQL statement.

Any help?

Adel

 

[adolsun]

Ok .. for No. 2 I mean I want to disply on upper pert the last ten articles has been added on, and then under this on the same page, a full list of all authors.

Adel

 

[DotNetGnat]

so did the No 1 work??

still did not get what you meant in No 2. You want to show all the records but want to separate the top 10 and the others...is that correct??

-DNG

 

[adolsun]

1. Still shows the result in one column

2. Example of No.2:

The latest articles:

1. Article 1 title
2. Article 2 title
3.
.
.
10



List Of authors:

1. Author 1 name
2. Author 2 name
.


Adel

 

[DotNetGnat]

please post your present code for two files...i will take a look over them and will make necessary changes...

-DNG

 

[adolsun]

All the work should be in the same page authors.asp.

1. This the code I'm trying in to display a full list of author names in more than one column.

<!--#include file=conn.inc -->
<%
selectnewsSQL="select * from authors"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%

count = 0
mymod = 0

do while not rsnews.eof

mymod = count mod 2
%>


<DIV align=center>
<table border="1" cellspacing="0" cellpadding="0">
<%
if (myMod = 0) then Response.Write "<tr>"

Response.Write "<a href='articles.asp?field=articles&authorid="&rsnews("authorid")&"'>"&rsnews("authorName")&"</a>"

if (myMod = 1) then Response.Write "</tr>"
count = count + 1

rsnews.movenext
loop
 

select case myMod
    case 0
      
      Response.Write "<td>&nbsp;</td></tr>"
    case 1
      'we dont need anything here
  end select
%>
</table>
</DIV>

<%
ADO.close
end if
Set ADO=Nothing
%>

2. To incorporate the code provided by you into same page, so the page can be:

The latest articles:

1. Article 1 title
2. Article 2 title
3.
.
.
10



List Of authors:

1. Author 1 name
2. Author 2 name
.

I wish that clear enough and thans for you patience.

Adel

 

[DotNetGnat]

were you able to solve this...if not please start a new thread describing your problem...

-DNG

 

[adolsun]

Yes ! I just solve it, the top ten is working beside the list of all authors liked to their articles.

Thanks a lot, you've done grate help and you deserve the star

I'll start new threat for formating my list in more than one column.

Adel