ADO and SQL

Home >> ASP >> ADO and SQL

ADO and SQL

With SQL, data to displayed in an HTML page can be filtered and sorted.

Display Selected Data

<%

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

rs.Open sql, conn

%>

<table border="1" width="100%">

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

Sort the Data

<%

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 * FROM Customers ORDER BY CompanyName"

rs.Open sql, conn

%>

<table border="1" width="100%">

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

Examples

1. Display Selected Records

This example demonstrates how to display data that matches a certain criteria.

Coding

<html>

<body>

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open(Server.Mappath("northwind.mdb"))

set rs = Server.CreateObject("ADODB.recordset")

sql="SELECT Companyname, Contactname FROM Customers WHERE CompanyName LIKE 'A%'"

rs.Open sql, conn

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

<%next

rs.MoveNext%>

</tr>

<%loop

rs.close

conn.close

</table>

</body>

</html>

Output

Companyname Contactname

Alfreds Futterkiste Maria Anders

Antonio Moreno Taquerķa Antonio Moreno

2. Sort Records

This example demonstrates how to sort the data on a specified fieldname.

Coding

<html>

<body>

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open(Server.Mappath("northwind.mdb"))

set rs = Server.CreateObject("ADODB.recordset")

sql="SELECT Companyname, Contactname FROM Customers ORDER BY CompanyName"

rs.Open sql, conn

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

<%next

rs.MoveNext%>

</tr>

<%loop

rs.close

conn.close

</table>

</body>

</html>

Output

Companyname Contactname

Alfreds Futterkiste Maria Anders

Antonio Moreno Taquerķa Antonio Moreno

Berglunds snabbköp Christina Berglund

Centro comercial Moctezuma Francisco Chang

Drachenblut Delikatessen Sven Ottlieb

Ernst Handel Roland Mendel

Open Directory Site ASP Tutorials