ADO 排序

我们可以使用SQL来规定如何对记录集中的数据进行排序。

实例

根据指定的字段名处对记录进行升序排序

如何根据指定字段名对数据进行排序 

  1. <html>
  2. <body>
  4. <%
  5. set conn=Server.CreateObject("ADODB.Connection")
  6. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  7. conn.Open(Server.Mappath("/db/northwind.mdb"))
  8. set rs = Server.CreateObject("ADODB.recordset")
  9. sql="SELECT Companyname, Contactname FROM Customers ORDER BY CompanyName"
  10. rs.Open sql, conn
  11. %>
  13. <table border="1" width="100%">
  14. <tr>
  15. <%for each x in rs.Fields
  16.     response.write("<th>" & x.name & "</th>")
  17. next%>
  18. </tr>
  19. <%do until rs.EOF%>
  20.     <tr>
  21.     <%for each x in rs.Fields%>
  22.        <td><%Response.Write(x.value)%></td>
  23.     <%next
  24.     rs.MoveNext%>
  25.     </tr>
  26. <%loop
  27. rs.close
  28. conn.close
  29. %>
  30. </table>
  32. </body>
  33. </html>

根据指定的字段名处对记录进行降序排序

如何根据指定字段名对数据进行排序 

  1. <html>
  2. <body>
  4. <%
  5. set conn=Server.CreateObject("ADODB.Connection")
  6. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  7. conn.Open(Server.Mappath("/db/northwind.mdb"))
  8. set rs = Server.CreateObject("ADODB.recordset")
  9. sql="SELECT Companyname, Contactname FROM Customers ORDER BY CompanyName DESC"
  10. rs.Open sql, conn
  11. %>
  13. <table border="1" width="100%">
  14. <tr>
  15. <%for each x in rs.Fields
  16.     response.write("<th>" & x.name & "</th>")
  17. next%>
  18. </tr>
  19. <%do until rs.EOF%>
  20.     <tr>
  21.     <%for each x in rs.Fields%>
  22.        <td><%Response.Write(x.value)%> </td>
  23.     <%next
  24.     rs.MoveNext%>
  25.     </tr>
  26. <%loop
  27. rs.close
  28. conn.close
  29. %>
  30. </table>
  32. </body>
  33. </html>

让用户来选择根据哪列进行排序

让用户来选择根据哪列进行排序 

  1. <html>
  2. <body>
  4. <table border="1" width="100%" bgcolor="#fff5ee">
  5. <tr>
  6. <th align="left" bgcolor="#b0c4de">
  7. <a href="/example/adoe/demo_adoe_sort_3.asp?sort=companyname">Company</a>
  8. </th>
  9. <th align="left" bgcolor="#b0c4de">
  10. <a href="/example/adoe/demo_adoe_sort_3.asp?sort=contactname">Contact</a>
  11. </th>
  12. </tr>
  13. <%
  14. if request.querystring("sort")<>"" then
  15.    sort=request.querystring("sort")
  16. else
  17.    sort="companyname"
  18. end if
  20. set conn=Server.CreateObject("ADODB.Connection")
  21. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  22. conn.Open(Server.Mappath("/db/northwind.mdb"))
  23. set rs=Server.CreateObject("ADODB.recordset")
  24. sql="SELECT Companyname,Contactname FROM Customers ORDER BY " & sort
  25. rs.Open sql,conn
  27. do until rs.EOF
  28.    response.write("<tr>")
  29.    for each x in rs.Fields
  30.      response.write("<td>" & x.value & "</td>")
  31.    next
  32.    rs.MoveNext
  33.    response.write("</tr>")
  34. loop
  35. rs.close
  36. conn.close
  37. %>
  38. </table>
  40. </body>
  41. </html>

对数据进行排序

我们希望显示 "Customers" 表中的"Companyname"和"Contactname"字段，并根据"Companyname"进行排序（请记得用.asp为后缀保存）：

  1. <html>
  2. <body>
  3.  
  4. <%
  5. set conn=Server.CreateObject("ADODB.Connection")
  6. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  7. conn.Open "c:/webdata/northwind.mdb"
  8.  
  9. set rs = Server.CreateObject("ADODB.recordset")
  10. sql="SELECT Companyname, Contactname FROM
  11. Customers ORDER BY CompanyName"
  12. rs.Open sql, conn
  13. %>
  14.  
  15. <table border="1" width="100%">
  16.   <tr>
  17.   <%for each x in rs.Fields
  18.     response.write("<th>" & x.name & "</th>")
  19.   next%>
  20.   </tr>
  21.   <%do until rs.EOF%>
  22.     <tr>
  23.     <%for each x in rs.Fields%>
  24.       <td><%Response.Write(x.value)%></td>
  25.     <%next
  26.     rs.MoveNext%>
  27.     </tr>
  28.   <%loop
  29.   rs.close
  30.   conn.close%>
  31. </table>
  32.  
  33. </body>
  34. </html>