<html>
<body>
<h1>
This is page 1!
</h1>
<%
Set nl=Server.CreateObject("MSWC.NextLink")
If (nl.GetListIndex("text/links2.txt")>1) Then
%>
<a href="<%Response.Write(nl.GetPreviousURL("text/links2.txt"))%>">Previous Page</a>
<%End If%>
<a href="<%Response.Write(nl.GetNextURL("text/links2.txt"))%>">Next Page</a>
<p>The example uses the Content Linking Component
to navigate between the pages in a text file.</p>
<p>
<a href="text/links2.txt"><img border="0" src="../images/btn_view_text.gif"></a>
</p>
</body>
</html>
ADO
ADO Tutorial
In our ADO tutorial you will learn about ADO, and how to use ADO to access databases from your Web. Start Learning ADO!
ADO can be used to access databases from your web pages.
What you should already know
Before you continue you should have a basic understanding of the following:
-
WWW, HTML and the basics of building Web pages
-
Active Server Pages (ASP)
-
Structured Query Language (SQL)
If you want to study these subjects first, go to our Home Page
What is ADO?
-
ADO is a Microsoft technology
-
ADO stands for ActiveX Data Objects
-
ADO is a Microsoft Active-X component
-
ADO is automatically installed with Microsoft IIS
-
ADO is a programming interface to access data in a database
Accessing a Database from an ASP Page
The common way to access a database from inside an ASP page is to:
1. Create an ADO connection to a database
2. Open the database connection
3. Create an ADO recordset
4. Open the recordset
5. Extract the data you need from the recordset
6. Close the recordset
7. Close the connection
Before a database can be accessed from a web page, a database connection has to be established.
Create a DSN-less Database Connection
The easiest way to connect to a database is to use a DSN-less connection. A DSN-less connection can be used against any Microsoft Access database on your web site.
If you have a database called "northwind.mdb" located in a web directory like "c:/webdata/", you can connect to the database with the following ASP code:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
%>
|
Note, from the example above, that you have to specify the Microsoft Access database driver (Provider) and the physical path to the database on your computer.
Create an ODBC Database Connection
If you have an ODBC database called "northwind" you can connect to the database with the following ASP code:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "northwind"
%>
|
With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.
An ODBC Connection to a MS Access Database
Here is how to create a connection to a MS Access Database:
1. Open the ODBC icon in your Control Panel.
2. Choose the System DSN tab.
3. Click on Add in the System DSN tab.
4. Select the Microsoft Access Driver. Click Finish.
5. In the next screen, click Select to locate the database.
6. Give the database a Data Source Name (DSN).
7. Click OK.
ADO Recordset
This chapter explains how to access an ADO Recordset.
ADO Recordset
To be able to read database data, the data must first be loaded into a recordset.
Create an ADO Table Recordset
After an ADO Database Connection has been created, as demonstrated in the previous chapter, it is possible to create an ADO Recordset.
Suppose we have a database named "Northwind", we can get access to the "Customers" table inside the database with the following lines:
<%
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")
rs.Open "Customers", conn
%>
|
Create an ADO SQL Recordset
We can also get access to the data in the "Customers" table using SQL:
<%
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")
rs.Open "Select * from Customers", conn
%>
|
Extract Data from the Recordset
After a recordset is opened, we can extract data from recordset.
Suppose we have a database named "Northwind", we can get access to the "Customers" table inside the database with the following lines:
<%
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")
rs.Open "Select * from Customers", conn
for each x in rs.fields
response.write(x.name)
response.write(" = ")
response.write(x.value)
next
%>
|
ADO Display
This chapter explains the most common way to display data from an ADO Recordset.
Display the Field Names and Field Values
We have a database named "Northwind" and we want to display the data from the "Customers" table (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")
rs.Open "Select * from Customers", conn
do until rs.EOF
for each x in rs.Fields
Response.Write(x.name)
Response.Write(" = ")
Response.Write(x.value & "<br />")
next
Response.Write("<br />")
rs.MoveNext
loop
rs.close
conn.close
%>
</body>
</html>
|
ADO Query
This chapter explains how to use SQL to filter an ADO Recordset.
ADO Sort
This chapter explains how to use SQL to sort an ADO Recordset.
ADO Add
This chapter explains how to use the SQL INSERT INTO command to add a record to a table in a database.
Add a Record to a Table in a Database
We want to add a new record to the Customers table in the Northwind database. We first create a form that contains the fields we want to collect data from:
<html>
<body>
<form method="post" action="demo_add.asp">
<table>
<tr>
<td>CustomerID:</td>
<td><input name="custid"></td>
</tr><tr>
<td>Company Name:</td>
<td><input name="compname"></td>
</tr><tr>
<td>Contact Name:</td>
<td><input name="contname"></td>
</tr><tr>
<td>Address:</td>
<td><input name="address"></td>
</tr><tr>
<td>City:</td>
<td><input name="city"></td>
</tr><tr>
<td>Postal Code:</td>
<td><input name="postcode"></td>
</tr><tr>
<td>Country:</td>
<td><input name="country"></td>
</tr>
</table>
<br /><br />
<input type="submit" value="Add New">
<input type="reset" value="Cancel">
</form>
</body>
</html>
|
When the user presses the submit button the form is sent to a file called "demo_add.asp". The "demo_add.asp" file contains the code that will add a new record to the Customers table:
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
sql="INSERT INTO customers (customerID,companyname,"
sql=sql & "contactname,address,city,postalcode,country)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("custid") & "',"
sql=sql & "'" & Request.Form("compname") & "',"
sql=sql & "'" & Request.Form("contname") & "',"
sql=sql & "'" & Request.Form("address") & "',"
sql=sql & "'" & Request.Form("city") & "',"
sql=sql & "'" & Request.Form("postcode") & "',"
sql=sql & "'" & Request.Form("country") & "')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>
</body>
</html>
|
Important
If you use the SQL INSERT command be aware of the following:
-
If the table contains a primary key, make sure to append a unique, non-Null value to the primary key field (if not, the provider may not append the record, or an error occurs)
-
If the table contains an AutoNumber field, do not include this field in the SQL INSERT command (the value of this field will be taken care of automatically by the provider)
What about Fields With no Data?
In a MS Access database, you can enter zero-length strings ("") in Text, Hyperlink, and Memo fields IF you set the AllowZeroLength property to Yes.
Note: Not all databases support zero-length strings and may cause an error when a record with blank fields is added. It is important to check what data types your database supports.
