ADO 实例

显示

显示记录

  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. rs.Open "Select * from Customers", conn
  11. do until rs.EOF
  12.     for each x in rs.Fields
  13.        Response.Write(x.name)
  14.        Response.Write(" = ")
  15.        Response.Write(x.value & "<br />") 
  16.     next
  17.     Response.Write("<br />")
  18.     rs.MoveNext
  19. loop
  21. rs.close
  22. conn.close
  23. %>
  25. </body>
  26. </html>

在 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"))
  9. set rs = Server.CreateObject("ADODB.recordset")
  10. rs.Open "SELECT Companyname, Contactname FROM Customers", conn
  11. %>
  13. <table border="1" width="100%">
  14. <%do until rs.EOF%>
  15.     <tr>
  16.     <%for each x in rs.Fields%>
  17.        <td><%Response.Write(x.value)%></td>
  18.     <%next
  19.     rs.MoveNext%>
  20.     </tr>
  21. <%loop
  22. rs.close
  23. conn.close
  24. %>
  25. </table>
  27. </body>
  28. </html>

向 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"
  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>

向 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"
  10. rs.Open sql, conn
  11. %>
  13. <table border="1" width="100%" bgcolor="#fff5ee">
  14. <tr>
  15. <%for each x in rs.Fields
  16.     response.write("<th align='left' bgcolor='#b0c4de'>" & 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>

查询

显示 "Companyname" 以 A 开头的记录

  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 WHERE CompanyName LIKE 'A%'"
  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>

显示 "Companyname" 大于 E 的记录

  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 WHERE CompanyName>'E'"
  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 WHERE Country='China'"
  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"))
  9. set rs=Server.CreateObject("ADODB.recordset")
  10. sql="SELECT DISTINCT Country FROM Customers ORDER BY Country"
  11. rs.Open sql,conn
  13. country=request.form("country")
  15. %>
  17. <form method="post">
  18. Choose Country <select name="country">
  19. <%  do until rs.EOF
  20.     response.write("<option")
  21.     if rs.fields("country")=country then
  22.       response.write(" selected")
  23.     end if
  24.     response.write(">")
  25.     response.write(rs.fields("Country"))
  26.     rs.MoveNext
  27. loop
  28. rs.Close
  29. set rs=Nothing %>
  30. </select>
  31. <input type="submit" value="Show customers">
  32. </form>
  34. <%
  35. if country<>"" then
  36.    sql="SELECT Companyname,Contactname,Country FROM Customers WHERE country='" & country & "'"
  37.    set rs=Server.CreateObject("ADODB.Recordset")
  38.    rs.Open sql,conn
  39. %>
  40.    <table width="100%" cellspacing="0" cellpadding="2" border="1">
  41.    <tr>
  42.      <th>Companyname</th>
  43.      <th>Contactname</th>
  44.      <th>Country</th>
  45.    </tr>
  46. <%
  47. do until rs.EOF
  48.    response.write("<tr>")
  49.    response.write("<td>" & rs.fields("companyname") & "</td>")
  50.    response.write("<td>" & rs.fields("contactname") & "</td>")
  51.    response.write("<td>" & rs.fields("country") & "</td>")
  52.    response.write("</tr>")
  53.    rs.MoveNext
  54. loop
  55. rs.close
  56. conn.Close
  57. set rs=Nothing
  58. set conn=Nothing%>
  59. </table>
  60. <%  end if %>
  62. </body>
  63. </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"
  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>

Recordset 对象

GetRows

  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. rs.Open "Select * from Customers", conn
  11. 'The first number indicates how many records to copy
  12. 'The second number indicates what recordnumber to start on
  13. p=rs.GetRows(2,0)
  15. response.write("<p>This example returns the value of the first column in the first two records:</p>")
  16. response.write(p(0,0))
  17. response.write("<br />")
  18. response.write(p(0,1))
  20. response.write("<p>This example returns the value of the first three columns in the first record:</p>")
  21. response.write(p(0,0))
  22. response.write("<br />")
  23. response.write(p(1,0))
  24. response.write("<br />")
  25. response.write(p(2,0))
  27. rs.close
  28. conn.close
  29. %>
  31. </body>
  32. </html>

GetString

  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. rs.Open "SELECT Companyname, Contactname FROM Customers", conn
  10. str=rs.GetString(,,"</td><td>","</td></tr><tr><td>"," ")
  11. %>
  13. <table border="1" width="100%">
  14.    <tr>
  15.      <td><%Response.Write(str)%></td>
  16.    </tr>
  17. </table>
  19. <%
  20. rs.close
  21. conn.close
  22. set rs = Nothing
  23. set conn = Nothing
  24. %>
  26. </body>
  27. </html>