Database Connection In ADOCON

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

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

Note that this configuration has
to be done on the computer where your web site is located. If you are running
Personal Web Server (PWS) or Internet Information Server (IIS) on your own
computer, the instructions above will work, but if your web site is located on
a remote server, you have to have physical access to that server, or ask your
web host to do this for you. 

The ADO
Connection Object

The ADO Connection object is used
to create an open connection to a data source. Through this connection,
you can access and manipulate a database.

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

The ADO
Recordset Object

The ADO Recordset object is used
to hold a set of records from a database table.

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>

Here is the result:

  CustomerID = ALFKI

  CompanyName = Alfreds Futterkiste

  ContactName = Maria Anders

  ContactTitle = Sales Representative

  Address = Obere Str. 57

  City = Berlin

  PostalCode = 12209

  Country = Germany

  CustomerID = BERGS

  CompanyName = Berglunds snabbköp

  ContactName = Christina Berglund

  ContactTitle = Order Administrator

  Address = Berguvsvägen 8

  City = Luleå

  PostalCode = S-958 22

  Country = Sweden
  CustomerID = CENTC

  CompanyName = Centro comercial Moctezuma

  ContactName = Francisco Chang

  ContactTitle = Marketing Manager

  Address = Sierras de Granada 9993

  City = México D.F.

  PostalCode = 05022

  Country = Mexico
  ....

  ....

  ....

Display the Field Names and Field Values in
an HTML Table

We can also display the data from
the "Customers" table inside an HTML table with the following lines
(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 Companyname, Contactname FROM Customers", 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>
</body>
</html>

Here is the result:

  Alfreds Futterkiste

  Maria Anders

  Berglunds snabbköp

  Christina Berglund

  Centro comercial Moctezuma

  Francisco Chang

  Ernst Handel

  Roland Mendel

  FISSA Fabrica Inter. Salchichas S.A.

  Diego Roel

  Galería del gastrónomo

  Eduardo Saavedra

  Island Trading

  Helen Bennett

  Königlich Essen

  Philip Cramer

  Laughing Bacchus Wine Cellars

  Yoshi Tannamuri

  Magazzini Alimentari Riuniti

  Giovanni Rovelli

  North/South

  Simon Crowther

  Paris spécialités

  Marie Bertrand

  Rattlesnake Canyon Grocery

  Paula Wilson

  Simons bistro

  Jytte Petersen

  The Big Cheese

  Liz Nixon

  Vaffeljernet

  Palle Ibsen

  Wolski Zajazd

  Zbyszek Piestrzeniewicz

Add Headers to the HTML Table

We want to add headers to the HTML
table to make it more readable (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"
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>

Here is the result:

  Companyname

  Contactname

  Alfreds Futterkiste

  Maria Anders

  Berglunds snabbköp

  Christina Berglund

  Centro comercial Moctezuma

  Francisco Chang

  Ernst Handel

  Roland Mendel

  FISSA Fabrica Inter. Salchichas S.A.

  Diego Roel

  Galería del gastrónomo

  Eduardo Saavedra

  Island Trading

  Helen Bennett

  Königlich Essen

  Philip Cramer

  Laughing Bacchus Wine Cellars

  Yoshi Tannamuri

  Magazzini Alimentari Riuniti

  Giovanni Rovelli

  North/South

  Simon Crowther

  Paris spécialités

  Marie Bertrand

  Rattlesnake Canyon Grocery

  Paula Wilson

  Simons bistro

  Jytte Petersen

  The Big Cheese

  Liz Nixon

  Vaffeljernet

  Palle Ibsen

  Wolski Zajazd

  Zbyszek Piestrzeniewicz

Display Selected Data

We want to display only the
records from the "Customers" table that have a
"Companyname" that starts with an 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>

Here is the result:

  Companyname

  Contactname

  Alfreds Futterkiste

  Maria Anders

Sort the Data

We want to display the
"Companyname" and "Contactname" fields from the "Customers"
table, ordered by "Companyname" (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 ORDER BY CompanyName"
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>

Here is the result:

  Companyname

  Contactname

  Alfreds Futterkiste

  Maria Anders

  Berglunds snabbköp

  Christina Berglund

  Centro comercial Moctezuma

  Francisco Chang

  Ernst Handel

  Roland Mendel

  FISSA Fabrica Inter. Salchichas S.A.

  Diego Roel

  Galería del gastrónomo

  Eduardo Saavedra

  Island Trading

  Helen Bennett

  Königlich Essen

  Philip Cramer

  Laughing Bacchus Wine Cellars

  Yoshi Tannamuri

  Magazzini Alimentari Riuniti

  Giovanni Rovelli

  North/South

  Simon Crowther

  Paris spécialités

  Marie Bertrand

  Rattlesnake Canyon Grocery

  Paula Wilson

  Simons bistro

  Jytte Petersen

  The Big Cheese

  Liz Nixon

  Vaffeljernet

  Palle Ibsen

  Wolski Zajazd

  Zbyszek Piestrzeniewicz

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.

Update a Record in a Table

We want to update a record in the
Customers table in the Northwind database. We first create a table that lists
all records in 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")
rs.open "SELECT * FROM customers",conn
%>
<h2>List Database</h2>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
  response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>
<% do until rs.EOF %>
<tr>
<form method="post" action="demo_update.asp">
<%
for each x in rs.Fields
  if lcase(x.name)="customerid" then%>
    <td>
    <input type="submit" name="customerID" value="<%=x.value%>">
    </td>
  <%else%>
    <td><%Response.Write(x.value)%></td>
  <%end if
next
%>
</form>
<%rs.MoveNext%>
</tr>
<%
loop
conn.close
%>
</table>
</body>
</html>

If the user clicks on the button
in the "customerID" column he or she will be taken to a new file
called "demo_update.asp". The "demo_update.asp" file
contains the source code on how to create input fields based on the fields from
one record in the database table. It also contains a "Update record"
button that will save your changes:

  <html>
<body>
<h2>Update Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
cid=Request.Form("customerID")
if Request.form("companyname")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn
  %>
  <form method="post" action="demo_update.asp">
  <table>
  <%for each x in rs.Fields%>
  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  <%next%>
  </tr>
  </table>
  <br /><br />
  <input type="submit" value="Update record">
  </form>
<%
else
  sql="UPDATE customers SET "
  sql=sql & "companyname='" & Request.Form("companyname") & "',"
  sql=sql & "contactname='" & Request.Form("contactname") & "',"
  sql=sql & "address='" & Request.Form("address") & "',"
  sql=sql & "city='" & Request.Form("city") & "',"
  sql=sql & "postalcode='" & Request.Form("postalcode") & "',"
  sql=sql & "country='" & Request.Form("country") & "'"
  sql=sql & " WHERE customerID='" & cid & "'"
  on error resume next
  conn.Execute sql
  if err<>0 then
    response.write("No update permissions!")
  else 
    response.write("Record " & cid & " was updated!")
  end if 
end if
conn.close
%>
</body>
</html>

Delete a Record in a Table

We want to delete a record in the
Customers table in the Northwind database. We first create a table that lists
all records in 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")
rs.open "SELECT * FROM customers",conn
%>
<h2>List Database</h2>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
  response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>
<% do until rs.EOF %>
<tr>
<form method="post" action="demo_delete.asp">
<%
for each x in rs.Fields
  if x.name="customerID" then%>
    <td>
    <input type="submit" name="customerID" value="<%=x.value%>">
    </td>
  <%else%>
    <td><%Response.Write(x.value)%></td>
  <%end if
next
%>
</form>
<%rs.MoveNext%>
</tr>
<%
loop
conn.close
%>
</table>
</body>
</html>

If the user clicks on the button
in the "customerID" column he or she will be taken to a new file
called "demo_delete.asp". The "demo_delete.asp" file
contains the source code on how to create input fields based on the fields from
one record in the database table. It also contains a "Delete record"
button that will delete the current record:

  <html>
<body>
<h2>Delete Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
cid=Request.Form("customerID")
if Request.form("companyname")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn
  %>
  <form method="post" action="demo_delete.asp">
  <table>
  <%for each x in rs.Fields%>
  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  <%next%>
  </tr>
  </table>
  <br /><br />
  <input type="submit" value="Delete record">
  </form>
<%
else
  sql="DELETE FROM customers"
  sql=sql & " WHERE customerID='" & cid & "'"
  on error resume next
  conn.Execute sql
  if err<>0 then
    response.write("No update permissions!")
  else 
    response.write("Record " & cid & " was deleted!")
  end if 
end if
conn.close
%>
</body>
</html>

Database Connection In ADOCON

Create a DSN-less Database Connection

Create an ODBC Database Connection

An ODBC Connection to an MS Access Database

The ADO Connection Object

Create an ADO Table Recordset

Create an ADO SQL Recordset

Extract Data from the Recordset

The ADO Recordset Object

Display the Field Names and Field Values

Display the Field Names and Field Values in an HTML Table

Add Headers to the HTML Table

Display Selected Data

Sort the Data

Add a Record to a Table in a Database

Important

What about Fields With no Data?

Update a Record in a Table

Delete a Record in a Table