Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 12th, 2006, 02:12 AM
Registered User
 
Join Date: Nov 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Display Record Frm 2Diff Tables&Without Duplicate

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
 
Old November 12th, 2006, 10:35 AM
Registered User
 
Join Date: Nov 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old November 12th, 2006, 10:37 AM
Registered User
 
Join Date: Nov 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

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

 
Old November 12th, 2006, 12:16 PM
Registered User
 
Join Date: Nov 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank for your reply :)

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

:D





Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop duplicate record msbsam SQL Server 2000 1 March 10th, 2008 11:24 AM
add & Remove frm Listbox hotshot_21 ASP.NET 1.0 and 1.1 Basics 1 June 21st, 2007 05:26 PM
Open & save 1 frm witout closing the prvs frm anukagni Access 3 January 10th, 2006 02:42 AM
get latest record frm transaction tbl jyopallavi SQL Server 2000 1 June 9th, 2005 08:11 AM
how duplicate a recordset of 2 tables k0023382 Access 3 October 1st, 2004 01:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.