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"> </td>
</tr>
<tr>
<td width="100%" bordercolor="#D8DC98" bgcolor="#D8DC98"> </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='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 %>
</font></td>
<td><font face="Arial" size="2">
<% response.write x_oclname %>
</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"> </td>
</tr>
<tr>
<td width="100%" bordercolor="#D8DC98" bgcolor="#D8DC98"> </td>
</tr>
</Table>
</body>
</html>
