repeating data

ph0neman · January 17th, 2008, 04:34 PM

I have a quick question... elementary to you seasoned asp'ers Im sure. I have a form that I am using to give a band name. This asp block of code is supposed to then connect to sql and grab the albums this band has released. What is happening though is it lists the album, artists and year for each album but it lists it like 12 times (how ever many songs are in that album is how many times it lists it)
Not sure how to tell it i only want unique instances of each album? Any ideas? Thank you!

<%
  option explicit
  Dim strConnect
%>


<HTML>
<HEAD>
<TITLE>Using the Find Method to find an Artists Albums</TITLE>
</HEAD>
<BODY>

<%
  Dim strArtist, strCriteria
  strArtist = Request.Form("artist")

  Dim objRS
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "top50000", strConnect, adOpenStatic, adLockReadOnly, adCmdTable

  strCriteria = "artist='" & strArtist & "'"

  objRS.Filter = strCriteria

While Not objRS.EOF
        Response.Write "<H2>Released by " & strArtist & ":</H2>" & _
         "<B>Album:</B> " & objRS("Album") & "<BR>" & _
         "<B>Artist:</B> " & objRS("Artist") & "<BR>" & _
         "<B>Year:</B> " & objRS("Year") & "<BR><BR><BR>"
objrs.movenext
wend

  objrs.close
  set objrs = nothing
%>
</BODY>
</HTML>

dparsons · January 17th, 2008, 05:44 PM

This isn't an asp question it is a SQL question.

Your query should be something like:

SELECT DISTINCT Artist, Albumn, Year FROM <Table>

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

ph0neman · January 18th, 2008, 10:02 AM

thank you dparsons for clarifying... i wasnt sure if i could get just the specific records using asp... guess i can not.

dparsons · January 18th, 2008, 12:03 PM

Well that is really a different question. The way you posed your question is that a user of your site will select a band and then you will display information based off of X band.

So you can do this 2 ways, the most effecient being through SQL:
SELECT DISTINCT Albumn, Year FROM <table> where artist = '<someartist>'

You can, however, return all of the data from your table and then use the recordsets Filter property to only get the data you want:

objrs.Filter = "Artist = 'some artist'"

Does that clear things up a bit better?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

ph0neman · January 18th, 2008, 12:11 PM

It does thanks so much for the quick responses dparsons. Both ways worked! :D

dparsons · January 18th, 2008, 12:34 PM

No problem, glad it worked out for you.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========