Hi all, Im new in ASP. Im having problem to display record from two different tables and without duplication from one of the field.

Example :-I have three tables in the same database.

MovieRecording(Table)
-movieid
-movietitle
-image

ActorRecording(Table)
-actorid
-actorname

MovieActor(Table)
-actorid
-movieid

As we know many actors link to one movie. I want to display the movie's image once and then follow by actor name.

Coding that i create:-

      <%
    dim sql, cn, rs, strconn

    strconn="DRIVER={Microsoft Access Driver (*.mdb)};"
    strconn=strconn & "DBQ=" & server.mappath("DVDDB.mdb")

    set cn = server.createobject("ADODB.Connection")
    cn.ConnectionString = strconn
    cn.Open strconn

    set rs=server.CreateObject("ADODB.Recordset")
    set ars=server.CreateObject("ADODB.Recordset")

    sql="select image,movieid from MovieRecording"

    actorsql="select ActorRecording.actorname, MovieActor.movieid from(ActorRecording INNER JOIN"
    actorsql=actorsql&" MovieActor ON ActorRecording.actorid=MovieActor.actorid)INNER JOIN"
    actorsql=actorsql&" MovieRecording ON MovieActor.movieid = MovieRecording.movieid"
        'response.write sql
    set rs=cn.Execute(sql)
    set ars=cn.Execute(actorsql)

    dim rscounter, arscounter

    rscounter=1
    arscounter=1

    while rs.eof=false
    response.write "<br>"
    'response.write "<img src ='Photo\Movie\" & rs("iamge") & "' width='80'>"
        while ars.eof=false
        if(rs("movieid") == ars("movieid")) then
        response.write "<br>"
        response.write ars("actorname")
        end if
        ars.movenext
        arscounter = arscounter + 1
        wend
    response.write "<br>"
    rs.movenext
    rscounter = rscounter + 1
    wend
    %>

When i run :-

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/online/dvd_list.asp, line 75, column 18
if(rs("movieid") == ars("movieid")) then


Im not sure im using the correct way to write my coding or not. I really dont have any idea.

Thx
Selina

the error you are getting is due to the == in the if statement.

"==" is C/Java style coding, in VB/VBScript you just need = when doing comparisons.

sorry, 1 more thing, you don't need the brackets either in the if. same reason.

If rs("movieid") = ars("movieid") Then
    ...
    ...
End If

Thank for your reply :)

Problem solved by adding one more line ars.movefirst() before while ars.eof = false

:D