Click here to Skip to main content
11,492,523 members (63,656 online)
Click here to Skip to main content

Alphabetically database Paging

, 5 Dec 2006 42.5K 509 20
Rate this:
Please Sign up or sign in to vote.
database Paging alphabetically tutorial

Introduction

Alphabetically Database Paging

This purpose of this code is to paging databse alphabetically.

<%
'*******************************************************
'* Replace <dsn-name> with actual dsn name e.g empdata *
'* Create table emp with field- id,firstname,lastname  *
'*******************************************************
%>

<%
Dim conn 
Dim rs
Dim sql

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "dsn=<dsn-name>;uid=;pwd="

If request.QueryString("letter") = "" then
 sql = "SELECT firstname,lastname FROM emp " & _
 "WHERE firstname Like '[A]%'" & _
 "ORDER BY firstname"
Else
 
sql = "SELECT firstname,lastname " & _
 "FROM emp " & _
 "WHERE firstname LIKE '" & request.Querystring("letter") & "%'" & _
 "ORDER BY firstname"
End If
' Debugging Line
'response.write sql
 
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 1


' Display message when error found
If Err.Number <> 0 Then
 Response.write("SQL: " & sql & "<br>")
 Response.write("Error Code: " & Err.Number & "<br>")
 Response.write("Error Description: " & Err.Description & "<br>")
 Response.write("Error Source: " & Err.Source & "<br>")
 Response.write("Error Line: " & Err.Line & "<br>")
 response.end
End If
totalRecs = rs.RecordCount
%>

<html>
<head><title>Paging Alphabetically ....</title></head>
<body>
<center>
<Table leftmargin='0' bordercolor="#111111" width="100%" cellspacing="0" style="border-collapse: collapse" cellpadding="0">
  <tr>
    <td width="100%" bgcolor="#888C00" bordercolor="#888C00">&nbsp;</td>
  </tr>
  <tr>
    <td width="100%" bordercolor="#D8DC98" bgcolor="#D8DC98">&nbsp;</td>
  </tr>
</Table>

<%
Response.write "<font face='verdana' size='2'>"
'Response.write "Total Number of Record : " &totalRecs
%>

<br>
<br>
<%
for x=1 to 26
response.write " <a href="/KB/asp/db.asp"?letter=" & chr(64+x) & "'>" & chr(64+x) & "</a> -"
next
%>
</center>
<br>
<Table border='1' cellspacing="0" width="100%" bordercolor="#666666" cellpadding="3" style="border-collapse: collapse">

<%
recCount = 0
recActual = 0

Do While (NOT rs.EOF)
 recCount = recCount + 1
 'If Cint(recCount) >= Cint(startRec) Then
 ' recActual = recActual + 1 %>

<% ' Display alternate color for rows

 If recCount mod 2 = 0 Then
  bgcolor="#98B4F8"
 Else
  bgcolor="#A8BCE8"
 End If

 x_ocfname = rs("firstname")
 x_oclname = rs("lastname")
%>

<tr bgcolor="<%= bgcolor %>">

<td><font face="Arial" size="2">
 <% response.write x_ocfname %>&nbsp;
</font></td>
<td><font face="Arial" size="2">
 <% response.write x_oclname %>&nbsp;
</font></td>

</tr>

<%
 
 rs.MoveNext
 Loop
%>

</Table>
<br>
<%
' Feedback of records effected
Response.write "Numbers of records found : " &totalRecs
Response.write "</font>"
Response.write "</br>"
Response.write "<br>"
%>
<%
' Close recordset and connection
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing %>

<Table leftmargin='0' bordercolor="#111111" width="100%" cellspacing="0" style="border-collapse: collapse" cellpadding="0">
  <tr>
    <td width="100%" bgcolor="#888C00" bordercolor="#888C00">&nbsp;</td>
  </tr>
  <tr>
    <td width="100%" bordercolor="#D8DC98" bgcolor="#D8DC98">&nbsp;</td>
  </tr>
</Table>

</body>
</html>

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Amitux
Team Leader
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
GeneralHere's an alternative way. Pin
Anonymous16-Sep-03 3:53
sussAnonymous16-Sep-03 3:53 
Generalthis is not an article Pin
Horatiu CRISTEA15-Sep-03 22:46
memberHoratiu CRISTEA15-Sep-03 22:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 6 Dec 2006
Article Copyright 2003 by Amitux
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid