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]

you need to change your sql statement on page 2 to something like this...

selectnewsSQL="select * from authors, articles where
authors.authored=articles.authored AND authorid=" & authorid

-DNG

 

[DotNetGnat]

what i mean to say is that you need to join those two tables...and the result set may contain a many rows...so
do something like...

if rs.eof and rs.bof then
response.write "sorry. no records found"
else
until rs.eof
<%=rsnews("title")%>
<%=rsnews("name")%>
<%rsnews("body")")%>
rs.movenext
loop
end if


you cana also format your results and put in the table format...but first lets get off of that error you are receiving

-DNG

 

[tsdragon]

DotNetGnat: you'll still get the same error with the sql statement you provided. You forgot to qualify authorid in the last part of the where clause:

selectnewsSQL="select * from authors, articles where
authors.authored=articles.authored AND [red]authors.[/red]authorid=" & authorid


Tracy Dryden

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

 

[DotNetGnat]

oh ok...the error you are getting is because it is complaining the authorid is found in both the tables...

so that refer the table...

authors.authorid=" & authorid

-DNG

 

[DotNetGnat]

i just realized that after posting...thanks for pointing out Tracy...

-DNG

 

[adolsun]

I've copyed and paste it on my page with a correction. DotNetGnat typed authorid: authored. So my code as follows:

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

But even though I received this error message on that line:
Data type mismatch in criteria expression.



Adel

 

[DotNetGnat]

could you please write out your sql query using Response.Write...

i think an empty value is getting passed for authorID...

also as a test try hardcoding the value to see if it works...

something like this:


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

-DNG

 

[DotNetGnat]

in your original post you stated...

I linked between them by [red]authored[/red])

thats why i used authored even though i thought it was authorid

-DNG

 

[adolsun]

This's my full code:

<%
Authorid=request.querystring("Authorid")

selectnewsSQL="select * from authors, articles where authors.authorid=articles.authorid AND authors.authorid=" & authorid

set rsnews=ADO.execute(selectnewsSQL)
%>

<%=rsnews("title")%>
<%=rsnews("Name")%>
<%=rsnews("body")%>
<%
ADO.close
Set ADO=Nothing
%>

Adel

 

[DotNetGnat]

<%
Authorid=request.querystring("Authorid")

[blue]Response.Write Authorid[/blue]

selectnewsSQL="select * from authors, articles where authors.authorid=articles.authorid AND authors.authorid=" & [red][b]Authorid[/b][/red]

[blue]Response.Write selectnewsSQL[/blue]

set rsnews=ADO.execute(selectnewsSQL)
%>

<%=rsnews("title")%>
<%=rsnews("Name")%>
<%=rsnews("body")%>
<%
ADO.close
Set ADO=Nothing
%>

-DNG

 

[adolsun]

Sorry .. but I'm still having an error:
Object required: ''

I copied and pasted what you send.

Adel

 

[DotNetGnat]

do you have this:

SET rsnews= Server.CreateObject("ADODB.RECORDSET")


-DNG

 

[adolsun]

Do you mean the connection file? Yes off course

It's as foloows:

<%
Dim ConnectionString
Dim ADO
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("../db/mydb.mdb")
Set ADO=Server.CreateObject("ADODB.Connection")
ADO.ConnectionString =ConnectionString
ADO.Open
%>

And linked to all pages as SSI.

Adel

 

[DotNetGnat]

no no...i mean in your code you are using rsnews...but you need to declare this object first...

<%
[b][blue]SET rsnews= Server.CreateObject("ADODB.RECORDSET")[/blue][/b]

Authorid=request.querystring("Authorid")

Response.Write Authorid

selectnewsSQL="select * from authors, articles where authors.authorid=articles.authorid AND authors.authorid=" & Authorid

Response.Write selectnewsSQL

set rsnews=ADO.execute(selectnewsSQL)
%>

<%=rsnews("title")%>
<%=rsnews("Name")%>
<%=rsnews("body")%>
<%
ADO.close
Set ADO=Nothing
%>

-DNG

 

[DotNetGnat]

this line:

set rsnews=ADO.execute(selectnewsSQL)

please rename ADO to something else...like...myconn...

-DNG

 

[adolsun]

I gave it some trying, so I just request data from one table without WHERE like this:

<!--#include file=conn.asp -->
<%
SET rsnews= Server.CreateObject("ADODB.RECORDSET")

Authorid=request.querystring("Authorid")

selectnewsSQL="select * from authors"

set rsnews=ADO.execute(selectnewsSQL)
%>

<%=rsnews("name")%>
<%
ADO.close
Set ADO=Nothing
%>

That's working fine. But when I add second table it desen't work.

I think the problem is how to request data from two linked tables by authorID field.

Adel

 

[DotNetGnat]

ok let say you have something like this:

authors

authorId | authorName

1 | Jon
2 | Kim

authorId | articletitle | articleDate
1 | blah | 06/19/2005
1 | blahblah | 06/20/2005
2 | sample | 06/25/2005

now
selectnewsSQL="select author.authorId, author.authorName, articles.articletitle, articles.articleDate from authors, articles where authors.authorID=articles.authorID AND authors.authorID=1 should work fine...and i am sure who know how the results will look like...

-DNG

 

[DotNetGnat]

i meant to say "and i am sure YOU know how the results will look like..."

-DNG

 

[adolsun]

Oh God .. still an error. This code can disply data from two tables. Hence I've deleted WHERE statment and created my tables as what you said DotNetGnat, so my code is as follows:

<!--#include file=..\includes\header.inc -->
<%
SET rsnews= Server.CreateObject("ADODB.RECORDSET")

Authorid=request.querystring("Authorid")

selectnewsSQL="select * from articles, authors"
set rsnews=ADO.execute(selectnewsSQL)
%>
<%=rsnews("authorName")%>
<br>
<%=rsnews("articleID")%>
<br>
<%=rsnews("articletitle")%>
<br>
<%=rsnews("body")%><%
ADO.close
Set ADO=Nothing
%>

When I add WHERE statment:

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

I recieve this messege:
Data type mismatch in criteria expression.

Thanks DotNetGnat for you patience and support.

Adel