ADO Query

We can use SQL to create queries, so that we can specify to only view selected records and fields.

Example

Display records where 'Companyname' starts with 'A'
How to only display records starting with 'A' in the 'Companyname' field of the 'Customers' table.
Display records where 'Companyname' is greater than 'E'
How to only display records in the 'Companyname' field of the 'Customers' table that are greater than 'E'.
Only display Spanish customers
How to only display Spanish customers in the 'Customers' table.
Allow users to select the filtering criteria
Allow users to select customers by country

Display selected data

We hope to only display records starting with 'A' in the 'Companyname' field of the 'Customers' table:

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers"
WHERE CompanyName LIKE 'A%'"
rs.Open sql, conn
%>
<table border="1" width="100%">
  <tr>
  <%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
  next%
  </tr>
  <%do until rs.EOF%
    <tr>
    <%for each x in rs.Fields%
      <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%
    </tr>
  <%loop
  rs.close
  conn.close%
</table>
</body>
</html>