Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 12th, 2006, 02:12 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old November 12th, 2006, 10:35 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
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.

Reply With Quote
  #3 (permalink)  
Old November 12th, 2006, 10:37 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
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

Reply With Quote
  #4 (permalink)  
Old November 12th, 2006, 12:16 PM
Registered User
 
Join Date: Nov 2006
Location: , , .
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.