Menu
×
   ❮     
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP HOW TO W3.CSS C C++ C# BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS R TYPESCRIPT ANGULAR GIT POSTGRESQL MONGODB ASP AI GO KOTLIN SASS VUE DSA GEN AI SCIPY AWS CYBERSECURITY DATA SCIENCE
     ❯   

ADO Query Data


We may use SQL to create queries to specify only a selection of records (and fields) to view.


Display Selected Data

Display companies that starts with "A"

Here we want to display only the records from the "Customers" table that have a "Companyname" that starts with "A" (remember to save the file with an .asp extension):

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

Display customers from Spain

Here we want to display only the customers from Spain (remember to save the file with an .asp extension):

<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 Country='Spain'"
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>

Create a filter for the user

Here we want to create a filter and let the user choose which country to show customers from (remember to save the file with an .asp extension):

<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 DISTINCT Country FROM Customers ORDER BY Country"
rs.Open sql, conn

country=request.form("country")
%>

<form method="post">
Choose Country <select name="country">
<% do until rs.EOF
  response.write("<option")
  if rs.fields("country")=country then
    response.write(" selected")
  end if
  response.write(">")
  response.write(rs.fields("Country"))
  rs.MoveNext
loop
rs.Close
set rs=Nothing %>
</select>
<input type="submit" value="Show customers">
</form>

<%
if country<>"" then
  sql="SELECT Companyname,Contactname,Country FROM Customers WHERE country='" & country & "'"
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.Open sql,conn
%>

<table width="100%" cellspacing="0" cellpadding="2" border="1">
<tr>
  <th>Companyname</th>
  <th>Contactname</th>
  <th>Country</th>
</tr>

<%
do until rs.EOF
  response.write("<tr>")
  response.write("<td>" & rs.fields("companyname") & "</td>")
  response.write("<td>" & rs.fields("contactname") & "</td>")
  response.write("<td>" & rs.fields("country") & "</td>")
  response.write("</tr>")
  rs.MoveNext
loop
rs.close
conn.Close
set rs=Nothing
set conn=Nothing%>
</table>
<% end if %>

</body>
</html>

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

Copyright 1999-2024 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.