Repeating Records

Nicky_uk · January 27th, 2005, 02:48 PM

Hi there

I have a products page which should be retuning one selected result with the related records. To explain clearly, if you click on a particular DVD, then the info regarding that DVD will show in a page. I am trying to show what actors are in the DVD, however, the whole result appears as many times as there are cast members. So instead of showing the DVD once along with all the cast members, the record repeats itself. For example, there are 7 cast members in Volcano, so Volcano is repeated 7 times along with a cast member each?

The code I have used is:

Code:

myRS.Open "SELECT * From AllMovies INNER JOIN Cast ON AllMovies.TitleID = Cast.CastID WHERE TitleID =" & strTitleID &"" , myConn

Although the information being bought back from the database is correct, I need to prevent the repeats from the AllMovies table.

I hope I've explained it clear enough.

Thanks for any help.

om_prakash · January 27th, 2005, 02:58 PM

Hi,

while displaying the content to the user, you can display "Volcano" only once by adding a flag/counter in the loop which will check if the data is displayed once, then not to display again.

It appears from ur query that for one "strTitleID" all the data is displayed. then other option could be use the following query:

myRS.Open "SELECT * From Cast where CastID =" & strTitleID &"" , myConn

and get all cast details. And you can have separate query for displaying DVD or can get from the previous page in hidden fields.

Om Prakash

shahchi1 · January 27th, 2005, 03:02 PM

I agree with Om Prakash!
If you are doing inner join, then quey should be like this
SELECT * FROM AllMovies INNER JOIN Cast ON AllMovies.TitleID = Cast.TitleID
WHERE AllMovies.TitleID =" & strTitleID &""

Nicky_uk · January 27th, 2005, 03:14 PM

Shahchi1. I tried that way but got an error message saying "The specified field 'TitleID' could refer to more than one table listed in the FROM clause of your SQL statement."

Creating a separate query for displaying the cast details seemed the right thing to do, but Im not sure how to do it.

Would it be

myRS.Open "SELECT * From AllMovies WHERE TitleID =" & strTitleID &""
myRS.Open "SELECT * From Cast where CastID =" & strTitleID &"", myConn

Its already looking wrong because I wouldnt have thought myRS.Open would be placed twice? (sorry, Im a bit of ....infact.. alot of a newbie)

shahchi1 · January 27th, 2005, 03:24 PM

**
SELECT * FROM AllMovies INNER JOIN Cast ON AllMovies.TitleID = Cast.TitleID
WHERE AllMovies.TitleID =" & strTitleID
**
is this giving you an error? response.write you SQL statment please..

Dim strSQL
strSQL = "SELECT * FROM AllMovies INNER JOIN Cast ON AllMovies.TitleID = Cast.TitleID " &_
" WHERE AllMovies.TitleID =" & strTitleID"

rs.Open strSQL,objConn,1,3
Response.Write(strSQL)
Response.End

om_prakash · January 27th, 2005, 03:27 PM

You need not open a recordset for allMovies to get description of DVD. You can get this from the previous Form in hidden fields.

And use this recordset to show cast

myRS.Open "SELECT * From Cast where CastID =" & strTitleID &"", myConn

You don't have to show DVD title in the loop. In can be displayed before the loop.

Om Prakash

Nicky_uk · January 27th, 2005, 06:40 PM

I tried your suggestion shahchi1 but still got an error message saying

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/moviebox/products.asp, line 45

I'll post the code so it might be clearer.

Code:

Dim myRS, strTitleID, Image, TitleID, Summary, Genre, Certificate, ImageLrg, CurPage,Runtime,Price, Director, CastID, mySQL
CurPage = Request.QueryString ("page")
strTitleID = Request("TitleID")
If CurPage = "" Then CurPage = 1

set myRS = server.createObject("ADODB.Recordset")

myRS.CursorType = adOpenStatic
myRS.PageSize = 3

Dim strSQL 
strSQL = "SELECT * FROM AllMovies INNER JOIN Cast ON AllMovies.TitleID = Cast.TitleID " &_
         " WHERE AllMovies.TitleID '" & strGenre & "'"

myRS.Open strSQL,objConn,1,3
Response.Write(strSQL)
Response.End

I feel a bit stupid, but, being new to ASP, I'm not 100% familiar with all the processes that can be tried to acheive the right result. Unfortunately, I'm not too sure what is meant when you say I can get the AllMovies details from the previous form in hidden fields?

Thanks for your help so far.

Nicky_uk · February 2nd, 2005, 05:41 PM

Sorry guys, I am still struggling with this one. How would I add a flag to check that if records have already been requested not to repeat them?