ADO 查询

我们可以使用 SQL 来创建查询，这样就可以指定仅查看选定的记录和字段。

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

如何仅仅显示 "Customers" 表的 "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 的记录

如何仅仅显示 "Customers" 表的 "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>

仅显示西班牙的客户

如何仅仅显示 "Customers" 表中的西班牙客户。 

  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>

显示选定的数据

我们希望仅仅显示 "Customers" 表的 "Companyname" 字段中以 A 开头的记录：

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