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

 

[DotNetGnat]

what database are you using...what is the daatatype is did you put for authorID

if it is a string...try something like this

selectnewsSQL="select * from articles, authors where authors.authorID=articles.authorID AND authors.authorID='1' "

if it is a integer then try something like this...

selectnewsSQL="select * from articles, authors where authors.authorID=articles.authorID AND authors.authorID= 1 "

how about using the join statements..

selectnewsSQL="select * from authors INNER JOIN articles ON authors.authorID=articles.authorID WHERE authors.authorID=1"

-DNG

 

[adolsun]

Good try DotNetGnat. At least we moved a step forward. Because I stored authorID as string, when I used:

selectnewsSQL="select * from articles, authors where authors.authorID=articles.authorID AND authors.authorID='1' "

I can disply authorID=1 details. But when I tried to use:

where authors.authorID=articles.authorID AND authors.authorID=" & authors.authorID

It dosen't work. I ned now to dusply all authors.




Adel

 

[DotNetGnat]

since it is a string..you should say:

where authors.authorID=articles.authorID AND authors.authorID= '"&AuthorID&"' "

that should fix your problem...

-DNG

 

[DotNetGnat]

so your whole sql should like this:

newsSQL="select * from articles, authors where authors.authorID=articles.authorID AND authors.authorID='"&AuthorID&"' "

-DNG

 

[adolsun]

Well done, you’ve done it! It’s a big help I appreciate your time and effort. Then the error was with the quotation mark because the authored type was string.
Thanks a lot.


Adel

 

[DotNetGnat]

no problem...glad to be of help...

-DNG

 

[tsdragon]

I'll give you the star you deserve for all that effort.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[DotNetGnat]

Thanks Tracy

-DNG

 

[adolsun]

Ok now. I linked between these two pages. I can display a list of all authors (authors.asp) then when I click on any author I should have a list of all articles belong to him/her on (articles.asp) the problem is when I click on any author the same list is displayed.
authors.asp code is:

<!--#include file=conn.inc -->
<%
selectnewsSQL="select * from authors"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%
do while not rsnews.eof
%>
<a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("authorName")%></a> 
<%
rsnews.movenext
loop
%>
<%
ADO.close
Set ADO=Nothing
%>

And articles.asp code which should display a list of each author is:

<%
selectnewsSQL="select * from articles"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%
do while not rsnews.eof
%>
<a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("title")%></a>
<%
rsnews.movenext
loop
%>
<%
ADO.close
Set ADO=Nothing
%>

Adel

 

[DotNetGnat]

in articles.asp, you need to make the following changes...

<%
[blue]authorid=Request.QueryString("authorid")[/blue]

selectnewsSQL="select * from articles [blue]WHERE authorid='"&authorid&"' [/blue]"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%
do while not rsnews.eof
%>
<a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("title")%></a>
<%
rsnews.movenext
loop
%>
<%
ADO.close
Set ADO=Nothing
%>

-DNG

 

[adolsun]

Yes DotNetGnat you are right, Thanks. Suppose an author has no article and is listed on the database can you wright IF statement to display a friendly message.

Adel

 

[DotNetGnat]

something like this on your articles.asp

<%
if rsnews.eof and rsnews.bof then
response.write "Sorry, no articles found for this author."
else
do while not rsnews.eof
%>
<a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("title")%></a>
<%
rsnews.movenext
loop
end if
%>

-DNG

 

[adolsun]

Great .. It's solved .. Thanks a lot.

Adel

 

[DotNetGnat]

no problem...glad to be of help...post back if you have any more questions...

-DNG

 

[adolsun]

More questions .. ok I have 2:
1. For authors or articles pages the result is displayed in one column on the table I've created. Can I display it in 2 or more columns?
2. Above the authors list I want to show only the last ten aricles had been added recently (I think SQL statement).
Thanks in advice.

Adel

 

[DotNetGnat]

1. For authors or articles pages the result is displayed in one column on the table I've created. Can I display it in 2 or more columns?

if you want to display it in two or more columns...then just have something like this...i mean just create a table

<table>
<tr>
<td><%=rs("authorId")%></td>
<td><%=rs("authorName")%></td>
</tr>
</table>

the above is just as example...let me know how you want the results to be..then we can make the changes to the original code...

2. Above the authors list I want to show only the last ten aricles had been added recently (I think SQL statement).

SELECT TOP 10 articleNames from articles WHERE authorid='"&authorid&"' Order By articleDate Desc"

the above sql is also an example...just try it out on the same lines...

-DNG

 

[adolsun]

1. I want Authors name on two columns, suppose I've a long list of Authors it's good idea to put them on more than on column .. somthing like this:
1. Kim 20. John
2. Mick 21. Harry
3. Garry 22. Poul
. .
. .
. .
Note: this list should be above authors list in the same page authors.asp (the code is down).

2. Where to put SQL statement .. This is the code:

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

<table>
<tr>
<td><%=rs("authorId")%></td>
<td><a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("authorName")%></a></td>
</tr>
</table>
 
<%
rsnews.movenext
loop
%>
<%
ADO.close
Set ADO=Nothing
%>

Adel

 

[DotNetGnat]

ok...i dont know how long this thread is going to be...but i guess you are learning some new stuff...here we go

1.

<!--#include file=conn.inc -->
<%
selectnewsSQL="select * from authors"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%
[red][b]
count = 0
mymod = 0
[/b][/red]

do while not rsnews.eof

[red][b]mymod = count mod 2[/b][/red]
%>


[red][b]<table border="1" cellspacing="0" cellpadding="0">
<%
if (myMod = 0) then Response.Write "<tr>"
%>[/b][/red]
<a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("authorName")%></a> 
[red][b]
<%
if (myMod = 1) then Response.Write "</tr>"
count = count + 1
[/b][/red]
rsnews.movenext
loop
 
[red][b]
select case myMod
    case 0
      
      Response.Write "<td>&nbsp;</td></tr>"
    case 1
      'we dont need anything here
  end select
%>
</table>

[/b][/red]
<%
ADO.close
Set ADO=Nothing
%>

2.

<!--#include file=conn.inc -->
<%
selectnewsSQL="select [red][b]Top 10[/b][/red] * from authors"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%
do while not rsnews.eof
%>

<table>
<tr>
<td><%=rs("authorId")%></td>
<td><a href="articles.asp?field=articles&authorid=<%=rsnews("authorid")%>"><%response.write rsnews("authorName")%></a></td>
</tr>
</table>
 
<%
rsnews.movenext
loop
%>
<%
ADO.close
Set ADO=Nothing
%>

-DNG

 

[adolsun]

Sorry for being too long, but I really learned a lot from this.
1. This code display the same result as before, means one column, although I tried to change the count and mod values.
2. Yes it shows the latest 10 had been added, but without full authors list?

Adel

 

[DotNetGnat]

try this again for the first part..

<!--#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
%>


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


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

did not understand what you meant in 2. please explain...

-DNG