"Browse by Artist" in a Sample Music Database

[eclipse33]

I am new to ASP and I am attempting to design a sample Music Store ASP page. (see

http://music.hollywood.com

for an example)

If you pick a Genre from the list box...there is a "Browse Country Artists by Name" and A,B,C... links under it. This is what I want to do. I want a letter only to be linked if it has a matching artist under it. For example if there isn't a Country artist with a last name starting with Z I just want Z displayed with no link. But if there is a match the letter will be passed in the querystring to the next page to display all the artist under that letter. (that part isn't in my code yet)

I have the connection created fine and the recordset is created but how would you match the letter of the alphabet with the first letter.

I have a variable in the database called browseLetter that I am using to represent what each artist/group should be listed under. And I created an array to hold the alphabet but I am having a problem matching the two and moving through the recordset with creating exceptions or infinite loops..Any ideas???

Here is some of the code I tried last ( I haven't put the actual h ref tags in yet)

Dim strSQL
strSQL = "SELECT DISTINCT BrowseLetter FROM tblMain 1999 ORDER BY BrowseLetter"

Dim objRS2
Set objRS2 = Server.CreateObject("ADODB.Recordset&quot
objRS2.Open strSQL, objConn, adOpenKeySet, adLockReadOnly, adCmdText
%>

<%
For x = 1 to 26
If objRS2(&quot;BrowseLetter&quot = arrLetters(x-1) then
'The below is for testing only (HREF will be here)
Response.Write &quot;link- &quot; & arrLetters(x-1) & &quot; -link<br>&quot;

Else

Do while NOT objRS2.EOF
If objRS2(&quot;BrowseLetter&quot <> arrLetters(x-1) then
'The below is for testing only (NO HREF will be here)
Response.Write &quot;NOlink- &quot; & arrLetters(x-1) & &quot;- NOlink<br>&quot;
objRS2.MoveNext
Else
'The below is for testing only (HREF will be here)
Response.Write &quot;link2- &quot; & arrLetters(x-1) & &quot;- link2<br>&quot;
objRS2.MoveLast
End if

Loop

End if
objRS2.MoveFirst

Next 'x

%>

<%
'Clean up and close the ADO Recordset
objRS2.Close
Set objRS2 = Nothing

objConn.Close
Set objConn = Nothing
%>

 

[GIGN]

Easiest way to do alphabet search is with Filters:

http://www.devguru.com/Technologies/ado/quickref/recordset_filter.html

Just be careful to use the % wildcard or you will get an error. So here you would filter by starting letter, then use those results - a nice touch is to add the number of records as the title for each link.

Oh and also - to save writing out the alphabet use chr().


<% ' Loop through the alphabet Filtering the Records ------ ALPHABET
With Response
For i= 65 to 90
filterString = filterField & &quot; LIKE '&quot;& chr(i) & &quot;%&quot; & &quot;'&quot;
RecordSet.Filter = filterString 'Set the filter.

'Reset
IF (CInt(RecordSet.RecordCount)> 0 )Then
.write &quot;<a title='&quot; & RecordSet.RecordCount & &quot; items' &quot; & &quot; href='category_display.asp?sort_letter=&quot; & chr(i) & &quot;&category=&quot; & category& &quot; ' class='alphabet'>&quot; & chr(i) & &quot;</a>&quot;
ELSE
.write &quot;<a title='No Items Here' href='javascript:void(0)' class='alphabet_inert'>&quot; & chr(i) & &quot;</a>&quot;
End IF
Next
End With
%>



[bb]