Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Sorting alphabetically with headings
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gilgamesh
Starting Member

United Kingdom
14 Posts

Posted - 11/05/2001 :  10:17:16  Show Profile  Reply with Quote
I am writing an address book app. The problem is that I need to display the contents of the SQL database alphabetically but with headings (see below) I can't work out the best way to do it. Below is the code I am using at the moment - but I get a feeling there is a much better way of doing it.

Many thanks in advance!

Oliver


A
Colin Abraham
Steven Adams

B
Andrew Binkley
...

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
'i am using a SELECT * FROM...

<%
ARRAlphabet = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z")

i = 0
do while i < 26
%>
<tr><td colspan=4><font size="4"><%=uCase(ArrAlphabet(i))%></font></td></tr>

<%
recordset.moveFirst
do while not recordset.eof

if request.QueryString("sort") = "last" then
sLetter = recordset.Fields("RecLastName")
sLetter = left(sLetter, 1)
sLetter = lcase(sLetter)

else
sLetter = recordset.Fields("RecFirstName")
sLetter = left(sLetter, 1)
sLetter = lcase(sLetter)
end if
if sLetter = ArrAlphabet(i) then
%>

<tr>
<td></td>
<td><%=recordset.Fields("RecFirstName")%></td>
<td><%=recordset.Fields("RecLastName")%></td>
<td><%=recordset.Fields("RecEmail")%></td>
</tr>

<%
else
end if
recordset.movenext
loop
i = i + 1
loop

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

smccreadie
Aged Yak Warrior

USA
505 Posts

Posted - 11/05/2001 :  10:24:53  Show Profile  Visit smccreadie's Homepage  Reply with Quote
If you're using asp, you can just return a recordset of data and use the SHAPE functionality to do this.

Go to Top of Page

yakoo
Constraint Violating Yak Guru

USA
312 Posts

Posted - 11/05/2001 :  10:29:12  Show Profile  Reply with Quote
if you want to stick with the ASP side of it do the following.



<%
Set objConn = Server.CreateObject("ADODB.Connection")

ARRAlphabet = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z")

i = 0
do while i < 26
%>
<tr><td colspan=4><font size="4"><%=uCase(ArrAlphabet(i))%></font></td></tr>

<%
sql = "SELECT * FROM ADDRESS_BOOK"
if Request.QueryString("sort") = "last" then
sql = sql & " WHERE Left(LAST_NAME, 1) = '" & ArrAlphabel(i) & "' ORDER BY LAST_NAME ASC"
elseif Request.QueryString("sort") = "first" then
sql = sql & " WHERE Left(FIRST_NAME, 1) = '" & ArrAlphabel(i) & "' ORDER BY FIRST_NAME ASC"
end if

Set recordset = Server.CreateObject("ADODB.Recordset")
recordset.CursorLocation = 3
recordset.CursorType = 3
recordset.ActiveConnection = objConn
recordset.open sql

while not recordset.eof
%>
<tr>
<td></td>
<td><%=recordset.Fields("RecFirstName")%></td>
<td><%=recordset.Fields("RecLastName")%></td>
<td><%=recordset.Fields("RecEmail")%></td>
</tr>
<%
recordset.movenext
wend
recodset.close

i = i + 1
loop



Go to Top of Page

yakoo
Constraint Violating Yak Guru

USA
312 Posts

Posted - 11/05/2001 :  10:30:29  Show Profile  Reply with Quote
forgot all about MSDATASHAPE. I also suggest you do that. You can have recordsets within your recordset.

Good call smccreadie.

I hate when a post goes up when your posting.

Go to Top of Page

gilgamesh
Starting Member

United Kingdom
14 Posts

Posted - 11/05/2001 :  11:28:04  Show Profile  Reply with Quote
Many thanks both of you. For now I have gone for yakoo's solution - mainly because I understand it! It has speeded up the page load so thanks. When I have a bit more time I would like to look into the SHAPE function though is it applicable if I only have one table as all the info surrounding SHAPE use examples and illustrations of inter-relational tables.

Again many thanks Yakoo and smccreadie (particularly yakoo) for your time.

Oliver

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000